PostgreSQL如何查询JSON类型列并将JSON数组元素转换为独立列

固定列场景（提前已知action_type枚举值，推荐） #

如果提前确定需要提取的action_type值（比如示例中的page_engagement、video_view），直接用JSON解析函数逐列提取即可，性能最高，写法最简单：
假设你的业务表名为action_log（替换为实际表名即可），actions为JSON类型列，查询语句如下：

SELECT
  -- 提取page_engagement对应的值
  (
    SELECT elem ->> 'value'
    FROM json_array_elements(actions) elem
    WHERE elem ->> 'action_type' = 'page_engagement'
  ) AS page_engagement,
  -- 提取video_view对应的值
  (
    SELECT elem ->> 'value'
    FROM json_array_elements(actions) elem
    WHERE elem ->> 'action_type' = 'video_view'
  ) AS video_view
FROM action_log;

如果你的actions列是JSONB类型，只需要把函数json_array_elements替换为jsonb_array_elements即可，其余语法完全一致。
如果需要提取的列较多，也可以用拆分数组+条件聚合的写法，逻辑等价：

SELECT
  id, -- 替换为你表的主键字段
  MAX(CASE WHEN elem ->> 'action_type' = 'page_engagement' THEN elem ->> 'value' END) AS page_engagement,
  MAX(CASE WHEN elem ->> 'action_type' = 'video_view' THEN elem ->> 'value' END) AS video_view
FROM action_log,
     json_array_elements(actions) elem
GROUP BY id; -- 按原表主键分组，保证每行对应原表的一条记录

注意：提取出的value默认是文本类型，如果需要转为数值、日期等其他类型，直接在取值后加类型转换即可，例如(elem ->> 'value')::INT。如果某行的数组中不存在对应action_type的对象，结果对应列会返回NULL。

动态列场景（action_type值不固定） #

SQL本身的查询列需要在语句执行前确定，如果action_type的取值不固定，无法提前枚举，需要借助tablefunc扩展的交叉表功能，或者通过动态SQL拼接列名实现：

1. 首先开启扩展（仅需执行一次，需要数据库超级用户权限）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 使用crosstab函数实现行转列，需要提前定义好返回的列名和类型：

SELECT * FROM crosstab(
  'SELECT
    t.id,
    elem ->> ''action_type'' AS action_type,
    elem ->> ''value'' AS action_value
  FROM action_log t,
       json_array_elements(t.actions) elem
  ORDER BY 1, 2',
  'SELECT DISTINCT elem ->> ''action_type''
  FROM action_log, json_array_elements(actions) elem
  ORDER BY 1'
) AS ct(
  id INT, -- 和原表主键类型保持一致
  page_engagement TEXT,
  video_view TEXT
  -- 存在其他action_type时，在这里按顺序补充列定义即可
);

如果列名完全动态无法提前确定，可以在应用层先查询所有不重复的action_type值，拼接生成上述SQL的列定义部分后再执行查询。

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