unnest多维数组时如何获取元素所属子数组的下标

直接对二维数组调用unnest(t, r)时，PostgreSQL会将多维数组递归展开到最底层的标量元素，此时WITH ORDINALITY生成的序号是所有展开元素的全局递增值，无法保留第一层子数组的位置信息。

分两层完成数组展开：第一层先拆分最外层数组，获取每个子数组及其在外层数组中的下标（即需要的stage值）；第二层再拆分每个子数组内的元素，保证t和r的元素一一对应。该写法无需提前感知数组长度，自动适配任意子数组长度，仅要求同一行内t和r维度一致即可。

实现SQL如下：

SELECT
  p.id,
  u1.stage,
  u2.t,
  u2.r
FROM p,
-- 拆分第一维，获取每个子数组和对应的子数组下标
unnest(
  ARRAY(SELECT t[i] FROM generate_subscripts(t, 1) AS i),
  ARRAY(SELECT r[i] FROM generate_subscripts(r, 1) AS i)
) WITH ORDINALITY AS u1(t_sub, r_sub, stage),
-- 拆分每个子数组内的标量元素，保持t、r元素对应关系
unnest(u1.t_sub, u1.r_sub) AS u2(t, r);

以给出的示例数据为例，返回结果完全符合预期：

• 对id为p1的数据，第一个子数组展开的3行数据stage均为1，第二个子数组展开的3行stage均为2，第三个子数组展开的3行stage均为3
• 对id为p2的数据，每个长度为2的子数组展开2行，同一子数组对应的行stage值一致，分别为1、2、3
• 展开过程中t和r的元素始终按位置一一对应，NULL值也会正常保留

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