在一个查询中选择具有所有相关子行的行作为数组

Question

我使用Postgres(最新)与节点(最新)PG(最新)。某个端点正在接收json，它看起来像:

{
"id": 12345,
"total": 123.45,
"items": [
{
"name": "blue shirt",
"url": "someurl"
},
{
"name": "red shirt",
"url": "someurl"
}
]
}

我将它存储在两个表中:

CREATE TABLE orders (
id INT NOT NULL,
total NUMERIC(10, 2) DEFAULT 0 NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX index_orders_id ON orders(id);
CREATE TABLE items (
id BIGSERIAL NOT NULL,
order_id INT NOT NULL,
name VARCHAR(128) NOT NULL,
url VARCHAR(128) DEFAULT '' NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
CREATE INDEX index_items_id ON items(id);

items表有一个order_id的FK，用于将订单的id与其各自的项目关联起来。现在，问题是I几乎总是需要获取订单和项目。

我如何得到一个输出类似于我的输入json在一个查询?我知道它可以在两个查询中完成，但这种模式将无处不在，需要高效。我最后的办法是将项目作为JSONB列直接存储在订单表中，但是如果我需要查询项目或与它们进行连接，这就不那么容易了。

Answer 1

方法之一:

SELECT jsonb_pretty(
to_jsonb(o.*)  -- taking whole row
|| (SELECT jsonb_build_object('items', jsonb_agg(i))
FROM  (
SELECT name, url  -- picking columns
FROM   items i
WHERE  i.order_id = o.id
) i
)
)
FROM   orders o
WHERE  o.id = 12345;

返回格式化文本类似于显示的输入。(但键是排序的，所以'total'排在'items'之后)

如果一个订单没有商品，你得到"items": null。

对于jsonb值，剥去jsonb_pretty()包装。

我选择jsonb是因为它的附加功能——比如jsonb || jsonb→jsonb运算符和jsonb_pretty()函数。

相关:

• 返回与JSON对象数组相同行的多个列

如果您想要一个json值，您可以直接转换jsonb(不带格式)或格式化的text(带格式)。或者直接用基本格式构建一个json值(更快):

SELECT row_to_json(sub, true)
FROM  (
SELECT o.*
, (SELECT json_agg(i)
FROM  (
SELECT name, url  -- pick columns to report
FROM   items i
WHERE  i.order_id = o.id
) i
) AS items
FROM   orders o
WHERE  o.id = 12345
) sub;

db<此处小提琴>

这完全取决于你需要什么。

旁白:
考虑类型text(或varchar)而不是看似任意的varchar(128)。看到:

• 我应该添加一个任意长度限制的VARCHAR列吗?