Snowflake中如何将ARRAY_AGG返回的空数组替换为NULL？

直接解决方案 #

在ARRAY_AGG外层嵌套NULLIF函数，将空数组[]替换为NULL，修改后的SQL如下：

NULLIF(array_agg(case when col1='abc' then object_construct('name',NAME) end), ARRAY_CONSTRUCT()) as names_list

原理说明 #

Snowflake中，当ARRAY_AGG处理的所有值都是NULL时，会默认返回空数组[]而非NULL。NULLIF函数的作用是：若两个参数相等则返回NULL，否则返回第一个参数。这里用ARRAY_CONSTRUCT()生成标准空数组作为对比值，就能精准把空数组转换为NULL。

备选方案（性能略差） #

如果需要显式判断数组长度，也可以用ARRAY_SIZE实现，但该方案需要重复调用ARRAY_AGG，不推荐用于大数据量场景：

CASE 
    WHEN ARRAY_SIZE(array_agg(case when col1='abc' then object_construct('name',NAME) end)) = 0 THEN NULL
    ELSE array_agg(case when col1='abc' then object_construct('name',NAME) end)
END as names_list

内容的提问来源于stack exchange，提问作者lil-wolf