PostgreSQL中json_agg结合json_build_object时如何优雅union多表数据

测试表结构及测试数据 #

CREATE TABLE test1 (
    id integer, value text);

INSERT INTO test1
    VALUES (1, 'xyz'), (2, 'xxy');

CREATE TABLE test2 (
    id integer, value text);

INSERT INTO test2
    VALUES (3, 'yyy'), (4, 'yxy');

现有单表聚合实现 #

单表聚合为JSON数组的正确写法如下：

SELECT json_agg (json_build_object(
        'ID', t1.id, 
        'someText', t1.value )) AS jbo
FROM test1 t1

问题说明 #

尝试通过UNION ALL合并test1、test2的同结构数据后统一聚合时，如下写法会触发语法错误：

SELECT
json_agg(
    SELECT
        json_build_object('ID', t1.id, 'someText', t1.value)
    FROM test1 t1

UNION ALL

SELECT
    json_build_object('ID', t2.id, 'someText', t2.value)
    FROM test2 t2
    ) AS jbo

报错原因是json_agg聚合函数的入参需要是符合要求的行集，不能直接将UNION ALL的查询语句作为参数传入。实际业务中还可能涉及多表关联、多表同名字段等复杂场景，需要更通用的实现方案。

优雅实现方案 #

先将所有UNION ALL分支的查询结果作为子查询或者CTE（通用表表达式），再在外层执行json_agg聚合即可，该方案可以完美兼容复杂业务场景：

方案1：子查询写法

SELECT json_agg(t.item) AS jbo
FROM (
    SELECT json_build_object('ID', t1.id, 'someText', t1.value) AS item
    FROM test1 t1
    UNION ALL
    SELECT json_build_object('ID', t2.id, 'someText', t2.value) AS item
    FROM test2 t2
) t;

方案2：CTE写法（逻辑更清晰，适合分支较多的场景）

WITH union_data AS (
    SELECT json_build_object('ID', t1.id, 'someText', t1.value) AS item
    FROM test1 t1
    -- 此处可以写test1对应的多表关联逻辑、筛选条件，同名字段可单独处理别名
    UNION ALL
    SELECT json_build_object('ID', t2.id, 'someText', t2.value) AS item
    FROM test2 t2
    -- 此处可以写test2对应的多表关联逻辑、筛选条件，同名字段可单独处理别名
)
SELECT json_agg(item) AS jbo
FROM union_data;

该实现的优势：

• 每个UNION ALL分支独立编写，各自的关联逻辑、字段处理互不干扰，出现同名字段时可以在分支内单独处理别名避免冲突
• 扩展性强，后续需要新增其他表的数据时，直接加UNION ALL分支即可，无需修改原有逻辑
• 语法合规，执行效率和单表聚合没有明显差异

内容的提问来源于stack exchange，提问作者sh4rkyy