Trino/Presto展开空/Null数组时保留缺失行的查询改写方案

使用cross join unnest(arr)时，空数组或Null数组的行会被过滤，因为交叉连接会丢弃没有匹配元素的行。要保留这些行，只需将交叉连接替换为左连接（LEFT JOIN），同时可通过COALESCE统一处理Null数组的情况，具体改写如下：

with table1(id, arr) as (
    values (1, array[1,2,3]),
           (2, array[]),
           (3, array[42]),
           (4, null)
)

select id, a
from table1
left join unnest(coalesce(arr, array[])) as t(a) on true;

说明： #

• coalesce(arr, array[])将Null数组转换为空数组，确保unnest能正常处理，避免因直接展开Null导致的异常；
• left join ... on true保证原表的每一行都被保留，空数组或处理后的Null数组展开后无元素，对应a列会显示为Null。

执行上述查询后，输出结果会包含所有ID的行：

id |  a
----+-----
  1 |   1
  1 |   2
  1 |   3
  2 | null
  3 |  42
  4 | null

如果需要给空数组/Null数组对应的a列设置自定义默认值（比如“无元素”），可以在select中嵌套COALESCE：

with table1(id, arr) as (
    values (1, array[1,2,3]),
           (2, array[]),
           (3, array[42]),
           (4, null)
)

select id, coalesce(a, '无元素') as a
from table1
left join unnest(coalesce(arr, array[])) as t(a) on true;

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