PostgreSQL:高效地将列表查询结果聚合成JSON对象

Question

我需要从一个查询中返回一个结果，在这个查询中，我匹配表中的一行，然后在JSON中聚合引用该行的多达500,000个文本值。

• JSON必须是一个映射，其中名称是数据点。它需要是一个key:value对象，因为这样它将被序列化为我的golang后端中的哈希映射，这将允许我在常量时间内搜索它。我不在乎值是什么，空字符串或null是首选，以减少传输和序列化时间。

最初我尝试使用jsonb_build_object，但它非常慢。下面是该查询的简化版本:

SELECT
table_a.id,
table_a.name,
table_a.description,
table_a.created,
table_a.createdby,
table_a.modified,
table_a.modifiedby,
jsonb_build_object( -- the aggregate takes a very long time
SELECT
table_b.item
FROM
table_b
WHERE
table_a.id = table_b.table_a_id
)
FROM
table_a
WHERE 
table_a.id = <some_id_input>;

我的下一步是获得一个数组，然后将其转换为JSON。这被证明更有效率:

SELECT
table_a.id,
table_a.name,
table_a.description,
table_a.created,
table_a.createdby,
table_a.modified,
table_a.modifiedby,
to_jsonb( ARRAY (
SELECT
table_b.item
FROM
table_b
WHERE
table_a.id = table_b.table_a_id
))
FROM
table_a
WHERE 
table_a.id = <some_id_input>;

尽管它更有效，但当我需要JSON对象时，它会给我一个JSON数组…

是否有一个简单而有效的方式在PostgreSQL 14产生我需要什么?

Answer 1

加入聚合结果:

SELECT
table_a.id,
table_a.name,
table_a.description,
table_a.created,
table_a.createdby,
table_a.modified,
table_a.modifiedby,
b.items
FROM table_a
LEFT JOIN (
select table_a_id, jsonb_agg(table_b.item) as items
FROM table_b
GROUP by table_a_id
) b on b.table_a_id = table_a.id
WHERE 
table_a.id = <some_id_input>;

或者可以使用横向连接将其限制为单个table_a.id:

SELECT
table_a.id,
table_a.name,
table_a.description,
table_a.created,
table_a.createdby,
table_a.modified,
table_a.modifiedby,
b.items
FROM table_a
LEFT JOIN LATERAL (
select jsonb_agg(table_b.item) as items
FROM table_b
WHERE table_b.table_a_id = table_a.id
) b on true 
WHERE 
table_a.id = <some_id_input>;

---

我不确定，但如果使用本机数组(array_agg())比JSON更有效地创建500,000项的数组

，我不会感到惊讶。