PostgreSQL分组查询结果合并为单个JSON数组的实现方法

你遇到的问题是因为当前查询在每个分组内单独创建了JSON数组，要把所有分组结果合并成一个大数组，我们可以利用PostgreSQL的json_agg聚合函数来实现，下面是具体的解决方案：

修正后的查询语句 #

方法一：子查询+row_to_json+json_agg（简洁版） #

SELECT json_agg(result_row)
FROM (
    SELECT
        e2."name" AS entegrator_name,
        p."name" AS provider_name,
        COUNT(t1.id) AS table1_count
    FROM table1 AS t1
    -- 修正原查询中未定义的表别名问题，将e改为t1（假设table1表存储集成商ID）
    JOIN entegrators AS e2 ON t1.entegrator_id = e2.id
    JOIN providers AS p ON t1.provider_id = p.id
    GROUP BY e2."name", p."name"
) AS result_row;

方法二：直接在聚合中构建JSON对象（灵活版） #

SELECT json_agg(
    json_build_object(
        'entegrator_name', e2."name",
        'provider_name', p."name",
        'table1_count', cnt
    )
)
FROM (
    SELECT
        e2."name",
        p."name",
        COUNT(t1.id) AS cnt
    FROM table1 AS t1
    JOIN entegrators AS e2 ON t1.entegrator_id = e2.id
    JOIN providers AS p ON t1.provider_id = p.id
    GROUP BY e2."name", p."name"
) AS grouped_data;

逻辑说明 #

• 内层子查询先完成核心的分组统计：按集成商和服务商的名称分组，计算每个组合对应的table1记录数，得到结构化的统计结果。
• 外层的json_agg函数会将所有分组的结果行（自动转换为JSON对象后）聚合为单一的JSON数组，最终输出一行包含所有结果的数组，完全符合你的期望格式。

另外补充：原查询中的JOIN entegrators AS e2 ON e.entegrator_id = e2.id存在表别名未定义的问题，我默认修正为t1.entegrator_id，如果你的实际表结构字段名不同，可以根据真实情况调整。

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