PostgreSQL递归行转换为JSONB映射实现方案问询

针对你给出的PostgreSQL表结构，我会一步步展示如何把category的树形递归结构转换为嵌套的JSONB，同时如果需要关联event表也会给出相应的扩展方案。

1. 先搞定Category的树形JSONB结构 #

首先，我们利用PostgreSQL的递归CTE（公共表表达式）来遍历category的父子关系，再结合JSON聚合函数生成嵌套结构。

第一步：用递归CTE梳理树形路径

先写一个递归CTE把每个节点的层级关系理清楚：

WITH RECURSIVE category_tree AS (
    -- 锚点：所有顶级组织（parent_id为空的行）
    SELECT
        id,
        type,
        label,
        parent_id,
        ARRAY[id] AS path
    FROM category
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归关联子节点
    SELECT
        c.id,
        c.type,
        c.label,
        c.parent_id,
        ct.path || c.id AS path
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

这个查询会输出每个节点的完整路径，方便后续聚合子节点。

第二步：生成嵌套JSONB

接下来用jsonb_agg和jsonb_build_object把子节点嵌套到父节点中：

WITH RECURSIVE category_tree AS (
    SELECT
        id,
        type,
        label,
        parent_id,
        ARRAY[id] AS path
    FROM category
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT
        c.id,
        c.type,
        c.label,
        c.parent_id,
        ct.path || c.id AS path
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
),
category_with_children AS (
    SELECT
        ct_parent.id,
        ct_parent.type,
        ct_parent.label,
        jsonb_agg(
            jsonb_build_object(
                'id', ct_child.id,
                'type', ct_child.type,
                'label', ct_child.label
            )
        ) AS children
    FROM category_tree ct_parent
    LEFT JOIN category_tree ct_child ON ct_child.parent_id = ct_parent.id
    WHERE ct_parent.parent_id IS NULL -- 只处理顶级组织节点
    GROUP BY ct_parent.id, ct_parent.type, ct_parent.label
)
SELECT
    jsonb_agg(
        jsonb_build_object(
            'id', id,
            'type', type,
            'label', label,
            'children', children
        )
    ) AS category_jsonb
FROM category_with_children;

执行后会得到这样的JSONB结果：

[
  {
    "id": 1,
    "type": "organisation",
    "label": "Google",
    "children": [{"id":2,"type":"product","label":"Gmail"}]
  },
  {
    "id":3,
    "type":"organisation",
    "label":"Apple",
    "children": [
      {"id":4,"type":"product","label":"iPhone"},
      {"id":5,"type":"product","label":"Mac"}
    ]
  }
]

2. 关联Event表扩展JSONB结构 #

看你给出的event表没写完，我假设它有category_id字段关联category表，完整结构大概是这样：

create table event (
 id integer primary key,
 name varchar(255),
 category_id integer references category(id),
 event_date timestamp
);

如果要把每个分类对应的事件也嵌套进JSONB，可以调整查询，加入event的聚合：

WITH RECURSIVE category_tree AS (
    SELECT
        c.id,
        c.type,
        c.label,
        c.parent_id,
        -- 聚合当前分类下的所有事件
        (SELECT jsonb_agg(
            jsonb_build_object(
                'event_id', e.id,
                'event_name', e.name,
                'event_date', e.event_date
            )
        ) FROM event e WHERE e.category_id = c.id) AS events
    FROM category c
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT
        c.id,
        c.type,
        c.label,
        c.parent_id,
        (SELECT jsonb_agg(
            jsonb_build_object(
                'event_id', e.id,
                'event_name', e.name,
                'event_date', e.event_date
            )
        ) FROM event e WHERE e.category_id = c.id) AS events
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
),
category_with_children AS (
    SELECT
        ct_parent.id,
        ct_parent.type,
        ct_parent.label,
        ct_parent.events,
        jsonb_agg(
            jsonb_build_object(
                'id', ct_child.id,
                'type', ct_child.type,
                'label', ct_child.label,
                'events', ct_child.events
            )
        ) AS children
    FROM category_tree ct_parent
    LEFT JOIN category_tree ct_child ON ct_child.parent_id = ct_parent.id
    WHERE ct_parent.parent_id IS NULL
    GROUP BY ct_parent.id, ct_parent.type, ct_parent.label, ct_parent.events
)
SELECT
    jsonb_agg(
        jsonb_build_object(
            'id', id,
            'type', type,
            'label', label,
            'events', events,
            'children', children
        )
    ) AS category_event_jsonb
FROM category_with_children;

这样生成的JSONB会包含每个分类下的事件列表，层级结构保持完整。

核心函数说明 #

• WITH RECURSIVE：PostgreSQL实现树形遍历的核心，通过锚点+递归成员遍历所有节点
• jsonb_build_object：自定义JSONB对象的键值对，灵活控制输出字段
• jsonb_agg：将多行数据聚合为JSONB数组，实现嵌套结构
• LEFT JOIN：保证没有子节点的父级也能被保留（如果不需要可以换成INNER JOIN）

如果需要调整JSON结构，比如重命名字段、过滤某些数据，直接修改jsonb_build_object里的内容或者添加WHERE条件即可。

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