BigQuery数组关联查询报错求助：关联子查询无法解关联

问题场景 #

现有BigQuery表结构如下：

Table1 (
  Id  int,
  Details Array<STRUCT<id int>>
)

Names(
  Id int,
  Name string
)

table2(
  data_id int
)

需要查询table1，为details数组内的每个元素关联names表获取对应的name字段，最终输出包含原Id和带name的details数组的结果。原查询语句触发报错：

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

原查询语句：

select a.*,(SELECT a.details,n.name FROM a.details d LEFT JOIN names n ON d.id = n.id ) AS name_details 
FROM table1 AS a 
LEFT JOIN (SELECT * FROM table2) as b ON a.id = b.data_id

解决方案 #

BigQuery不支持在关联子查询中直接引用外部表（如这里的names），需通过UNNEST展开数组 + JOIN关联 + ARRAY_AGG重新聚合的方式实现需求：

SELECT
  a.Id,
  ARRAY_AGG(STRUCT(d.id, n.name)) AS Details,
  b.* -- 若需保留table2的字段，可添加，注意处理NULL情况
FROM table1 a
LEFT JOIN table2 b ON a.Id = b.data_id
-- 展开details数组为行数据
LEFT JOIN UNNEST(a.Details) d
-- 关联names表获取对应name
LEFT JOIN names n ON d.id = n.id
-- 按原table1的Id和table2的字段分组，重新聚合成数组
GROUP BY a.Id, b.data_id -- 若table2有其他字段，需全部加入GROUP BY

关键步骤说明 #

1. UNNEST(a.Details)：将table1的details数组拆分成独立行，每行对应一个数组元素
2. LEFT JOIN names：为每个展开后的数组元素匹配对应的name字段
3. ARRAY_AGG(STRUCT(d.id, n.name))：将关联后的行数据重新聚合成数组，结构包含id和name
4. GROUP BY：必须按table1的Id以及table2的所有字段分组，确保每个原table1的行对应一个聚合后的数组

若无需保留table2的关联结果，可简化为：

SELECT
  a.Id,
  ARRAY_AGG(STRUCT(d.id, n.name)) AS Details
FROM table1 a
LEFT JOIN UNNEST(a.Details) d
LEFT JOIN names n ON d.id = n.id
GROUP BY a.Id

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