CODEWITHSUNDEEP
sqlarraysjsonpostgresql
Mo Halem
Mo Halem

Reputation: 194

Get element from array of JSON postgres

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"

Upvotes: 4

Views: 7193

Answers (1)

S-Man
S-Man

Reputation: 23756

demo:db<>fiddle

SELECT jsonb_array_elements(jsonb) -> 0 -> 'string' 
FROM (
    SELECT '[[{"string":"whateverstring1"}],[{"string":"whateverstring2"}]]'::jsonb
) s
  1. jsonb_array_elements extract each element into one row
  2. -> 0 gives the first element of the nested arrays which is {"string":"whateverstring1"}
  3. -> 'string' gives the value of the elements

Upvotes: 11

Related Questions