Select every array item in column from every row postgres-CodePudding

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?

CodePudding user response：

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