PostgreSQL中自动将JSONB动态映射为SQL表列的方法

针对你用PostgreSQL http扩展获取API返回的JSONB数组，想要自动将所有键值对转为表列的需求，可以通过动态SQL结合jsonb_object_keys实现，无需手动逐个指定列名和类型。

实现步骤与代码 #

核心思路是先从JSONB数组中提取所有唯一键，再动态生成jsonb_to_recordset的列定义，最后执行拼接好的查询语句。

1. 基础版：基于已有CTE的动态解析

假设你已经有sport_markets_api这个CTE，用以下代码自动生成列并查询：

DO $$
DECLARE
    cols TEXT;
    query TEXT;
BEGIN
    -- 提取JSONB数组中所有唯一键，生成默认TEXT类型的列定义
    SELECT string_agg(DISTINCT quote_ident(key) || ' TEXT', ', ')
    INTO cols
    FROM sport_markets_api,
         jsonb_array_elements(details) AS arr,
         jsonb_object_keys(arr) AS key;

    -- 拼接并执行完整查询
    query := format('
        SELECT col.*
        FROM sport_markets_api,
             jsonb_to_recordset(details) AS col(%s)
    ', cols);

    EXECUTE query;
END $$;

2. 整合版：API请求+动态解析一步完成

如果要把API请求和动态解析整合在一起，直接执行以下代码即可：

DO $$
DECLARE
    cols TEXT;
    query TEXT;
BEGIN
    -- 先获取API数据并提取所有唯一键
    WITH sport_markets_api AS (
        SELECT ((CONTENT::jsonb ->> 'data')::jsonb ->> 'sportMarkets')::jsonb AS details 
        FROM http_post(
            'https://api.thegraph.com/subgraphs/name/',
            '{"query": "{sportMarkets(first:2,skip:0,orderBy:timestamp,orderDirection:desc){id,timestamp,address,gameId,maturityDate,tags,isOpen,isResolved,isCanceled,finalResult,homeTeam,awayTeam }}"}'::text,
            'application/json')
    )
    SELECT string_agg(DISTINCT quote_ident(key) || ' TEXT', ', ')
    INTO cols
    FROM sport_markets_api,
         jsonb_array_elements(details) AS arr,
         jsonb_object_keys(arr) AS key;

    -- 拼接包含API请求的完整查询并执行
    query := format('
        WITH sport_markets_api AS (
            SELECT ((CONTENT::jsonb ->> ''data'')::jsonb ->> ''sportMarkets'')::jsonb AS details 
            FROM http_post(
                ''https://api.thegraph.com/subgraphs/name/'',
                ''{"query": "{sportMarkets(first:2,skip:0,orderBy:timestamp,orderDirection:desc){id,timestamp,address,gameId,maturityDate,tags,isOpen,isResolved,isCanceled,finalResult,homeTeam,awayTeam }}"}''::text,
                ''application/json'')
        )
        SELECT col.*
        FROM sport_markets_api,
             jsonb_to_recordset(details) AS col(%s)
    ', cols);

    EXECUTE query;
END $$;

优化：自动推断列类型 #

如果需要更精准的列类型（比如布尔、数字），可以结合jsonb_typeof自动推断，修改列定义的生成逻辑：

SELECT string_agg(DISTINCT quote_ident(key) || ' ' || 
    CASE jsonb_typeof(arr->key)
        WHEN 'boolean' THEN 'BOOLEAN'
        WHEN 'number' THEN 'NUMERIC'
        WHEN 'array' THEN 'JSONB'
        ELSE 'TEXT'
    END, ', ')
INTO cols
FROM sport_markets_api,
     jsonb_array_elements(details) AS arr,
     jsonb_object_keys(arr) AS key;

注意：如果数组中存在null值，jsonb_typeof会返回'null'，这里默认归为TEXT类型，你可以根据需求调整分支逻辑。

注意事项 #

• 执行动态SQL需要有足够的权限调用EXECUTE；
• 如果API返回的JSON对象键不统一，string_agg(DISTINCT ...)会保留所有出现过的键，缺失该键的行对应列会显示为null；
• 上述方法仅解析顶层键，嵌套JSON结构会保留为JSONB类型，如需进一步解析可嵌套使用相同逻辑。

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