如何在PostgreSQL的SELECT子句中强制UNNEST多数组生成笛卡尔积？

当然可以！完全不用把操作移到FROM子句，就能在SELECT列表里强制触发多个数组UNNEST的笛卡尔积行为，完美适配你提到的映射层生成SQL的场景。

先说说PostgreSQL的默认逻辑：当你在SELECT里同时UNNEST多个数组时，如果数组长度一致，它会自动把对应位置的元素“拉链”配对；只有当数组长度不同时，才会返回笛卡尔积。但我们可以通过构造下标组合的方式，不管数组长度是否相同，都强制生成笛卡尔积。

具体实现方案 #

用generate_series生成足够的行数（等于两个数组长度的乘积），再通过下标计算从数组中取对应元素，完全在SELECT子句里实现：

示例1：数组长度相同时强制笛卡尔积

select
  ('{1,2}'::int[])[(generate_series(1, array_length('{1,2}'::int[],1)*array_length('{3,4}'::int[],1)) - 1)/array_length('{3,4}'::int[],1) + 1] as a_element,
  ('{3,4}'::int[])[(generate_series(1, array_length('{1,2}'::int[],1)*array_length('{3,4}'::int[],1)) - 1)%array_length('{3,4}'::int[],1) + 1] as b_element;

执行结果：

a_element | b_element
-----------+-----------
         1 |         3
         1 |         4
         2 |         3
         2 |         4

示例2：数组长度不同时的笛卡尔积（和你给出的效果一致）

select
  ('{1,2}'::int[])[(generate_series(1, array_length('{1,2}'::int[],1)*array_length('{3,4,5}'::int[],1)) - 1)/array_length('{3,4,5}'::int[],1) + 1] as a_element,
  ('{3,4,5}'::int[])[(generate_series(1, array_length('{1,2}'::int[],1)*array_length('{3,4,5}'::int[],1)) - 1)%array_length('{3,4,5}'::int[],1) + 1] as b_element;

执行结果：

a_element | b_element
-----------+-----------
         1 |         3
         1 |         4
         1 |         5
         2 |         3
         2 |         4
         2 |         5

适配表中列的场景

如果数组是来自表中的列，只需要把字面量替换成列名即可：

select
  (a_col)[(generate_series(1, array_length(a_col,1)*array_length(b_col,1)) - 1)/array_length(b_col,1) + 1] as a_element,
  (b_col)[(generate_series(1, array_length(a_col,1)*array_length(b_col,1)) - 1)%array_length(b_col,1) + 1] as b_element
from your_table;

原理说明 #

• generate_series(1, len(a)*len(b))：生成刚好能容纳所有笛卡尔积组合的行数，确保每个元素配对都有对应的行；
• 第一个下标计算(n-1)/len(b)+1：让第一个数组的每个元素重复len(b)次，覆盖第二个数组的所有元素；
• 第二个下标计算(n-1)%len(b)+1：循环遍历第二个数组的所有下标，和第一个数组的每个元素一一配对。

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