Postgres：如何将表中异构JSON数据展开为列和行？

一、先把JSON拆成键值对行（通用基础操作） #

不管每行JSON是单个对象还是数组，都可以先把所有键值对拆成多行，同时保留原表的其他字段：

SELECT 
  t.id,
  t.other_col1, -- 替换成你表中的实际其他字段
  t.other_col2,
  j.key AS 字段名,
  j.value AS 字段值
FROM your_table t, -- 替换成你的表名
     LATERAL (
       -- 处理单个JSON对象的情况
       SELECT * FROM json_each_text(t.data_value)
       WHERE json_typeof(t.data_value) = 'object'
       UNION ALL
       -- 处理JSON数组的情况，先拆数组再拆键值对
       SELECT * FROM json_array_elements(t.data_value) arr, json_each_text(arr)
       WHERE json_typeof(t.data_value) = 'array'
     ) j;

这个查询会把每行的JSON内容拆成一行行的键值对，原表的其他字段会重复对应到每个键值对行——比如你提到的第二行如果是包含两个对象的数组，会生成6行（3个键×2个数组元素）。

二、将键值对转置为动态列（生成新列） #

如果需要把这些键变成实际的列（比如把rate作为列名，对应值填到列里），因为每行JSON结构不同，静态SQL无法直接实现，需要用**动态SQL+交叉表（crosstab）**的方式：

步骤1：创建动态展开的函数 #

执行以下PL/pgSQL函数（注意替换表名和其他字段）：

CREATE OR REPLACE FUNCTION expand_json_cols()
RETURNS SETOF RECORD AS $$
DECLARE
  all_columns TEXT;
BEGIN
  -- 先获取所有JSON里出现过的唯一键，拼接成列定义
  SELECT string_agg(DISTINCT quote_ident(j.key) || ' TEXT', ', ')
  INTO all_columns
  FROM your_table t,
       LATERAL (
         SELECT * FROM json_each_text(t.data_value)
         WHERE json_typeof(t.data_value) = 'object'
         UNION ALL
         SELECT * FROM json_array_elements(t.data_value) arr, json_each_text(arr)
       ) j;

  -- 生成并执行动态交叉表查询
  RETURN QUERY EXECUTE format(
    'SELECT 
       t.id,
       t.other_col1,
       t.other_col2,
       %s
     FROM crosstab(
       ''SELECT 
           t.id,
           t.other_col1,
           t.other_col2,
           j.key,
           j.value
         FROM your_table t,
              LATERAL (
                SELECT * FROM json_each_text(t.data_value)
                WHERE json_typeof(t.data_value) = ''''object''''
                UNION ALL
                SELECT * FROM json_array_elements(t.data_value) arr, json_each_text(arr)
              ) j
         ORDER BY 1,2,3'',
       ''SELECT DISTINCT j.key
         FROM your_table t,
              LATERAL (
                SELECT * FROM json_each_text(t.data_value)
                WHERE json_typeof(t.data_value) = ''''object''''
                UNION ALL
                SELECT * FROM json_array_elements(t.data_value) arr, json_each_text(arr)
              ) j
         ORDER BY 1''
     ) AS ct(id INT, other_col1 TEXT, other_col2 TEXT, %s)',
    all_columns, all_columns
  );
END;
$$ LANGUAGE plpgsql;

步骤2：调用函数获取结果 #

调用时需要指定返回的列结构（因为是动态列，必须明确列名和类型）：

SELECT * 
FROM expand_json_cols() 
AS (id INT, other_col1 TEXT, other_col2 TEXT, rate TEXT, "from" TEXT, "to" TEXT, fixed TEXT);

这里的列列表要包含原表的字段加上所有JSON里出现过的键，如果你不确定所有键，可以先执行第一步的查询获取所有唯一键。

三、针对固定结构的JSON子集展开 #

如果只是部分行的JSON结构固定，可以用json_to_record（单个对象）或json_to_recordset（数组）直接指定结构展开，不需要动态SQL：

单个JSON对象的行 #

SELECT 
  t.id,
  t.other_col1,
  j.*
FROM your_table t,
     json_to_record(t.data_value) j(rate TEXT);

JSON数组的行 #

SELECT 
  t.id,
  t.other_col1,
  j.*
FROM your_table t,
     json_to_recordset(t.data_value) j("from" TEXT, "to" TEXT, fixed TEXT);

注意事项 #

• 如果JSON里有嵌套的JSON对象，需要额外添加嵌套的拆分逻辑（比如再嵌套json_each_text）。
• 动态生成的列默认是TEXT类型，如果需要转成数字、日期等类型，需要在函数里修改列定义（比如把TEXT改成NUMERIC等）。
• 如果后续JSON里新增了新的键，需要重新执行函数定义，或者修改函数自动获取最新的键。

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