PostgreSQL动态JSON列转表格输出：兼容不同键与混合值类型

适用场景说明 #

针对JSON字段键不固定、同键名值类型异构的需求，提供两种可直接落地的实现方案，假设你的表名为my_table，包含主键字段id和JSON类型字段foo。

方案1：键值对行式展开（无需提前知晓所有键） #

该方案将每行JSON的每个键值对拆为独立行，天然适配键不固定的场景，同时可保留原值类型标识：

SELECT
  t.id, -- 原表主键，可关联回原行数据
  j.key, -- JSON键名
  j.value, -- JSON原值（jsonb类型）
  jsonb_typeof(j.value) AS value_type -- 自动识别值类型：string/number/boolean/null等
FROM my_table t
CROSS JOIN jsonb_each(t.foo::jsonb) j; -- 统一转jsonb类型使用更丰富的处理函数

输出示例 #

如果你的foo字段本身就是jsonb类型，可去掉::jsonb类型转换。

方案2：宽表格式展开（一行原数据对应一行结果） #

如果需要输出传统二维宽表结构，可通过动态SQL自动适配所有存在的JSON键，统一用文本类型接收值解决异构兼容问题：

第一步：执行动态SQL生成查询语句 #

DO $$
DECLARE
    column_sql text;
    final_query text;
BEGIN
    -- 自动提取所有唯一的JSON键，拼接为查询列
    SELECT string_agg(
        format('(foo::jsonb ->> %L) AS %I', key, key),
        ', '
    ) INTO column_sql
    FROM (SELECT DISTINCT jsonb_object_keys(foo::jsonb) AS key FROM my_table) AS all_keys;

    -- 组装完整查询语句
    final_query := format('SELECT id, %s FROM my_table ORDER BY id', column_sql);

    -- 打印生成的SQL，可直接复制执行
    RAISE NOTICE '生成的查询SQL：%', final_query;
    -- 如需直接将结果存入新表，可替换为：EXECUTE format('CREATE TABLE expanded_foo AS %s', final_query);
END $$;

第二步：执行生成的静态SQL得到结果 #

执行上述代码后会自动生成类似如下的查询语句，直接运行即可得到宽表：

SELECT 
  id,
  (foo::jsonb ->> 'a') AS a,
  (foo::jsonb ->> 'b') AS b,
  (foo::jsonb ->> 'c') AS c,
  (foo::jsonb ->> 'd') AS d
FROM my_table ORDER BY id;

输出示例 #

使用->>运算符取值会自动将所有JSON类型转换为文本，完全兼容同键不同值类型的场景，无需额外做类型兼容处理。

优化建议 #

• 数据量较大的场景建议将foo字段改为jsonb类型，并创建GIN索引，可大幅提升JSON处理效率
• 新增JSON键后重新执行一次动态SQL即可自动适配新键

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