Select every array item in column from every row postgres

Question

I have a table which looks like this:

Name | Data
-------------
Test1 | {"toasts": [{"type": "dark", "calories": "100"}, {"type": "white", "calories": "200"}, {"type": "gray", "calories": "300"}]}
"Test2" | {"toasts": [{"type": "white", "calories": "200"}]}

Data is an array of objects.

What I need is to select all array items in all rows:

Res
--------
{type: 'dark', calories: '100'}
{type: 'white', calories: '200'}
{type: 'gray', calories: '300'}
{type: 'white', calories: '200'}

Can anyone help?

Edouard · Accepted Answer · 2022-12-19 22:30:04Z

1

Your example is not a valid json data. It should rather be :

Name	Data
Test1	{"toasts": [{"type": "dark", "calories": 100}, {"type": "white", "calories": 200}, {"type": "gray", "calories": 300}]}
Test2	{"toasts": [{"type": "white", "calories": 200}]}

The query to get the expected result is :

SELECT jsonb_path_query(Data :: jsonb, '$.toasts[*]') AS res FROM my_table

see dbfiddle

answered Dec 19, 2022 at 22:30

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

1 Comment

OttoV Over a year ago

Thank you thats it. sorry JSON, thats not an actual prod data, I compose it on the fly. will edit now.

Collectives™ on Stack Overflow

Select every array item in column from every row postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related