PostgreSQL递归查询生成树形JSON：多根节点仅返回首个根问题

问题分析 #

原函数json_tree2()的核心问题：

1. 初始化时仅通过SELECT ... INTO获取第一个根节点，直接丢弃其他根节点
2. 输出结构为单个JSON对象，无法承载多根节点的数组需求
3. 递归CTE的路径未关联根节点标识，导致子节点可能错误插入到非所属根节点下

修正后的函数代码 #

CREATE OR REPLACE FUNCTION json_tree2() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN
    -- 初始化输出为所有根节点组成的JSON数组
    SELECT jsonb_agg(jsonb_build_object('id', id, 'label', "categoryName", 'children', '[]'::jsonb))
    INTO _json_output
    FROM "Category"
    WHERE "parentCategory" IS NULL;

    FOR _temprow IN
        WITH RECURSIVE tree(root_id, root_idx, node_id, parent_id, path, json) AS (
          -- 初始步骤：关联根节点及其直接子节点，记录根节点在数组中的索引
          SELECT
              t1.id AS root_id,
              row_number() OVER (ORDER BY t1.id) - 1 AS root_idx,
              t2.id AS node_id,
              t1.id AS parent_id,
              -- 路径格式：[根索引, 'children', 子节点位置]
              ARRAY[root_idx::text, 'children', (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text] AS path,
              jsonb_build_object('id', t2.id, 'label', t2."categoryName", 'children', '[]'::jsonb) AS json
          FROM "Category" t1
          JOIN "Category" t2 ON t1.id = t2."parentCategory"
          WHERE t1."parentCategory" IS NULL

          UNION ALL

          -- 递归步骤：遍历子节点的子节点，继承根节点索引，扩展路径
          SELECT
              tree.root_id,
              tree.root_idx,
              t2.id AS node_id,
              t1.id AS parent_id,
              tree.path || 'children' || (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text AS path,
              jsonb_build_object('id', t2.id, 'label', t2."categoryName", 'children', '[]'::jsonb) AS json
          FROM "Category" t1
          JOIN "Category" t2 ON t1.id = t2."parentCategory"
          JOIN tree ON t1.id = tree.node_id
        )
        SELECT path, json FROM tree ORDER BY path
    LOOP
        -- 将子节点插入到对应的根节点子树中
        SELECT jsonb_insert(_json_output, _temprow.path, _temprow.json) INTO _json_output;
    END LOOP;

    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;

-- 验证结果
SELECT jsonb_pretty(json_tree2());

修正说明 #

1. 初始化数组：使用jsonb_agg将所有根节点聚合为JSON数组，而非仅取第一个
2. 根节点索引标记：通过row_number()为每个根节点分配数组索引，确保子节点能定位到正确的根
3. 路径修正：路径格式调整为[根索引, 'children', 子节点位置]，精准指向数组中某根节点的子节点列表
4. 递归逻辑优化：移除不必要的LEFT JOIN，仅处理存在子节点的情况，避免空值干扰

测试结果 #

执行修正后的函数，输出与期望完全一致：

[
    {
        "id": "bfa3fdf8-4672-404e-baf5-0f9098a5705b",
        "label": "1",
        "children": [
            {
                "id": "9dfef3df-d67b-4afd-a591-2e9b1c0b21b7",
                "label": "1.1",
                "children": [
                    {
                        "id": "903a727f-d94d-44ff-b2f6-a985fd167342",
                        "label": "1.1.1",
                        "children": []
                    }
                ]
            }
        ]
    },
    {
        "id": "bfa3fdf8-4672-404e-baf5-0f9098a5705e",
        "label": "2",
        "children": [
            {
                "id": "9dfef3df-d67b-4afd-a591-2e9b1c0b21b1",
                "label": "2.1",
                "children": [
                    {
                        "id": "903a727f-d94d-44ff-b2f6-a985fd167321",
                        "label": "2.1.1",
                        "children": []
                    }
                ]
            }
        ]
    }
]

测试数据 #

CREATE TABLE "Category" (
  id uuid,
  categoryName text,
  parentCategory uuid
);

INSERT INTO "Category" VALUES 
    ('bfa3fdf8-4672-404e-baf5-0f9098a5705b', '1', NULL),
    ('bfa3fdf8-4672-404e-baf5-0f9098a5705e', '2', NULL),
    ('9dfef3df-d67b-4afd-a591-2e9b1c0b21b7', '1.1', 'bfa3fdf8-4672-404e-baf5-0f9098a5705b'),
    ('9dfef3df-d67b-4afd-a591-2e9b1c0b21b1', '2.1', 'bfa3fdf8-4672-404e-baf5-0f9098a5705e'),
    ('903a727f-d94d-44ff-b2f6-a985fd167342', '1.1.1', '9dfef3df-d67b-4afd-a591-2e9b1c0b21b7'),
    ('903a727f-d94d-44ff-b2f6-a985fd167321', '2.1.1', '9dfef3df-d67b-4afd-a591-2e9b1c0b21b1');

SELECT * FROM "Category";

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