如何在Snowflake的ARRAY_AGG中保留NULL值生成指定格式数组

问题背景 #

源数据表定义：

CREATE OR REPLACE TABLE tab(id INT, val TEXT) AS
SELECT * FROM VALUES  (1,'b0'), (2,'a1'), (3,'a2'), (4,'a3'), (5,'b1');

需求：使用ARRAY_AGG生成格式为[NULL, 'a1', 'a2', 'a3', NULL]的数组，需将所有不以字母a开头的条目转为NULL，且保留这些NULL元素在数组中。

失败尝试分析 #

• 尝试1：直接用CASE表达式但未指定IGNORE NULLS => FALSE，ARRAY_AGG默认忽略NULL值，导致结果缺失NULL元素。
• 尝试2：用PARSE_JSON('null')作为ELSE分支，因CASE表达式需类型一致，隐式转换后仍被默认规则忽略NULL，且类型不符合需求。
• 尝试3：将val转为VARIANT类型，得到的数组元素为NULL_VALUE类型，与目标格式不符。

解决方案 #

通过显式设置ARRAY_AGG的IGNORE NULLS => FALSE参数，结合CASE表达式转换非目标值为TEXT类型的NULL，同时指定排序保证数组顺序与id对应：

SELECT ARRAY_AGG(
    CASE WHEN val LIKE 'a%' THEN val ELSE NULL END
    IGNORE NULLS => FALSE
    ORDER BY id
) AS result_array
FROM tab;

结果验证 #

执行上述SQL后，将得到符合需求的数组：

[NULL, 'a1', 'a2', 'a3', NULL]

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