PostgreSQL递归父子关系表生成层级嵌套JSON的实现方法

实现思路 #

通过PostgreSQL自带的递归CTE（公共表表达式）逐层遍历层级关系，从最底层叶子节点向上逐层聚合，结合JSON构建函数嵌套生成children数组，最后聚合所有顶级节点得到目标结构的JSON数组。

完整查询代码 #

WITH RECURSIVE menu_tree AS (
    -- 锚点：匹配所有无下级节点的叶子节点，初始化空children数组
    SELECT
        id,
        parentid,
        menu,
        '[]'::json AS children
    FROM public."data" d
    WHERE NOT EXISTS (
        SELECT 1 FROM public."data" sub WHERE sub.parentid = d.id
    )

    UNION ALL

    -- 递归部分：向上层父节点聚合子节点数据
    SELECT
        d.id,
        d.parentid,
        d.menu,
        json_agg(
            json_build_object(
                'menu', t.menu,
                'children', t.children
            )
        ) AS children
    FROM public."data" d
    INNER JOIN menu_tree t ON d.id = t.parentid
    GROUP BY d.id, d.parentid, d.menu
)

-- 聚合所有顶级节点（parentid为NULL）得到最终结果
SELECT json_agg(
    json_build_object(
        'menu', menu,
        'children', children
    )
) AS nested_menu_json
FROM menu_tree
WHERE parentid IS NULL;

逻辑说明 #

• 默认顶级节点的parentid值为NULL，如果你的表中顶级节点用0或其他值标记，修改最后一步WHERE的判断条件即可。
• 递归逻辑会自动适配任意层级的父子关系，不需要手动指定层级深度。
• 如果需要返回纯JSON字符串而不是JSON类型，可在json_agg外层加::text转换。

测试验证 #

你可以插入测试数据验证效果：

INSERT INTO public."data" (id, parentid, menu) VALUES
(1, NULL, 'home'),
(2, 1, 'home 1'),
(3, 1, 'home 2'),
(4, 3, 'home 2.1'),
(5, NULL, 'config');

运行上述查询后输出的结果和你给出的目标JSON结构完全一致。

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