PostgreSQL中JSON列嵌套键值对转列的实现需求

问题分析 #

你用json_array_elements拆分JSON数组后得到多行，是因为这个函数会将数组的每个元素拆分为独立行，而你需要将这些元素的键值对聚合回原表的单行，同时把每个键转为单独的列。

解决方案 #

方法1：已知目标键名（静态提取） #

如果已经明确要提取哪些键（比如GA4常见的page_location、page_title、session_id等），可以先将JSON数组聚合为键值对对象，再从中提取对应列：

SELECT
  -- 保留原表需要的列，也可以用a.*直接取所有列
  a.id, a.event_name, a.event_timestamp,
  -- 用COALESCE适配GA4不同类型的value字段
  COALESCE((params->'page_location')->>'string_value', (params->'page_location')->>'int_value') AS page_location,
  COALESCE((params->'page_title')->>'string_value', (params->'page_title')->>'int_value') AS page_title,
  COALESCE((params->'session_id')->>'string_value', (params->'session_id')->>'int_value') AS session_id
FROM (
  SELECT
    *,
    -- 将数组元素聚合为{key: value}格式的JSON对象
    json_object_agg(ep->>'key', ep->'value') AS params
  FROM public.ga4
  CROSS JOIN json_array_elements(event_params::json) AS ep
  -- 按原表主键/唯一标识分组，确保聚合后回到原表每行
  GROUP BY ga4.id, ga4.event_name, ga4.event_timestamp
) a
LIMIT 100;

方法2：动态生成所有键对应的列 #

如果不确定所有键名，需要自动提取所有存在的键并转为列，可以用动态SQL实现：

第一步：先查询所有唯一键名

SELECT DISTINCT ep->>'key' AS key_name
FROM public.ga4
CROSS JOIN json_array_elements(event_params::json) AS ep;

第二步：用PL/pgSQL生成并执行动态SQL

DO $$
DECLARE
  key_list TEXT[];
  dynamic_sql TEXT;
BEGIN
  -- 获取所有唯一键名
  SELECT ARRAY_AGG(DISTINCT ep->>'key') INTO key_list
  FROM public.ga4
  CROSS JOIN json_array_elements(event_params::json) AS ep;

  -- 拼接动态SQL，自动生成每个键对应的列
  dynamic_sql := '
    SELECT
      a.*, ' ||
      STRING_AGG(
        'COALESCE((params->' || quote_literal(k) || ')->>''string_value'', (params->' || quote_literal(k) || ')->>''int_value'', (params->' || quote_literal(k) || ')->>''float_value'') AS ' || quote_ident(k),
        ', '
      ) || '
    FROM (
      SELECT
        *,
        json_object_agg(ep->>''key'', ep->''value'') AS params
      FROM public.ga4
      CROSS JOIN json_array_elements(event_params::json) AS ep
      GROUP BY ga4.id -- 替换为你的表主键或所有非聚合列
    ) a
    LIMIT 100;';

  -- 执行动态SQL
  EXECUTE dynamic_sql;
END $$;

关键说明 #

• json_object_agg是核心：它将拆分后的数组元素（每个键值对）聚合为一个JSON对象，这样就能在原表的每行中通过键名直接提取值。
• 适配GA4的value结构：GA4的value字段会根据数据类型存储在string_value、int_value等子键中，用COALESCE可以自动匹配存在的类型。
• 分组注意事项：GROUP BY必须包含原表中所有不需要聚合的列（通常是主键或唯一标识列），确保聚合后不会合并原表的行。

内容的提问来源于stack exchange，提问作者UDAY REDDY D