递归SQL排序分组文件夹树生成正确JSON结构的问题排查

表结构与测试数据 #

我有一个folder_tree表，结构如下：

CREATE TABLE folder_tree (
    id SERIAL PRIMARY KEY,
    name TEXT,
    level INTEGER,
    parent_id INTEGER,
    created_at TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES folder_tree (id) ON DELETE CASCADE);

测试数据：

INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section A', 0, null, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B', 0, null, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.1', 1, 2, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section A.1', 1, 1, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section C', 0, null, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.1.1', 2, 3, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.1.2', 2, 3, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section B.2', 1, 2, NOW());
INSERT INTO folder_tree (name, level, parent_id, created_at) VALUES ('Section C.1', 1, 5, NOW());

需求与问题 #

我需要按层级（level）和父ID（parent_id）对数据排序分组，最终输出JSON格式的目录树。期望的结构如下：

├── A
│   └── A.1
├── B
│   ├── B.1
│   │   ├── B.1.1
│   │   └── B.1.2
│   └── B.2
└── C
    └── C.1

我编写了递归CTE的SQL查询，但执行后得到的JSON结构不符合预期：Section B的子节点顺序错误，且Section B.1重复出现：

├── A
│   └── A.1
├── B
│   ├── B.2
│   ├── B.1
│   │   └── B.1.1
│   └── B.1
│       └── B.1.2
└── C
    └── C.1

我的SQL代码如下：

WITH json_agg_scope_cte AS (
  WITH scope_cte AS (
    WITH RECURSIVE sub_scopes_cte AS (
      SELECT
        s.*,
        NULL :: JSON AS sub_scopes
      FROM
        folder_tree s
      WHERE NOT EXISTS(
          SELECT 1
          FROM folder_tree
          WHERE parent_id = s.id
      )

      UNION ALL

      SELECT
        s.*,
        row_to_json(ssc) AS sub_scopes
      FROM
        sub_scopes_cte ssc
        JOIN folder_tree s
          ON s.id = ssc.parent_id
    )

    SELECT
      id,
      name,
      json_agg(sub_scopes) AS sub_scopes
    FROM sub_scopes_cte ssc
    WHERE parent_id IS NULL
    GROUP BY 1, 2
  )

  SELECT
    s.*,
    sc.sub_scopes
  FROM folder_tree s
    INNER JOIN scope_cte sc
      ON s.id = sc.id
)

SELECT json_agg(json_agg_scope_cte.*) AS scopes
FROM json_agg_scope_cte

请问上述SQL存在什么问题？有没有其他解决方案？

原SQL的问题 #

• 递归方向错误：原递归CTE从叶子节点向上遍历到根节点，每个叶子节点都会触发一次父节点关联，导致父节点（如Section B.1）被多次处理，最终聚合时重复出现。
• 缺少排序逻辑：json_agg默认无排序规则，导致子节点顺序混乱（如B.2出现在B.1之前）。
• 多层CTE冗余：嵌套多层CTE不仅增加复杂度，还导致聚合时重复收集节点数据。

正确解决方案 #

调整递归方向为从根节点向下遍历，同时在聚合时指定排序规则，确保节点顺序正确且无重复。优化后的SQL如下：

WITH RECURSIVE folder_hierarchy AS (
    -- 根节点：level=0的节点，初始子节点为空数组
    SELECT
        id,
        name,
        level,
        parent_id,
        created_at,
        '[]'::JSON AS sub_scopes
    FROM folder_tree
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归遍历子节点，将子节点聚合到父节点的sub_scopes中
    SELECT
        p.id,
        p.name,
        p.level,
        p.parent_id,
        p.created_at,
        json_agg(c ORDER BY c.name)::JSON AS sub_scopes
    FROM folder_hierarchy p
    JOIN folder_tree c ON p.id = c.parent_id
    GROUP BY p.id, p.name, p.level, p.parent_id, p.created_at
)
-- 最终聚合根节点，得到完整的目录树JSON
SELECT json_agg(f ORDER BY f.name) AS scopes
FROM folder_hierarchy f
WHERE f.parent_id IS NULL;

代码说明 #

1. 递归方向调整：从根节点（parent_id IS NULL）开始向下遍历，每次关联子节点并聚合到父节点的sub_scopes中，避免重复处理父节点。
2. 排序规则：在json_agg中添加ORDER BY c.name，确保子节点按名称顺序排列，符合预期的目录结构。
3. 简化逻辑：用单层递归CTE完成层级聚合，减少冗余处理，提升查询效率。

执行该SQL后，将得到符合预期的JSON结构，层级关系正确、节点顺序一致且无重复。

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