表"Orders":
orderId | products |
---|---|
12345 | [{"title":"product1","amount":2,"price":5.60},{"title":"product2","amount":1,"price":3.29}] |
预期输出:
orderId | title | amount | price |
---|---|---|---|
12345 | product1 | 2 | 5.60 |
12345 | product2 | 1 | 3.29 |
我设法走到了这一步:
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 |
但我想不出怎么给每一行加"orderId".
在这方面有人能帮忙吗?