PostgreSQL单表生成嵌套JSON对象的实现方案与问题解决

问题背景 #

需求：将单表数据按id字段分组，每个分组内再按section字段嵌套分组，生成指定结构的JSON。

现有表数据：

-----------------------------
| id | section | subsection |
----------------------------
| 1  | s_1     | ss_1       |
----------------------------
| 1  | s_1     | ss_2       |
----------------------------
| 1  | s_2     | ss_3       |
----------------------------
| 2  | s_3     | ss_4       |
----------------------------

期望生成的JSON格式：

[
  {
    "id": 1,
    "sections": [
      {
        "section": "s_1",
        "subsections": [
          {
            "subsection": "ss_1"
          },
          {
            "subsection": "ss_2"
          }
        ]
      },
      {
        "section": "s_2",
        "subsections": [
          {
            "subsection": "ss_3"
          }
        ]
      }
    ]
  },
  {
    "id": 2,
    "sections": [
      {
        "section": "s_3",
        "subsections": [
          {
            "subsection": "ss_4"
          }
        ]
      }
    ]
  }
]

用户尝试的SQL语句：

select
json_build_array(
    json_build_object(
        'id', a.id,
        'sections', json_agg(
                json_build_object(
                    'section', b.section,
                    'subsections', json_agg(
                            json_build_object(
                                'subsection', c.subsection
                            )
                    )
                )
        )
    )
)
from table as a
         inner join table  as b on a.section = b.section
         inner join table  as c on b.subsection = c.subsection
group by a.id;

执行时报错：Nested aggregate calls are not allowed（不允许嵌套聚合调用）

错误原因 #

PostgreSQL不支持在一个聚合函数（如json_agg）内部直接嵌套另一个聚合函数，因为内层聚合的分组逻辑无法与外层分组对齐，会导致分组上下文冲突。

解决方案 #

需要通过分步聚合实现：先按id和section分组生成每个section对应的subsections聚合；再按id分组生成每个id对应的sections聚合；最后将结果包装成目标JSON数组。

方法一：使用CTE（公共表表达式）分步处理 #

WITH section_subsections AS (
    SELECT
        id,
        section,
        json_agg(json_build_object('subsection', subsection)) AS subsections
    FROM your_table  -- 替换为实际表名
    GROUP BY id, section
),
id_sections AS (
    SELECT
        id,
        json_agg(json_build_object('section', section, 'subsections', subsections)) AS sections
    FROM section_subsections
    GROUP BY id
)
SELECT json_agg(json_build_object('id', id, 'sections', sections)) AS result
FROM id_sections;

方法二：使用子查询嵌套 #

SELECT json_agg(json_build_object('id', id, 'sections', sections)) AS result
FROM (
    SELECT
        id,
        json_agg(json_build_object('section', section, 'subsections', subsections)) AS sections
    FROM (
        SELECT
            id,
            section,
            json_agg(json_build_object('subsection', subsection)) AS subsections
        FROM your_table  -- 替换为实际表名
        GROUP BY id, section
    ) AS sub_query
    GROUP BY id
) AS main_query;

逻辑说明 #

1. 第一步聚合：按id和section分组，将每个section下的所有subsection聚合成数组，得到每个(id, section)对应的subsections结构。
2. 第二步聚合：按id分组，将每个id下的所有section结构聚合成数组，得到每个id对应的sections结构。
3. 最后一步：将所有id的结构聚合成最外层的JSON数组，得到目标格式。

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