Postgres中如何将邻接表层级数据转为任意深度嵌套JSON树

你遇到的报错是PostgreSQL对递归CTE的语法限制：递归查询的递归项中不允许使用聚合函数，你的自底向上聚合子节点的思路直接用递归CTE写会触发这个限制，需求完全可以实现，推荐两种可行方案：

方案1：使用PL/pgSQL递归函数（最简洁，支持任意深度） #

这个方案逻辑清晰，无需处理递归CTE的语法限制，生成的结构完全符合要求：

-- 创建递归函数
CREATE OR REPLACE FUNCTION get_creature_tree(p_parent text) 
RETURNS json AS $$
BEGIN
    RETURN (
        SELECT json_agg(
            json_build_object(
                'name', name,
                'children', COALESCE(get_creature_tree(name), '[]'::json)
            )
        )
        FROM creatures
        WHERE parent IS NOT DISTINCT FROM p_parent
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- 调用函数获取根节点树形结构
SELECT get_creature_tree(NULL) AS creature_tree;

执行后返回的JSON和你要求的格式完全一致。

方案2：纯SQL递归CTE实现（无需自定义函数） #

如果不希望创建自定义函数，可以通过分层聚合的方式绕过递归项不能用聚合的限制，先递归获取所有节点的深度，再从最底层逐层向上聚合：

WITH RECURSIVE node_depth AS (
    -- 根节点深度为1
    SELECT name, parent, 1 AS depth
    FROM creatures
    WHERE parent IS NULL
    
    UNION ALL
    
    SELECT c.name, c.parent, nd.depth + 1 AS depth
    FROM creatures c
    JOIN node_depth nd ON c.parent = nd.name
),
max_depth AS (
    SELECT MAX(depth) AS max FROM node_depth
),
layered_agg AS (
    -- 从最深层开始，逐层向上聚合
    SELECT 
        nd.parent,
        json_build_object('name', nd.name, 'children', '[]'::json) AS node
    FROM node_depth nd
    JOIN max_depth md ON nd.depth = md.max
    
    UNION ALL
    
    SELECT 
        nd.parent,
        json_build_object(
            'name', nd.name,
            'children', COALESCE(json_agg(la.node) FILTER (WHERE la.node IS NOT NULL), '[]'::json)
        ) AS node
    FROM node_depth nd
    LEFT JOIN layered_agg la ON la.parent = nd.name
    GROUP BY nd.name, nd.parent
)
SELECT json_agg(node) AS creature_tree
FROM layered_agg
WHERE parent IS NULL;

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