PostgreSQL使用crosstab实现行转列拆分JSONB地址为多列

方案1：固定地址数量场景（推荐，性能最优） #

如果提前知道最多需要展示多少个地址列（比如示例中最多2个），直接使用GROUP BY+条件聚合即可实现，代码如下：

SELECT
  name,
  age,
  MAX(CASE WHEN x->>1 = '1' THEN x->>2 END) AS "addr 1",
  MAX(CASE WHEN x->>1 = '2' THEN x->>2 END) AS "addr 2"
FROM zzzzzz, jsonb_array_elements(address::jsonb) AS x
GROUP BY name, age;

逻辑说明：

• 拆分后的x为jsonb数组，x->>1取数组第2位的地址序号，x->>2取数组第3位的地址实际值
• 用CASE语句按序号匹配对应地址值，聚合函数MAX/MIN提取非空的地址值，最后按name、age分组合并为单行

方案2：地址数量不固定动态生成列场景 #

如果地址数量不固定，需要动态适配所有存在的地址序号，可以用PostgreSQL内置的tablefunc扩展提供的crosstab行转列函数实现：

1. 首先安装扩展（仅需执行一次）

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行行转列查询

SELECT * FROM crosstab(
  -- 第一个参数：源数据查询，返回行标识、列标识、值三部分
  'SELECT name, age, x->>1 AS addr_no, x->>2 AS addr_val
   FROM zzzzzz, jsonb_array_elements(address::jsonb) AS x
   ORDER BY 1,2',
  -- 第二个参数：获取所有需要生成的列标识
  'SELECT DISTINCT x->>1 FROM zzzzzz, jsonb_array_elements(address::jsonb) AS x ORDER BY 1'
) AS ct(
  name varchar, 
  age int, 
  -- 这里需要和实际返回的地址列数量、类型对应，完全动态可以封装为PL/pgSQL函数拼接返回结构
  "addr 1" text, 
  "addr 2" text
);

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