如何自动将Postgres大表的JSONB字段标准化并生成新表

方案一：纯SQL实现（推荐，性能最优） #

全程在Postgres内部执行，无数据导出导入开销，自动适配所有JSON键的超集，无需人工干预。

步骤1：提取全量JSON路径与对应字段类型 #

执行以下SQL，递归遍历所有JSONB的嵌套键（因为无嵌套数组，无需额外处理数组展开逻辑），生成建表所需的列定义和查询语句片段：

WITH RECURSIVE extract_json_paths AS (
    -- 第一层键提取
    SELECT
        ARRAY[key] AS full_path,
        jsonb_typeof(value) AS val_type,
        value
    FROM original_table,
         jsonb_each(data)
    UNION ALL
    -- 递归提取嵌套对象的键
    SELECT
        parent.full_path || child.key,
        jsonb_typeof(child.value) AS val_type,
        child.value
    FROM extract_json_paths parent,
         jsonb_each(parent.value) child
    WHERE parent.val_type = 'object'
)
SELECT
    -- 生成查询列片段，列名默认用下划线连接路径，需要带点可替换array_to_string的分隔符为'.'
    string_agg(
        DISTINCT format('data #>> %L AS %I', full_path, array_to_string(full_path, '_')),
        ', '
    ) AS select_columns,
    -- 生成建表列定义，自动适配类型，类型冲突时默认统一为text
    string_agg(
        DISTINCT format(
            '%I %s',
            array_to_string(full_path, '_'),
            CASE val_type
                WHEN 'number' THEN 'numeric'
                WHEN 'boolean' THEN 'boolean'
                WHEN 'string' THEN 'text'
                ELSE 'text'
            END
        ),
        ', '
    ) AS create_table_columns
FROM extract_json_paths;

步骤2：生成目标表 #

将步骤1查询得到的select_columns和create_table_columns替换到下方语句，执行即可直接生成标准化后的新表：

-- 方式1：直接用CTAS（Create Table As Select）一键生成，自动处理所有行的缺键补NULL
CREATE TABLE new_table AS
SELECT
    id,
    -- 此处替换为步骤1得到的select_columns内容
    data #>> '{hi}' AS hi,
    data #>> '{age}' AS age,
    data #>> '{bye}' AS bye
FROM original_table;

-- 方式2：如果需要提前定义约束、索引，先建表再插入
CREATE TABLE new_table (
    id INT PRIMARY KEY,
    -- 此处替换为步骤1得到的create_table_columns内容
    hi text,
    age numeric,
    bye text
);
INSERT INTO new_table
SELECT
    id,
    -- 此处替换为步骤1得到的select_columns内容
    data #>> '{hi}' AS hi,
    data #>> '{age}' AS age,
    data #>> '{bye}' AS bye
FROM original_table;

优势 #

• 全库内执行，数百万行数据通常几十秒到数分钟即可完成，远快于导出到Python处理的方案
• 自动适配所有历史JSON版本的键，无需人工梳理结构
• 无内存溢出风险，数据库原生优化批量处理逻辑

方案二：Python改进方案（适用于需要额外自定义数据处理逻辑的场景） #

解决原有分批处理的结构差异问题，无需加载全量数据到内存。

步骤1：预采集全量键集合 #

仅扫描全表的JSON结构，不加载实际值，收集所有存在的键的超集，存入Python的set结构，百万行数据的采集开销极低。也可以直接复用方案一的SQL查询拿到全量键列表，无需扫表。

步骤2：分批写入目标表 #

1. 提前用拿到的全量键列表创建目标表
2. 按每批1~10万行的粒度分批查询原表数据
3. 对每批数据的每行JSON，按全量键列表补全缺省键，值设为None
4. 批量插入目标表即可

优化提示 #

如果数据量超过千万行，可将建表语句改为CREATE UNLOGGED TABLE跳过WAL日志写入，插入完成后再执行ALTER TABLE new_table SET LOGGED转为普通表，速度可提升3~5倍。

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