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

Asked

Viewed 212 times

0

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

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

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

returning:

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

How can I get the key template of all objects at the same time, without specifying the index?

EDIT: example of stored array:

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

1 answer

0

The function jsonb_array_elements expands an array to a set of json values, so you only need to filter these tuples by returning the contents of the key template.

If it is necessary to return the results in a single row just use some aggregation function like the array_agg.

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

Upshot:

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

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.