如何高效遍历并合并PostgreSQL中的嵌套JSON数据？

不用创建几百张表，直接用PostgreSQL的JSON数组展开函数+横向连接就能一次性提取所有层级的末端数据，最终得到一张包含所有展开记录的表，具体实现如下：

核心思路 #

逐层展开嵌套的JSON数组：

• 第一层：展开pro对应的p数组（元素0、1）
• 第二层：展开每个p元素里的s数组（元素0-44）
• 第三层：展开每个s元素里的c数组（元素0-16）
• 最后从每个c元素中提取需要的字段，同时保留原表的基础字段

完整SQL示例 #

CREATE TABLE all_extracted_data AS
SELECT
    title,
    path,
    "user",  -- user是PostgreSQL关键字，需加双引号
    userP,
    active,
    created,
    updated,
    -- 提取c对象中的字段
    (c_obj->>'signup')::text AS signup,
    (c_obj->>'finish')::text AS finish,
    (c_obj->>'cost')::numeric AS cost,  -- 若cost是数值类型可转换
    (c_obj->>'status')::text AS status,
    (c_obj->>'startat')::timestamp AS startat,  -- 若startat是时间类型可转换
    -- 可选：保留层级索引，方便追溯来源
    p_idx AS p_level,
    s_idx AS s_level,
    c_idx AS c_level
FROM
    db_st,
    -- 第一层：展开pro数组，带索引
    json_array_elements(pro::json) WITH ORDINALITY AS p(p_obj, p_idx),
    -- 第二层：展开每个p元素里的s数组，带索引
    json_array_elements(p_obj->'s') WITH ORDINALITY AS s(s_obj, s_idx),
    -- 第三层：展开每个s元素里的c数组，带索引
    json_array_elements(s_obj->'c') WITH ORDINALITY AS c(c_obj, c_idx);

关键说明 #

1. json_array_elements：用于将JSON数组拆分为多行记录，每条记录对应数组中的一个元素
2. WITH ORDINALITY：可选，用来获取元素在数组中的索引（比如p_level=0/1，s_level=0-44等），方便后续追溯数据来源层级
3. 类型转换：根据实际字段类型，把提取的文本（->>返回text）转换为对应类型（比如numeric、timestamp），方便后续分析
4. 空值处理：如果某些层级可能为空，把逗号分隔的隐式连接改成LEFT JOIN LATERAL，避免丢失原表数据：

-- 空值兼容版本
CREATE TABLE all_extracted_data AS
SELECT
    title,
    path,
    "user",
    userP,
    active,
    created,
    updated,
    COALESCE((c_obj->>'signup')::text, '') AS signup,
    COALESCE((c_obj->>'finish')::text, '') AS finish,
    COALESCE((c_obj->>'cost')::numeric, 0) AS cost,
    COALESCE((c_obj->>'status')::text, '') AS status,
    COALESCE((c_obj->>'startat')::timestamp, '1970-01-01'::timestamp) AS startat,
    p_idx AS p_level,
    s_idx AS s_level,
    c_idx AS c_level
FROM
    db_st
LEFT JOIN LATERAL json_array_elements(pro::json) WITH ORDINALITY AS p(p_obj, p_idx) ON true
LEFT JOIN LATERAL json_array_elements(p_obj->'s') WITH ORDINALITY AS s(s_obj, s_idx) ON true
LEFT JOIN LATERAL json_array_elements(s_obj->'c') WITH ORDINALITY AS c(c_obj, c_idx) ON true;

这样执行一次就能得到所有层级的末端数据，不用手动创建几百张表，效率和可维护性都高很多。

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