Please help newbie - to get one row to multiple - order id and multiple product rows (from json)-CodePudding

What I have:

Orders

orderId	products
12345	[{"title":"product1","amount":2,"price":5.60},{"title":"product2","amount":1,"price":3.29}]

What I need:

orderId	title	amount	price
12345	product1	2	5.60
12345	product2	1	3.29

Googling the problem I managed to get this far

with products_array as
(select json_array_elements(products #> '{}')
from Orders)
select 
products_array.json_array_elements #>> '{title}' as title,
products_array.json_array_elements #>> '{amount}' as amount,
products_array.json_array_elements #>> '{price}' as price,
from products_array

title	amount	price
product1	2	5.60
product2	1	3.29

But I can't figure out how to add orderId for each line.

Can someone help? My knowledge of this is very limited.

CodePudding user response：

You can extract your json objects using JSON_ARRAY_ELEMENTS, then use a CROSS JOIN LATERAL to associate them to the corresponding "orderid".

SELECT orderid, 
       prods ->> 'title'  AS title,
       prods ->> 'amount' AS amount,
       prods ->> 'price'  AS price
FROM tab
CROSS JOIN LATERAL JSON_ARRAY_ELEMENTS(tab.products) as prods;

Check the demo here.