BigQuery生成指定结构空数组：解决ARRAY_AGG返回NULL而非空数组问题

• BigQuery 的 ARRAY_AGG 函数开启 IGNORE NULLS 配置时，如果所有输入值都为NULL被过滤、或没有符合条件的输入行，默认返回NULL而非空数组，就是你当前abc列返回结果不符合预期的原因。
• 你代码中的xyz列是直接显式声明了ARRAY<INT64>[]空数组，因此返回符合预期。

你可以任选以下两种方案之一修改代码，都可以实现返回对应STRUCT结构的空数组：

方案1：IFNULL兜底（可读性强） #

用IFNULL包裹ARRAY_AGG结果，配置对应结构的空数组作为 fallback 值即可，完整修改后的代码如下：

WITH CTE1 AS
(
  SELECT
    STRUCT
      (
        CAST(NULL AS INT64) AS id,
        CAST(NULL AS BOOL) AS number,
        CAST(NULL AS STRING) AS name
      ) AS input
)

,CTE2 AS
(
  SELECT
    IFNULL(ARRAY_AGG(input ignore nulls), ARRAY<STRUCT<id INT64, number BOOL, name STRING>>[]) AS abc,
    ARRAY<INT64>[] AS xyz
    FROM CTE1
)

SELECT * FROM CTE2

方案2：UNNEST重聚合（无需重复声明结构） #

如果不想重复写STRUCT结构定义，可以利用UNNEST NULL返回空结果集的特性，间接生成对应结构的空数组，代码更简洁，仅需替换CTE2中abc列的定义即可：

ARRAY(SELECT * FROM UNNEST(ARRAY_AGG(input ignore nulls))) AS abc

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