PostgreSQL从JSONB对象数组提取指定键值并转为列查询

假设你的表名为your_table，存储JSONB数组的列名为data_col，可以通过以下两种方式实现需求：

方法一：条件聚合（无需额外扩展） #

这种方式适合已知固定键名的场景，无需依赖额外扩展，兼容性更好：

SELECT
  -- 提取meetingDate对应的值
  MAX(CASE WHEN elem->>'key' = 'meetingDate' THEN elem->>'value' END) AS meetingDate,
  -- 提取userName对应的值并去除空格（匹配示例结果格式）
  MAX(CASE WHEN elem->>'key' = 'userName' THEN REPLACE(elem->>'value', ' ', '') END) AS userName
FROM your_table,
     -- 将JSONB数组拆分为单行元素
     jsonb_array_elements(data_col) AS elem
-- 按原表主键分组，确保每条原记录对应一行结果
GROUP BY your_table.id;

说明： #

• jsonb_array_elements(data_col)：将JSONB数组展开为多行，每行对应数组中的一个对象元素。
• elem->>'key'：获取对象中key字段的文本值；elem->>'value'获取value字段的文本值。
• MAX(CASE ...)：通过条件判断将不同key的值聚合到对应的列中，MAX用于确保每个分组只保留一个有效值（因为每个key在数组中只会出现一次）。

方法二：使用crosstab（支持动态键名） #

如果需要处理动态变化的键名，可以使用PostgreSQL的crosstab函数，但需要先启用tablefunc扩展：

第一步：启用扩展 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

第二步：执行交叉表查询 #

SELECT *
FROM crosstab(
  -- 子查询生成三列数据：原记录ID、键名、键值
  'SELECT t.id, elem->>''key'', REPLACE(elem->>''value'', '' '', '''')
   FROM your_table t, jsonb_array_elements(t.data_col) elem
   ORDER BY 1, 2'
) AS ct(id INT, meetingDate TEXT, userName TEXT);

说明： #

• crosstab函数需要输入一个生成"行转列"源数据的SQL，输出指定结构的表格。
• 定义ct(id INT, meetingDate TEXT, userName TEXT)时，需要明确指定输出的列名和类型，需与子查询中的键名对应。

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