SQL中如何保留数组顺序对多个数组列执行unnest操作

要在unnest两个数组列时严格保留元素原有顺序、避免位置错位，核心是显式获取元素在原数组中的位置下标，按下标对齐两个数组的展开结果，不要依赖多个unnest并列书写的隐式行为。

以最常用的PostgreSQL环境为例，使用WITH ORDINALITY语法即可稳定实现需求，该语法会在展开数组时同步返回元素对应的原数组位置序号（从1开始计数，和数组元素顺序严格对应）。
假设你的测试表名为test_arr，可直接使用如下SQL：

SELECT
  a.val AS col1,
  b.val AS col2
FROM test_arr
CROSS JOIN unnest(col1) WITH ORDINALITY AS a(val, pos)
CROSS JOIN unnest(col2) WITH ORDINALITY AS b(val, pos)
WHERE a.pos = b.pos;

针对你给出的测试数据，该语句的返回结果完全匹配预期：

注意事项 #

• 不要直接写SELECT unnest(col1), unnest(col2) FROM test_arr：这种写法依赖数据库的隐式展开逻辑，当数组长度不一致、表内有多行数组数据、查询关联逻辑复杂时，很容易出现顺序错乱、生成错误笛卡尔积的问题。
• 如果两个数组长度不一致，上述CROSS JOIN加位置相等判断的写法只会返回和较短数组等长的对齐结果；如果需要保留长数组的所有元素，可将关联改为FULL OUTER JOIN，缺失位置的元素会返回NULL。

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