Postgresql SELECT item from JSONB array - array as top-level json object

Question

I have JSONB column which stores the data in this format

[
    {
        "item1": "value1",
        "item2": "value2"
    },
    {
        "item1": "value3",
        "item2": "value4"
    }
]

I am trying to select 'item1' with query

SELECT jsonb_array_elements(data)->'item1' FROM test_table

Error I am getting is:

cannot extract elements from an object

How should I change the query to make it work?

user1822 · Accepted Answer · 2019-03-13 11:49:57Z

3

You need to unnest the array elements to rows in the from clause:

select i.dt -> 'item1' as item1
from test_table t
  cross join jsonb_array_elements(t.data) as i(dt)

answered Mar 13, 2019 at 11:49

user1822

1 Answer 1