PostgreSQL如何将JSONB嵌套数组拆解为多行多列的明细数据

解法 #

你可以直接通过JSONB数组的下标索引提取内层数组的对应元素，无需转换为记录。完整查询语句如下：

SELECT
  shop,
  item->>0 AS product,
  (item->>1)::numeric AS quantity,
  (item->>2)::numeric AS price
FROM Receipts,
     jsonb_array_elements(receipt) AS item;

如果你偏好更规范的显式连接写法，也可以写成LATERAL横向连接的形式，逻辑完全等价：

SELECT
  shop,
  item->>0 AS product,
  (item->>1)::numeric AS quantity,
  (item->>2)::numeric AS price
FROM Receipts
CROSS JOIN LATERAL jsonb_array_elements(receipt) AS item;

原理解释 #

1. JSONB类型的数组支持从0开始的下标直接访问：
   • -> 运算符返回JSONB类型的元素
   • ->> 运算符直接将元素转为text类型
2. 数量和单价字段需要将text类型强转为数值类型，这里用::numeric做类型转换即可
3. 你之前用到的jsonb_to_record确实仅支持JSON对象（键值对结构）输入，不适合数组结构的场景，直接按下标访问是当前场景下最简单高效的方案。

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