如何在PostgreSQL中从JSONB列生成含嵌套分表的结构（支持动态JSON）

一、手动创建关联表（针对固定JSON结构） #

假设原表为existing_table，存储JSON数据的JSONB列名为jsonb_column，以下是针对示例JSON结构的分步实现：

1. 创建主表（存储顶层字段） #

-- 创建主表donuts，存储JSON顶层的id、type、name、ppu字段
CREATE TABLE donuts (
    id VARCHAR PRIMARY KEY,
    type VARCHAR NOT NULL,
    name VARCHAR,
    ppu DECIMAL(5,2)
);

-- 从原表JSONB列提取数据插入主表
INSERT INTO donuts (id, type, name, ppu)
SELECT
    (jsonb_column ->> 'id')::VARCHAR,
    (jsonb_column ->> 'type')::VARCHAR,
    (jsonb_column ->> 'name')::VARCHAR,
    (jsonb_column ->> 'ppu')::DECIMAL(5,2)
FROM existing_table;

2. 创建batter关联表 #

针对JSON中嵌套的batters.batter数组，创建独立表并通过外键关联主表：

-- 创建batter表，包含与主表的关联外键
CREATE TABLE batters (
    id VARCHAR PRIMARY KEY,
    type VARCHAR NOT NULL,
    donut_id VARCHAR NOT NULL REFERENCES donuts(id)
);

-- 展开JSON数组并插入数据
INSERT INTO batters (id, type, donut_id)
SELECT
    (batter ->> 'id')::VARCHAR,
    (batter ->> 'type')::VARCHAR,
    (jsonb_column ->> 'id')::VARCHAR AS donut_id
FROM existing_table,
     jsonb_array_elements(jsonb_column -> 'batters' -> 'batter') AS batter;

3. 创建topping关联表 #

同理处理topping数组：

-- 创建topping表
CREATE TABLE toppings (
    id VARCHAR PRIMARY KEY,
    type VARCHAR NOT NULL,
    donut_id VARCHAR NOT NULL REFERENCES donuts(id)
);

-- 插入数据
INSERT INTO toppings (id, type, donut_id)
SELECT
    (topping ->> 'id')::VARCHAR,
    (topping ->> 'type')::VARCHAR,
    (jsonb_column ->> 'id')::VARCHAR AS donut_id
FROM existing_table,
     jsonb_array_elements(jsonb_column -> 'topping') AS topping;

二、动态处理可变JSON结构 #

如果JSON结构不固定，无法提前确定字段，可以通过PostgreSQL的系统函数和PL/pgSQL实现自动化处理：

1. 分析JSON结构 #

先提取JSON的所有键和对应的数据类型，为动态建表做准备：

-- 获取JSON顶层字段及其对应的数据类型
SELECT
    key,
    jsonb_typeof(jsonb_column -> key) AS json_type,
    -- 映射为PostgreSQL原生类型
    CASE jsonb_typeof(jsonb_column -> key)
        WHEN 'string' THEN 'VARCHAR'
        WHEN 'number' THEN 'DECIMAL'
        WHEN 'boolean' THEN 'BOOLEAN'
        ELSE 'JSONB'
    END AS pg_type
FROM existing_table,
     jsonb_object_keys(jsonb_column) AS key
GROUP BY key, json_type;

2. 动态生成建表语句 #

编写PL/pgSQL函数，自动根据JSON结构生成主表：

CREATE OR REPLACE FUNCTION generate_main_table(table_name VARCHAR, source_table VARCHAR, jsonb_col VARCHAR)
RETURNS VOID AS $$
DECLARE
    rec RECORD;
    create_sql TEXT := 'CREATE TABLE ' || quote_ident(table_name) || ' (';
    first_col BOOLEAN := TRUE;
BEGIN
    -- 遍历所有顶层字段
    FOR rec IN
        SELECT
            key,
            CASE jsonb_typeof(jsonb_col_val)
                WHEN 'string' THEN 'VARCHAR'
                WHEN 'number' THEN 'DECIMAL'
                WHEN 'boolean' THEN 'BOOLEAN'
                ELSE 'JSONB'
            END AS pg_type
        FROM (
            SELECT
                key,
                jsonb_column -> key AS jsonb_col_val
            FROM ' || quote_ident(source_table) || ',
                 jsonb_object_keys(' || quote_ident(jsonb_col) || ') AS key
            GROUP BY key, jsonb_col_val
        ) AS cols
    LOOP
        IF NOT first_col THEN
            create_sql := create_sql || ', ';
        END IF;
        -- 自动添加主键（假设id字段为唯一标识）
        IF rec.key = 'id' THEN
            create_sql := create_sql || quote_ident(rec.key) || ' ' || rec.pg_type || ' PRIMARY KEY';
        ELSE
            create_sql := create_sql || quote_ident(rec.key) || ' ' || rec.pg_type;
        END IF;
        first_col := FALSE;
    END LOOP;
    create_sql := create_sql || ')';
    -- 执行建表语句
    EXECUTE create_sql;
END;
$$ LANGUAGE plpgsql;

3. 递归处理嵌套数组 #

对于嵌套的数组结构，可以扩展上述函数，检测到array类型的字段时，自动生成对应的关联表并建立外键关联。核心逻辑是递归解析JSON结构，生成子表的建表语句和数据插入语句。

三、关键注意事项 #

• 类型映射：需确保JSON数据类型与PostgreSQL原生类型正确匹配，避免数据精度丢失或类型错误。
• 数据一致性：通过外键约束维护主表与关联表之间的引用关系，防止脏数据。
• 性能优化：处理大量JSON数据前，建议为JSONB列创建GIN索引，提升字段提取和数组展开的效率。

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