BigQuery中如何将关联表转为STRUCT数组且无需逐个指定列？

在BigQuery中，你可以直接聚合关联表的整行数据，无需逐个指定列，就能得到符合要求的重复记录结构，具体修改如下：

修改后的SQL代码 #

WITH A1 AS 
(
  SELECT 1 AS A, 2 AS B
  UNION ALL
  SELECT 2 AS A, 4 AS B
  UNION ALL
  SELECT 3 AS A, 6 AS B
  UNION ALL
  SELECT 4 AS A, 8 AS B  
),
A2 AS 
(
  SELECT 10 AS C, 2 AS B
  UNION ALL
  SELECT 15 AS C, 2 AS B
  UNION ALL
  SELECT 20 AS C, 4 AS B
  UNION ALL
  SELECT 25 AS C, 4 AS B
  UNION ALL
  SELECT 30 AS C, 6 AS B
  UNION ALL
  SELECT 40 AS C, 8 AS B
)

SELECT
  A1.A,
  A1.B,
  ARRAY_AGG(A2 IGNORE NULLS) AS A2
FROM A1
LEFT JOIN A2
ON A1.B = A2.B
GROUP BY A1.A, A1.B

说明 #

1. 直接聚合整行：使用ARRAY_AGG(A2)替代原来的ARRAY_AGG(STRUCT(A2.B, A2.C))，数组中的每个元素就是A2的完整行，包含B、C列，展开后直接对应A2.B和A2.C，不会产生嵌套层级。
2. 处理NULL场景：添加IGNORE NULLS参数可以过滤掉LEFT JOIN中无匹配的NULL行，避免数组中出现无效的NULL元素（如果不需要保留空数组，可以去掉该参数）。

为什么之前的尝试失败 #

• ARRAY_AGG(STRUCT(A2.*))报错：BigQuery的STRUCT()函数不支持将表名.*作为单个参数传入，必须明确列出具体列名。
• ARRAY_AGG(STRUCT(A2))产生嵌套：这种写法是把整个A2行作为STRUCT的一个子字段，因此会出现A2.A2.B、A2.A2.C的嵌套结构，不符合需求。

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