BigQuery中如何UNNEST嵌套变量后完成两表关联查询

原SQL的核心问题 #

你写的查询跑不通主要是几个语法和逻辑错误：

• 表别名混乱：引用p2.sku但从未给table2定义过p2这个别名，字段引用时也没加对应表/UNNEST结果的别名，容易触发字段歧义报错
• 关联逻辑缺失：既没有明确两表的关联键匹配规则，还用了逗号隐式JOIN的写法，执行时会生成笛卡尔积导致结果错误
• 语法不完整：用了SUM聚合函数但没有写GROUP BY子句，不符合SQL聚合查询的语法要求
• UNNEST引用不规范：没有明确指定UNNEST的字段属于哪个主表，多表同名字段时会解析失败

UNNEST跨表关联的核心规则 #

BigQuery里只有REPEATED（数组/重复记录）类型的字段需要UNNEST展开，展开时默认和所属父表的行隐式关联：

• 用LEFT JOIN UNNEST(表名.嵌套字段) AS 别名的写法最安全，即使嵌套数组为空也会保留父表原行，不会丢数
• 两表关联时，必须先把两张表里存储关联键的嵌套字段都UNNEST成平级字段，才能做等值匹配
• 禁止用逗号+UNNEST的隐式JOIN写法，多表关联时优先级混乱极易出问题

修正后的可运行示例 #

假设两张表的关联键是sku，存储在t1的vendor嵌套字段、t2的items嵌套字段中，对应写法如下，你可以根据自己实际的关联键存储位置调整ON条件里的字段路径：

SELECT
  bb.brand_name,
  wi.warehouse_name,
  SUM(CASE WHEN vi.is_active = TRUE THEN 1 ELSE 0 END) AS Total_Active_Listed
FROM
  `table1` AS t1
-- 依次展开t1下的三个重复嵌套字段
LEFT JOIN UNNEST(t1.brand) AS bb
LEFT JOIN UNNEST(t1.warehouse_info) AS wi
LEFT JOIN UNNEST(t1.vendor) AS vi
-- 关联t2，先展开t2的嵌套items字段，再匹配关联键
LEFT JOIN `table2` AS t2
LEFT JOIN UNNEST(t2.items) AS it
  -- 替换为你实际的关联键匹配逻辑，比如如果sku存在brand字段下就改成bb.sku = it.sku
  ON vi.sku = it.sku
-- 聚合时必须把SELECT中非聚合的维度字段全部列入GROUP BY
GROUP BY bb.brand_name, wi.warehouse_name

调试注意点 #

• 如果嵌套字段是单条STRUCT结构（不是数组/REPEATED类型），不需要UNNEST，直接用别名.字段名.子字段名的路径访问即可
• 如果展开后数据量比预期大很多，先检查是不是漏写了关联条件，或者多个嵌套数组UNNEST产生了数组内笛卡尔积，可以先加LIMIT 100看展开后的明细行再调整逻辑
• 所有字段引用尽量加前缀别名，避免同名字段解析错误

内容的提问来源于stack exchange，提问作者G.J