BigQuery关联子查询报错，如何改写查询获取预期输出？

报错信息 #

查询错误：引用其他表的关联子查询不被支持，除非可以将其去关联，例如转换为高效的JOIN。位置[31:1]

原问题SQL #

create temporary table records (ID int64, Events array<struct<Tag string, Info string, Citations array<struct<Tag string, SourceID int64>>>>);
insert into records values(1, [('A', 'C', [('AA', 1000), ('AB', 1001)]),('A', 'C', [('AA', 1000), ('AB', 1001)]),('B', 'D', [('BA', 1000), ('BB', 1001)])]);
create temporary table sources (ID int64, Title string);
insert into sources values(1000, "ABCD"),(1001, "EFGH");

select 
  Record.ID,
  array(
    select as struct
      Event.Tag,
      Event.Info,
      array(
        select as struct
          Citation.SourceID,
          Citation.Tag,
          Source.Title
        from unnest(Event.Citations) as Citation
        left join sources as Source on Citation.SourceID = Source.ID
      ) as Citations
    from unnest(Record.Events) as Event
  ) as Events
from records as Record;

修复后的SQL #

create temporary table records (ID int64, Events array<struct<Tag string, Info string, Citations array<struct<Tag string, SourceID int64>>>>);
insert into records values(1, [('A', 'C', [('AA', 1000), ('AB', 1001)]),('A', 'C', [('AA', 1000), ('AB', 1001)]),('B', 'D', [('BA', 1000), ('BB', 1001)])]);
create temporary table sources (ID int64, Title string);
insert into sources values(1000, "ABCD"),(1001, "EFGH");

select 
  ID,
  array_agg(
    distinct as struct
      Event.Tag,
      Event.Info,
      Citations
  ) as Events
from (
  select 
    r.ID,
    e.Tag,
    e.Info,
    array_agg(
      struct(
        c.SourceID,
        c.Tag,
        s.Title
      )
    ) as Citations
  from records r
  cross join unnest(r.Events) e
  cross join unnest(e.Citations) c
  left join sources s on c.SourceID = s.ID
  group by r.ID, e.Tag, e.Info
)
group by ID;

修复说明 #

原SQL的问题在于嵌套的数组构造子查询中直接关联外部表sources，BigQuery不支持这种关联方式。修复思路是：

1. 通过cross join unnest逐层展开嵌套的Events和Citations数组
2. 展开后直接与sources表做JOIN关联，获取Title字段
3. 先按ID、Event.Tag、Event.Info分组，聚合出每个Event对应的Citations数组
4. 最后按ID分组，聚合出去重后的Events数组（匹配预期输出的去重要求）

预期输出 #

[{ 
  "ID": "1", 
  "Events": [
    { 
      "Tag": "A", 
      "Info": "C",
      "Citations": [
        { "Tag": "AA", "SourceID": "1000", "Title": "ABCD" }, 
        { "Tag": "AB", "SourceID": "1001", "Title": "EFGH" }
      ] 
    }, 
    { 
      "Tag": "B", 
      "Info": "D",
      "Citations": [
        { "Tag": "BA", "SourceID": "1000", "Title": "ABCD" }, 
        { "Tag": "BB", "SourceID": "1001", "Title": "EFGH" }
      ] 
    }
  ] 
}]

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