PostgreSQL如何将模块数据转换为带children的层级JSON结构？

假设你的模块表（示例表名为modules）包含以下核心字段：

• id: 模块唯一ID（主键）
• name: 模块名称
• parent_id: 父模块ID（顶层模块的parent_id为NULL）

要生成带children字段的层级JSON，需要结合递归CTE和JSON聚合函数实现，具体SQL如下：

WITH RECURSIVE module_hierarchy AS (
    -- 第一步：获取所有顶层模块（无父节点的模块）
    SELECT
        id,
        name,
        parent_id,
        '[]'::json AS children
    FROM modules
    WHERE parent_id IS NULL

    UNION ALL

    -- 第二步：递归遍历子模块，聚合子节点为JSON数组
    SELECT
        m.id,
        m.name,
        m.parent_id,
        json_agg(child_node) AS children
    FROM modules m
    JOIN module_hierarchy child_node ON m.id = child_node.parent_id
    GROUP BY m.id, m.name, m.parent_id
)
-- 第三步：聚合顶层模块，生成最终层级JSON数组
SELECT array_to_json(array_agg(json_build_object(
    'id', id,
    'name', name,
    'children', children
))) AS module_tree
FROM module_hierarchy
WHERE parent_id IS NULL;

关键逻辑说明 #

1. 递归CTE：通过WITH RECURSIVE先抓取顶层节点，再递归关联子节点，逐层构建层级关系。
2. JSON聚合：用json_agg将当前模块的所有子节点聚合为JSON数组，赋值给children字段。
3. 最终组装：用json_build_object自定义每个节点的JSON结构，再通过array_to_json(array_agg(...))将顶层节点组合为完整的层级JSON数组。

适配自定义字段 #

如果你的表有额外字段（如description、sort_order），只需在json_build_object中添加对应的键值对即可，例如：

json_build_object(
    'id', id,
    'name', name,
    'description', description,
    'sort_order', sort_order,
    'children', children
)

你之前使用的array_to_json(array_agg(row_to_json(...)))只能将扁平化的行数据直接转为JSON数组，无法处理父子节点的关联聚合，因此需要用递归CTE来实现层级结构。

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