T O P

Extracting the values of a nested array in JSON for PostgreSQL

Extracting the values of a nested array in JSON for PostgreSQL

Quit-Affectionate

Hello. How about: -- data is the table With json_rows as ( select json_array_elements(json_data::json #> '{order,line_items}') from data ) select json_array_elements #>> '{quantity}' as quantity, json_array_elements #>> '{name}' as name, json_array_elements #> '{total_money}' #>> '{amount}' as total_money from json_rows;


MiyagiJunior

Thanks - I will try!


truilus

You can access the array directly using `#> `{order,line_items}` the result of that can then be fed into `jsonb_array_elements()` to unnest the array: select li.item ->> 'name' as name, li.item ->> 'note' as not, li.item ->> 'quantity' as quantity, li.item #>> '{total_money,amount}' as total_amount, li.item #>> '{total_money,currency}' as total_amount_currency from the_table t cross join jsonb_array_elements(t.json_data #> '{order,line_items}') as li(item) this assumes `json_data` is a `jsonb` column (which it should be). If it's just `json` use `json_array_elements()` instead.


MiyagiJunior

Thank you, I will try this!


MiyagiJunior

It worked - thank you!