如何在BigQuery数组中处理null值？有无可行方案或特性请求？

问题场景 #

我经常需要将列转换为数组，示例数据如下：

`cost`
4.50
null
1.00

尝试用以下语句生成数组：

SELECT ARRAY_AGG(val) FROM tbl

预期结果为 [4.50, null, 1.00]，但BigQuery不允许数组直接包含null值，执行会报错。由于需要保留null值来和其他列做zip等匹配操作，不能使用IGNORE NULLS参数。

PostgreSQL中可直接生成包含null的数组，示例如下：

postgres=# SELECT ARRAY_AGG(cost) FROM (VALUES (4.50),(null),(1.00)) tbl(cost);
    array_agg     
------------------
 {4.50,NULL,1.00}

可行解决方案 #

方法1：用结构体包装Null值 #

将每个值包装为单字段结构体，结构体本身是非null的，因此可以被正常聚合到数组中：

SELECT ARRAY_AGG(STRUCT(val)) AS val_array FROM tbl

返回结果示例：[{val: 4.50}, {val: NULL}, {val: 1.00}]

后续需要使用数组元素时，可通过UNNEST提取结构体字段，比如和其他列zip时：

SELECT 
  ARRAY(
    SELECT STRUCT(a.val, b.other_col)
    FROM UNNEST(val_array) a
    JOIN UNNEST(other_array) b
    ON OFFSET(a) = OFFSET(b)
  ) AS zipped_array
FROM your_table

方法2：用特殊标记替换后还原 #

如果需要更接近原生数值数组的形式，可先用业务中不存在的特殊值替换null，聚合后再还原：

SELECT 
  ARRAY(
    SELECT IF(x = -999999, NULL, x)
    FROM UNNEST(ARRAY_AGG(IF(val IS NULL, -999999, val))) x
  ) AS val_array
FROM tbl

注意：必须确保特殊值（如示例中的-999999）不会出现在真实业务数据中，避免错误替换。

特性请求相关 #

目前BigQuery尚未支持数组直接包含null值的特性，你可以在Google Cloud官方Issue Tracker提交特性请求，详细描述你的使用场景和需求，助力官方评估开发优先级。

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