如何将PostgreSQL的jsonb数组的数组转换为记录集？

这个场景我之前也碰到过！当数据源是数组的数组而非键值对形式的对象数组时，jsonb_to_recordset确实派不上用场，但我们可以通过jsonb_array_elements拆分外层数组，再按索引提取子数组中的元素，最终得到和对象数组转换后一致的表格结果。

解决方案示例 #

假设你的源数据是这种数组的数组格式：

[ ["name01", 12], ["name02", 14], ["name03", 16], ["name04", 18] ]

你可以用以下SQL查询来转换：

SELECT
  arr->>0 AS name,
  (arr->>1)::INT AS age
FROM jsonb_array_elements($$[ ["name01", 12], ["name02", 14], ["name03", 16], ["name04", 18] ]$$) AS arr;

执行结果 #

原理说明 #

1. jsonb_array_elements函数会把外层的JSON数组拆分成一个个独立的子JSON数组（每个子数组作为一行的arr字段）。
2. 使用->>操作符按索引位置提取子数组中的元素：arr->>0取第一个元素（姓名），arr->>1取第二个元素（年龄）。
3. 因为->>返回的是文本类型，所以需要用::INT把年龄字段转换成整数类型，和对象数组转换后的类型保持一致。

扩展场景 #

如果你的子数组包含更多字段（比如姓名、年龄、城市），只需要按索引依次提取即可：

SELECT
  arr->>0 AS name,
  (arr->>1)::INT AS age,
  arr->>2 AS city
FROM jsonb_array_elements($$[ ["name01", 12, "Beijing"], ["name02", 14, "Shanghai"] ]$$) AS arr;

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