Get element from array of JSON postgres

Question

I have a column x with data type jsonb

and the value looks like:

[  
    [{"string":"whateverstring1"}],
    [{"string":"whateverstring2"}]
]

How to return each element of the array?

Something like this: "whateverstring1","whateverstring2"

S-Man · Accepted Answer · 2018-10-30 16:48:12Z

11

SELECT jsonb_array_elements(jsonb) -> 0 -> 'string' 
FROM (
    SELECT '[[{"string":"whateverstring1"}],[{"string":"whateverstring2"}]]'::jsonb
) s

jsonb_array_elements extract each element into one row
-> 0 gives the first element of the nested arrays which is {"string":"whateverstring1"}
-> 'string' gives the value of the elements

answered Oct 30, 2018 at 16:48

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1