如何编写PostgreSQL递归查询生成部门JSON树形结构？

现有departments表结构包含id、parent_id、name，数据如下：

需要编写PostgreSQL递归查询生成指定格式的部门JSON树形结构（目标格式如下），但当前递归查询只能返回指定部门的上级，无法生成目标JSON，求正确写法。

目标JSON格式：

[
  {
    "departmentId": 0,
    "departmentName": "Отдел 1",
    "children": [
      {
        "departmentId": 1,
        "departmentName": "Отдел 1.1",
        "children": [
          {
            "departmentId": 2,
            "departmentName": "Отдел 1.1.1",
            "children": [
              {
                "departmentId": 5,
                "departmentName": "Отдел 1.1.1.1",
                "children": null
              }
            ]
          }
        ]
      }
    ]
  },
  {
    "departmentId": 3,
    "departmentName": "Отдел 2",
    "children": [
      {
        "departmentId": 4,
        "departmentName": "Отдел 2.1",
        "children": null
      }
    ]
  }
]

当前错误查询：

WITH recursive parents AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE id = '798c66092ef24ec488ef308291947913'
  UNION ALL
  SELECT dep.id, dep.name, dep.parent_id
  FROM departments dep
  JOIN parents ON parents.parent_id = dep.id
)
SELECT *
FROM parents
WHERE id != '798c66092ef24ec488ef308291947913'
order by id;

解决方案 #

正确的递归查询需要从顶级部门（parent_id IS NULL）开始，递归收集子部门，并结合PostgreSQL的JSON函数构建树形结构：

WITH RECURSIVE department_tree AS (
    -- 基础查询：获取所有顶级部门
    SELECT
        id AS "departmentId",
        name AS "departmentName",
        ARRAY[]::INT[] AS path,
        NULL::JSONB AS children
    FROM departments
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询：遍历所有子部门，记录路径
    SELECT
        d.id AS "departmentId",
        d.name AS "departmentName",
        dt.path || d.id,
        NULL::JSONB AS children
    FROM departments d
    JOIN department_tree dt ON d.parent_id = dt."departmentId"
),
-- 为每个节点聚合子节点集合
department_with_children AS (
    SELECT
        "departmentId",
        "departmentName",
        (
            SELECT COALESCE(jsonb_agg(jsonb_strip_nulls(row_to_json(child))), NULL)
            FROM department_tree child
            WHERE child.path[:array_length(child.path, 1)-1] = parent.path
        ) AS children
    FROM department_tree parent
)
-- 输出顶级部门的完整树形结构
SELECT jsonb_pretty(jsonb_agg(jsonb_strip_nulls(row_to_json(top)))) AS department_tree_json
FROM department_with_children top
WHERE top.path = ARRAY[]::INT[];

关键说明： #

1. department_tree递归CTE：先抓取所有无父级的顶级部门，再递归遍历所有子部门，用path数组记录部门的层级路径，方便后续匹配父节点。
2. department_with_childrenCTE：通过子查询为每个部门聚合其直接子节点，COALESCE确保无子女时返回null（若需要空数组，可将NULL替换为'[]'::JSONB）。
3. 最终输出：仅筛选顶级部门，用jsonb_agg聚合为JSON数组，jsonb_pretty格式化输出便于阅读。

如果需要生成某个特定部门的子树，只需修改基础查询的条件，比如将WHERE parent_id IS NULL改为WHERE id = 0，即可生成Отдел 1的完整树形结构。

内容的提问来源于stack exchange，提问作者Татьяна Задорожняя