如何修改PostgreSQL递归查询实现完整层级文件夹结构JSON输出

问题背景 #

使用如下表结构模拟文件夹层级：

CREATE TABLE folders (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name text,
    parent UUID REFERENCES folders(id)
);

测试数据（简化ID）：

(1, "parent", NULL)
(2, "child of 1", 1)
(3, "child of 2", 2)
(4, "other parent", NULL)

原递归查询仅能返回一级子节点的JSON，无法实现多层级嵌套：

WITH RECURSIVE recursive_cte AS (
    SELECT id, name, parent
    FROM folders
    WHERE parent IS NULL -- 从根节点开始（parent为NULL）
    
    UNION ALL
    
    SELECT f.id, f.name, f.parent
    FROM folders f
    JOIN recursive_cte rc ON f.parent = rc.id
)
SELECT id, name, 
  (
      SELECT json_agg(json_build_object('id', c.id, 'name', c.name))
      FROM recursive_cte c
      WHERE c.parent = folders.id
  ) AS children
FROM folders
WHERE parent IS NULL;

当前输出仅包含一级子节点：

id, name, children[]
(1, "parent", [{"id":2, "name":"child of 1"}])
(4, "other parent", [])

修改方案 #

需要让递归CTE直接构建嵌套的JSON结构，而非仅聚合一级子节点。推荐从叶子节点向上递归聚合的方式，确保所有层级子节点都被嵌套：

WITH RECURSIVE folder_tree AS (
    -- 基础分支：匹配所有无下级子节点的文件夹（叶子节点）
    SELECT 
        id,
        name,
        parent,
        json_build_object('id', id, 'name', name) AS node_json
    FROM folders
    WHERE id NOT IN (SELECT parent FROM folders WHERE parent IS NOT NULL)

    UNION ALL

    -- 递归分支：向上遍历父节点，将子节点的JSON聚合为children字段
    SELECT 
        f.id,
        f.name,
        f.parent,
        json_build_object(
            'id', f.id,
            'name', f.name,
            'children', json_agg(ft.node_json)
        ) AS node_json
    FROM folders f
    JOIN folder_tree ft ON f.id = ft.parent
    GROUP BY f.id, f.name, f.parent
)
-- 最终只输出根节点的完整嵌套结构
SELECT node_json AS full_folder_tree
FROM folder_tree
WHERE parent IS NULL;

预期输出 #

执行后将得到包含完整层级的嵌套JSON：

full_folder_tree
---------------------------------------------------------------
{"id":1,"name":"parent","children":[{"id":2,"name":"child of 1","children":[{"id":3,"name":"child of 2"}]}]}
{"id":4,"name":"other parent"}

方案说明 #

1. 基础分支：先定位所有没有子节点的叶子文件夹，为其生成最基础的JSON对象（仅包含id和name）。
2. 递归分支：向上匹配父节点，将当前父节点下所有子节点的JSON对象聚合为children数组，再生成包含该数组的父节点JSON。
3. 最终查询：筛选出根节点（parent IS NULL）的JSON，即为完整的层级结构。

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