Get one row to multiple order id and multiple product rows from json-CodePudding

Table "Orders":

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

Expected Output:

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

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 in that regard?

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.