PostgreSQL分层表递归查询生成嵌套JSON及详情重复问题解决

场景与表结构 #

现有PostgreSQL关联表构成人员层级结构，包含人员信息、详情信息及关联关系：

CREATE TABLE persons (
    person_id serial primary key,
    parent_person_id int REFERENCES persons (person_id),
    person_name text
);

INSERT INTO persons (person_id,parent_person_id,person_name) VALUES
    (1,NULL,'Grandpa'),
    (2,1,'Dad'),
    (3,2,'Me'),
    (4,1,'Uncle'),
    (5,4,'Cousin'),
    (6,4,'Other Cousin'),
    (7,NULL,'Bachelor Great Uncle');
    
CREATE TABLE details (
    detail_id serial primary key,
    detail text
);

INSERT INTO details (detail_id,detail) VALUES
    (1,'beard'),
    (2,'combover'),
    (3,'long-windedness'),
    (4,'pot-belly');

CREATE TABLE person_details (
    person_id int NOT NULL REFERENCES persons (person_id),
    detail_id int NOT NULL REFERENCES details (detail_id)
);

INSERT INTO person_details (person_id, detail_id) VALUES
    (1,2),
    (1,3),
    (2,2),
    (3,1),
    (3,3),
    (4,2),
    (4,4),
    (4,3),
    (5,1),
    (6,2),
    (6,4);

该层级支持任意深度，存在多个根节点（parent_person_id为NULL的人员），每个人员可关联0或多个详情。

需求 #

生成嵌套JSON结构，每个节点包含人员ID、名称、详情列表、子节点列表，子节点嵌套在对应父节点中，示例输出：

{
    "person ID": 1,
    "person Name": "Grandpa",
    "details": [
        {"detail": "combover"},
        {"detail": "long-windedness"}
    ],
    "descendents": [
        {
            "person ID": 2,
            "person Name": "Dad",
            "details": [
                {"detail":"combover"}
            ],
            "descendents": [
                {
                    "person ID": 3, 
                    "person Name": "Me",
                    "details": [
                        {"detail": "beard"},
                        {"detail": "long-windedness"}
                    ]
                }
            ]
        },
        {
            "person ID": 4,
            "person Name": "Uncle",
            "details": [
                {"detail": "combover"},
                {"detail": "pot-belly"},
                {"detail": "long-windedness"}
            ],
            "descendents": [
                {
                    "person ID": 5, 
                    "person Name": "Cousin",
                    "details":[
                        {"detail": "beard"}
                    ]
                },
                {
                    "person ID": 6, 
                    "person Name": "Other Cousin",
                    "details":[
                        {"detail": "combover"},
                        {"detail": "pot-belly"}
                    ]
                }
            ]
        }
    ]
}

当前实现与问题 #

原实现通过递归视图聚合详情，再用PL/pgSQL函数动态生成视图构建嵌套JSON，但存在多子节点人员的details字段重复问题：当人员有N个子节点时，其详情列表会被重复聚合N次，导致details数组中出现N个相同的详情集合。

问题根源：原视图details_by_person已为每个人聚合了详情，但在后续CTE关联子节点时，每个子节点对应一行数据，此时调用json_agg(details_by_person.details)会将同一个人的详情重复聚合N次（N为子节点数量）。

解决方案 #

无需动态生成视图，直接通过单次预聚合详情+递归CTE构建嵌套结构即可解决问题，步骤如下：

1. 预聚合人员详情（可选，也可合并到递归CTE中） #

先一次性聚合所有人员的详情，避免后续递归中重复处理：

CREATE OR REPLACE VIEW person_details_agg AS
SELECT 
    p.person_id,
    p.person_name,
    p.parent_person_id,
    -- 无详情时返回空数组
    COALESCE(jsonb_agg(jsonb_build_object('detail', d.detail)), '[]'::jsonb) AS details
FROM persons p
LEFT JOIN person_details pd ON p.person_id = pd.person_id
LEFT JOIN details d ON pd.detail_id = d.detail_id
GROUP BY p.person_id, p.person_name, p.parent_person_id;

2. 递归CTE生成嵌套JSON #

利用PostgreSQL递归CTE直接构建任意深度的嵌套结构，仅在预聚合时处理一次详情，递归过程仅负责嵌套子节点：

WITH RECURSIVE family_hierarchy AS (
    -- 根节点：无父节点的人员，初始子节点为空数组
    SELECT 
        person_id,
        parent_person_id,
        jsonb_build_object(
            'person ID', person_id,
            'person Name', person_name,
            'details', details,
            'descendents', '[]'::jsonb
        ) AS node_json
    FROM person_details_agg
    WHERE parent_person_id IS NULL

    UNION ALL

    -- 递归处理子节点：将子节点的JSON聚合到父节点的descendents中
    SELECT 
        parent.person_id,
        parent.parent_person_id,
        -- 更新父节点的descendents为聚合后的子节点数组
        jsonb_set(
            parent.node_json,
            '{descendents}',
            jsonb_agg(child.node_json)
        ) AS node_json
    FROM family_hierarchy parent
    JOIN person_details_agg child ON parent.person_id = child.parent_person_id
    GROUP BY parent.person_id, parent.parent_person_id, parent.node_json
)
-- 输出所有根节点的完整嵌套结构，用jsonb_pretty格式化输出
SELECT jsonb_pretty(node_json) AS family_tree
FROM family_hierarchy
WHERE parent_person_id IS NULL;

效果说明 #

• 每个人员的详情仅聚合一次，避免重复
• 自动适配任意层级深度，无需动态生成SQL
• 支持多根节点输出（若有多个根节点，会分别输出每个根节点的完整树）

内容的提问来源于stack exchange，提问作者M. Andersen