PostgreSQL中如何将多结果聚合为JSON对象数组

要将你的查询结果转换为指定的JSON格式，核心思路是分组聚合标签为数组，再将每组数据构建为JSON对象，最后汇总为JSON数组。以下是具体实现方案：

最终查询语句 #

SELECT json_agg(
  json_build_object(
    'id', id,
    'sub_id', sub_id,
    'name', name,
    'tags', tags_array
  ) ORDER BY id, sub_id
) AS json_result
FROM (
  SELECT 
    a.id,
    a.sub_id,
    a.name,
    json_agg(at.tag ORDER BY at.tag) AS tags_array
  FROM names a    
  -- 筛选出包含Tag1或Tag3的name记录（去重避免重复连接）
  INNER JOIN (SELECT DISTINCT id, sub_id FROM tags WHERE tag IN ('Tag1', 'Tag3')) AS mt 
    ON a.id = mt.id AND a.sub_id = mt.sub_id  
  -- 关联获取该name的所有标签
  INNER JOIN tags as at 
    ON a.id = at.id AND a.sub_id = at.sub_id
  -- 按id、sub_id、name分组
  GROUP BY a.id, a.sub_id, a.name
) AS grouped_data;

关键步骤说明 #

1. 筛选目标记录：通过mt子查询获取所有包含Tag1或Tag3的id/sub_id对（加DISTINCT避免重复连接导致数据冗余），并关联names表得到基础数据。
2. 聚合标签为JSON数组：使用json_agg(at.tag ORDER BY at.tag)将每组的标签汇总为有序的JSON数组。
3. 构建单组JSON对象：通过json_build_object将id、sub_id、name和标签数组组合成单个JSON对象。
4. 汇总为JSON数组：外层使用json_agg将所有分组对象包裹为最终的JSON数组，并按id和sub_id排序保证结果一致性。

补充说明 #

• 若需要去重标签，可将json_agg(at.tag)改为json_agg(DISTINCT at.tag)。
• 若偏好JSONB格式，可将json_agg和json_build_object替换为jsonb_agg和jsonb_build_object。

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