BigQuery(BQ)左关联后构造嵌套表如何避免生成含大量NULL的数组

解决方案 #

你可以直接在聚合t1结构体的ARRAY_AGG函数中添加DISTINCT关键字实现去重，修改后的代码如下：

with 
  t1 AS (select 1 AS c1, 2 AS c2 UNION ALL select 5, 6),
  t2 AS (select 3 AS c1, 4 AS c4 UNION ALL select 3, 5 UNION ALL select 3,4 UNION ALL select 5,6)
SELECT 
  t2.c1, 
  ARRAY_AGG(DISTINCT t2.c4), 
  ARRAY_AGG(DISTINCT (SELECT AS STRUCT t1.*))
FROM t2 LEFT JOIN t1 ON t1.c1=t2.c1
GROUP BY t2.c1

原理说明

当t2的c1值在t1中没有匹配记录时，每一行t2数据都会对应生成一条全字段为NULL的t1结构体，聚合时这些完全相同的NULL结构体会全部存入数组导致重复。添加DISTINCT后，聚合过程会自动对数组元素去重，完全一致的NULL结构体只会保留1个，符合你预期的输出效果。

可选扩展

如果你不需要保留NULL记录，希望无匹配时返回空数组，可以额外添加过滤条件：

ARRAY_AGG(DISTINCT (SELECT AS STRUCT t1.*) FILTER (WHERE t1.c1 IS NOT NULL))

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