从PostgreSQL 15生成符合权限的有序JSON菜单树

针对你遇到的重复节点和父节点丢失问题，以下是修正后的递归CTE查询，严格符合需求规则，能生成前端所需的嵌套JSON菜单结构：

WITH RECURSIVE accessible_modules AS (
    -- 锚点：获取用户1有权限的符合条件的终端模块
    SELECT 
        m.id_module,
        m.parent_id,
        m.name,
        m.file,
        m.group_order
    FROM modules m
    JOIN users_modules um ON m.id_module = um.id_module
    JOIN users u ON um.id_user = u.id_user
    WHERE 
        u.id_user = 1
        AND u.status = 'A'
        AND m.id_subsystem = 1
        AND m.status = 'A'
        AND m.file IS NOT NULL
    UNION
    -- 递归向上获取所有父节点（父节点需属于子系统1且状态激活）
    SELECT 
        m.id_module,
        m.parent_id,
        m.name,
        m.file,
        m.group_order
    FROM modules m
    JOIN accessible_modules am ON m.id_module = am.parent_id
    WHERE 
        m.id_subsystem = 1
        AND m.status = 'A'
),
distinct_modules AS (
    -- 去重：避免同一父节点被多次递归引入导致重复
    SELECT DISTINCT ON (id_module) *
    FROM accessible_modules
    ORDER BY id_module
),
menu_tree AS (
    -- 初始化根节点（无父节点的模块）
    SELECT 
        id_module,
        parent_id,
        name,
        file,
        group_order,
        '[]'::json AS children
    FROM distinct_modules
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归构建子节点嵌套结构，按group_order排序
    SELECT 
        dm.id_module,
        dm.parent_id,
        dm.name,
        dm.file,
        dm.group_order,
        (
            SELECT json_agg(child ORDER BY child.group_order)
            FROM (
                SELECT 
                    mt.id_module,
                    mt.name,
                    mt.file,
                    mt.group_order,
                    mt.children
                FROM menu_tree mt
                WHERE mt.parent_id = dm.id_module
            ) child
        ) AS children
    FROM distinct_modules dm
    JOIN menu_tree mt ON dm.id_module = mt.parent_id
)
-- 输出最终的菜单JSON树
SELECT 
    json_build_object(
        'id', id_module,
        'name', name,
        'file', file,
        'children', children
    ) AS menu_json
FROM menu_tree
WHERE parent_id IS NULL
ORDER BY group_order;

关键修正点说明： #

1. 补全父节点获取逻辑：递归部分不再限制父节点必须有file，只要属于子系统1且状态激活就会被纳入，解决Settings节点丢失的问题。
2. 添加去重步骤：通过distinct_modules CTE用DISTINCT ON (id_module)去重，避免多个子节点递归到同一父节点时产生重复记录，解决Registrations子项重复的问题。
3. 严格按group_order排序：在聚合子节点时明确按group_order排序，确保菜单顺序符合定义。
4. 结构化JSON输出：用json_build_object整理出前端易解析的字段结构，包含模块ID、名称、关联文件和子节点数组。

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