使用ARRAY_AGG的IGNORE NULLS仍含NULL值，是否因嵌套STRUCT？

问题核心原因 #

ARRAY_AGG的IGNORE NULLS参数作用是忽略整个聚合对象为NULL的行，而非过滤STRUCT内部的NULL字段。你的代码中，待聚合的STRUCT本身并非NULL（即使其中event_date字段为NULL），因此ARRAY_AGG不会跳过这些STRUCT，最终数组里就会包含带有NULL字段的STRUCT实例。

以你示例数据的第5行为例，生成的STRUCT是STRUCT(4,9,NULL)，这个STRUCT整体非空，所以会被保留在defectInfo数组中，这就是你看到NULL的直接原因。

解决方案 #

要将STRUCT内部的NULL替换为空值，需要在生成STRUCT时对可能为NULL的字段单独处理，而非依赖ARRAY_AGG的IGNORE NULLS。可以用IFNULL（或COALESCE）函数将NULL替换为目标空值（比如空字符串）。

修改后的完整代码如下：

WITH t AS (
  SELECT 1 as batch_id, 1 as sensor_id, 1 as product_part_id, 2 as defect_id,  5 as count_defects, '2018-7-1' as event_date
  UNION ALL SELECT 1 as batch_id, 2 as sensor_id, 1 as product_part_id, 2 as defect_id,  6 as count_defects, '2018-7-1' as event_date
  UNION ALL SELECT 1 as batch_id, 2 as sensor_id, 2 as product_part_id, 3 as defect_id,  7 as count_defects, '2018-7-1' as event_date
  UNION ALL SELECT 1 as batch_id, 3 as sensor_id, 2 as product_part_id, 3 as defect_id,  8 as count_defects, '2018-7-1' as event_date
  UNION ALL SELECT 1 as batch_id, 3 as sensor_id, 2 as product_part_id, 4 as defect_id,  9 as count_defects, null as event_date
  UNION ALL SELECT 1 as batch_id, 3 as sensor_id, 3 as product_part_id, 5 as defect_id, 10 as count_defects, '2018-7-1' as event_date
),
defect_nesting as (
  SELECT 
    batch_id, 
    sensor_id, 
    product_part_id, 
    -- 对event_date字段单独处理，将NULL替换为空字符串
    array_agg(STRUCT(defect_id, count_defects, IFNULL(event_date, '')) IGNORE NULLS ORDER BY defect_id) defectInfo
  FROM t 
  GROUP BY 1, 2, 3
  ),

product_nesting as (  
  SELECT 
    batch_id,
    sensor_id,
    array_agg(STRUCT(product_part_id, defectInfo) IGNORE NULLS ORDER BY product_part_id) productInfo
  FROM defect_nesting
  GROUP BY 1,2
)

SELECT 
  batch_id,
  array_agg(STRUCT(sensor_id, productInfo) IGNORE NULLS ORDER BY sensor_id) sensorInfo
FROM product_nesting
GROUP BY 1

补充说明 #

• 若STRUCT中有多个可能为NULL的字段，需要对每个字段单独使用IFNULL或COALESCE处理。
• IGNORE NULLS仅在聚合对象本身为NULL时生效，比如某行的STRUCT(...)完全为NULL（所有字段都是NULL），才会被ARRAY_AGG忽略。

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