Postgres jsonb_insert array

Question

我正在尝试构建一个查询，该查询返回客户购物车及其所有商品的数组，例如

表格：

购物车：id、customer_id

项目：cart_id、price、brand

所需的JSON结果示例：

[{
"id": 1, "customer_id": 1,
"items": [{
"price": 14.50, "brand": "Coke"
}]
}]

失败的SQL查询(此处可测试：http://sqlfiddle.com/#！17/0ef54/1(：

SELECT jsonb_insert(to_jsonb(c.*), 'items', array(
SELECT to_jsonb(i.*)
FROM item i
WHERE i.cart_id = c.id
)
FROM cart c

如何才能做到这一点？

Answer 1

听起来好像你在寻找一个聚合：

select jsonb_agg(to_jsonb(c)||jsonb_build_object('items', i.items))
from cart c 
join (
select cart_id, jsonb_agg(to_jsonb(i) - 'cart_id') as items
from item i
group by cart_id
) i on i.cart_id = c.id;

在线示例

Answer 2

select jsonb_agg(to_jsonb(c.*) || 
jsonb_build_object
(
'items', 
(select jsonb_agg(to_jsonb(i.*) - 'cart_id') from item i where c.id = i.cart_id)
)
) from cart c;

• 演示

create temporary table item (cart_id integer, price numeric, brand text);
create temporary table cart (id integer, customer_id integer);
insert into item values (1, 10, 'skoda'),(1, 11, 'ford'), (1, 12, 'opel'); 
insert into item values (2, 20, 'skoda'),(2, 21, 'ford'), (2, 22, 'opel'); 
insert into cart values (1, 1), (2, 2);
select jsonb_agg(to_jsonb(c.*) || jsonb_build_object(
'items', (select jsonb_agg(to_jsonb(i.*) - 'cart_id') from item i where c.id = i.cart_id)
)) from cart c;

• 结果：

[
{
"id": 1,
"items": [
{
"brand": "skoda",
"price": 10
},
{
"brand": "ford",
"price": 11
},
{
"brand": "opel",
"price": 12
}
],
"customer_id": 1
},
{
"id": 2,
"items": [
{
"brand": "skoda",
"price": 20
},
{
"brand": "ford",
"price": 21
},
{
"brand": "opel",
"price": 22
}
],
"customer_id": 2
}
]