jsonb: how to fetch the value of the same key from all objects in an array?

Question

jsonb: how to fetch the value of the same key from all objects in an array?

Navigation

#1 by (0 votes)

0

I have a column of type jsonb that stores objects in an array.

I can get the key template of all index objects 0 like this:

psql=# select data->'components'->0->'template' as template from page;

that returns:

template             
-------------------------------------
 "nome-do-template"
 //etc
(100 rows)

How can I do to search for the key template of all objects at the same time without specifying the index?

EDIT: stored array example:

[
  {
    "type": "feed",
    "template": "_vertical-content",
  },
  {
    "type": "feature",
    "template": "_single-content",
  }
]

json postgresql

asked by anonymous 18.08.2017 / 01:07

1 answer

Turning rows into columns Calling element of a method in another method

score 0 · Answer 1

The function jsonb_array_elements expands an array to a set of json values, so it is only necessary to filter these tuples by returning the content of the key template .

If you need to return the results on a single line, just use some aggregation function such as array_agg .

with d(dados) as (
    select 
        jsonb_array_elements(data#>'{components}') 
    from teste
)
select dados->>'template'
from d;

Result:

    template      
-------------------
_vertical-content
_single-content
(2 registros)

Note: I performed the test considering that the json array corresponds to key components .