PostgreSQL递归查询：将树形表数据转为嵌套子节点格式

表结构与数据 #

现有menu_item表包含id、name、parent字段，数据如下：

期望结果 #

仅返回1条完整层级的嵌套数据，格式如下：

当前问题 #

使用的递归CTE查询返回全部4条数据，且仅展开一级子节点，需修改查询以得到预期结果。

解决方案 #

要实现完整嵌套层级，需在递归过程中从底层节点向上聚合构建JSON结构，修改后的查询如下：

WITH RECURSIVE menu_tree AS (
    -- 基础查询：获取无子女的叶子节点，转为基础JSON对象
    SELECT 
        id,
        name,
        parent,
        jsonb_build_object('id', id, 'name', name) AS node
    FROM menu_item mi
    WHERE NOT EXISTS (SELECT 1 FROM menu_item WHERE parent = mi.id)
    
    UNION ALL
    
    -- 递归查询：向上聚合子节点，构建包含children的父节点JSON
    SELECT 
        mi.id,
        mi.name,
        mi.parent,
        jsonb_build_object(
            'id', mi.id,
            'name', mi.name,
            'children', jsonb_agg(mt.node)
        ) AS node
    FROM menu_item mi
    INNER JOIN menu_tree mt ON mi.id = mt.parent
    GROUP BY mi.id, mi.name, mi.parent
)
-- 仅筛选根节点（parent为NULL），返回完整层级数据
SELECT 
    id,
    name,
    jsonb_agg(node) AS children
FROM menu_tree
WHERE parent IS NULL
GROUP BY id, name;

逻辑说明 #

1. 叶子节点初始化：先定位所有没有子节点的叶子节点，将其转换为不含children的基础JSON对象。
2. 递归向上聚合：从叶子节点往根节点遍历，每次将当前节点的所有子节点JSON聚合为children字段，生成包含子层级的父节点JSON。
3. 根节点筛选：最终只选取parent为NULL的根节点，确保仅返回1条完整的嵌套层级数据。

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