将递归结构(在Postgres sql中存储为json )展平为平面列表

-- 创建包含递归结构的表
CREATE TABLE recursive_data (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 插入递归数据
INSERT INTO recursive_data (data) VALUES
    ('{"name": "A", "children": [{"name": "B", "children": [{"name": "C", "children": []}]}, {"name": "D", "children": []}]}'),
    ('{"name": "E", "children": []}');

-- 使用递归查询展开递归结构
WITH RECURSIVE flatten_data AS (
    SELECT id, jsonb_array_elements(data->'children') AS child
    FROM recursive_data
    UNION ALL
    SELECT id, jsonb_array_elements(child->'children')
    FROM flatten_data
    WHERE child->'children' IS NOT NULL
)
-- 将展开后的数据插入到新表中
INSERT INTO flattened_data (id, name)
SELECT id, child->>'name'
FROM flatten_data;

-- 查询展平后的平面列表
SELECT * FROM flattened_data;