BigQuery如何无需指定全部字段通过JOIN创建嵌套记录

基础数据 #

现有两张表：Person（人员表）和Address（地址表）：

• Person表
  包含name（姓名）、address_id（地址ID）两个字段，存储数据如下：
  • Alice对应address_id为10
  • Bob对应address_id为11
  • Charlie对应address_id为10
• Address表
  包含id（地址ID）、street（街道）、city（城市）三个字段，存储数据如下：
  • id=10对应街道为William Street、城市为NYC
  • id=11对应街道为Old Street、城市为London

期望输出结果 #

需要将两表关联后生成嵌套结构的记录，输出字段为name、address.street、address.city，对应结果为：

• Alice对应William Street、NYC
• Bob对应Old Street、London
• Charlie对应William Street、NYC

由于两张表均包含大量字段，不希望逐一指定所有查询字段，期望实现类似EXCEPT语法的效果，将关联得到的地址相关字段嵌套为名为address的RECORD类型记录，参考思路写法如下：

SELECT * EXCEPT (address_id)
FROM person p
JOIN address a
ON p.address_id = a.id

请问上述需求在BigQuery中是否可以实现？

解答 #

可以实现，BigQuery原生语法就支持这类无需枚举全字段的关联嵌套查询，不需要手动列出所有字段。
基础写法是通过* EXCEPT排除不需要的关联键字段，再将地址字段组装为address结构体：

SELECT * EXCEPT(address_id, id),
  STRUCT(a.street, a.city) AS address
FROM person p
JOIN address a
ON p.address_id = a.id

如果地址表字段量极大，连street、city这类字段都不想手动指定，还可以用REPLACE配合结构体子查询的写法，直接把排除id后的整行地址数据嵌套为address字段：

SELECT * EXCEPT(address_id) REPLACE(
  (SELECT AS STRUCT a.* EXCEPT(id)) AS address
)
FROM person p
JOIN address a
ON p.address_id = a.id

两种写法的返回结果完全符合需求：顶层保留Person表除address_id外的全部字段，同时生成RECORD类型的address字段，内部嵌套街道、城市等地址属性，全程不需要逐一枚举两张表的其余业务字段。
如果两张表存在除关联键之外的重名字段，只需要把重复字段加到EXCEPT的排除列表中即可，不会触发字段重名报错。

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