Postgres递归CTE生成JSON树异常，求正确层级结构方案

问题分析 #

原实现的核心问题在于递归CTE未正确维护节点路径关联，且最终输出未过滤非根节点，导致根节点重复；同时子节点聚合逻辑错误，使得1.1.1、1.1.2未挂载到1.1节点下。

修正后的SQL代码 #

WITH RECURSIVE tree AS (
    -- 锚点：选择根节点，初始化路径数组
    SELECT 
        id,
        parent_id,
        name,
        ARRAY[id] AS path
    FROM category
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归：关联子节点，追加路径
    SELECT 
        c.id,
        c.parent_id,
        c.name,
        t.path || c.id
    FROM category c
    JOIN tree t ON c.parent_id = t.id
),
-- 构建带子节点的JSON结构
node_json AS (
    SELECT 
        parent_id,
        json_agg(
            json_build_object(
                'id', id,
                'name', name,
                'children', COALESCE(child_json, '[]'::json)
            )
        ) AS node_list
    FROM tree t
    LEFT JOIN node_json nj ON t.id = nj.parent_id
    GROUP BY parent_id
)
-- 只输出根节点的JSON树
SELECT node_list AS json_tree
FROM node_json
WHERE parent_id IS NULL;

关键逻辑说明 #

1. 路径追踪：递归CTE中用ARRAY[id]记录节点路径，确保每个节点的层级关系可追溯，避免聚合时的关联错误。
2. 子节点聚合：通过自左连接node_json，将子节点的JSON数组挂载到对应父节点的children字段，使用COALESCE处理无子女节点的情况。
3. 根节点过滤：最终仅选择parent_id IS NULL的根节点输出，避免重复根节点问题。

期望输出示例 #

[
  {
    "id": 1,
    "name": "1",
    "children": [
      {
        "id": 2,
        "name": "1.1",
        "children": [
          {"id": 4, "name": "1.1.1", "children": []},
          {"id": 5, "name": "1.1.2", "children": []}
        ]
      },
      {"id": 3, "name": "1.2", "children": []}
    ]
  }
]

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