如何通过INSERT...SELECT语句将两张独立表数据插入BigQuery嵌套表

核心思路是通过分组聚合将每个用户的多个地址转换为结构体数组，再与用户基础信息关联后插入目标表。以下是主流SQL方言的实现示例：

BigQuery 实现 #

INSERT INTO MEMBERS (userid, firstname, lastname, addresses)
SELECT
  u.userid,
  u.firstname,
  u.lastname,
  ARRAY_AGG(STRUCT(a.street, a.city, a.state)) AS addresses
FROM USERS u
LEFT JOIN ADDRESSES a ON u.userid = a.userid
GROUP BY u.userid, u.firstname, u.lastname;

• 使用LEFT JOIN保证无地址的用户也能被插入，此时addresses会是一个空数组
• STRUCT()构造单条地址的结构体，ARRAY_AGG()将同一用户的所有地址结构体聚合成数组

PostgreSQL 实现 #

首先需要定义地址结构体类型（需与MEMBERS表addresses字段类型匹配）：

CREATE TYPE address_struct AS (street TEXT, city TEXT, state TEXT);

然后执行插入：

INSERT INTO MEMBERS (userid, firstname, lastname, addresses)
SELECT
  u.userid,
  u.firstname,
  u.lastname,
  array_agg(ROW(a.street, a.city, a.state)::address_struct) AS addresses
FROM USERS u
LEFT JOIN ADDRESSES a ON u.userid = a.userid
GROUP BY u.userid, u.firstname, u.lastname;

• ROW()::address_struct将地址字段转换为预定义的结构体类型
• array_agg()聚合生成结构体数组

Hive 实现 #

INSERT INTO TABLE MEMBERS
SELECT
  u.userid,
  u.firstname,
  u.lastname,
  collect_list(named_struct('street', a.street, 'city', a.city, 'state', a.state)) AS addresses
FROM USERS u
LEFT JOIN ADDRESSES a ON u.userid = a.userid
GROUP BY u.userid, u.firstname, u.lastname;

• named_struct()指定结构体的字段名与对应值
• collect_list()聚合生成结构体数组（若需去重可用collect_set()）

额外注意事项 #

• 若需排除空地址（比如用户无地址时不生成空元素），可在聚合函数中添加过滤：例如BigQuery用ARRAY_AGG(STRUCT(...) IGNORE NULLS)，或在JOIN后添加WHERE a.userid IS NOT NULL
• 确保MEMBERS表的addresses字段类型与聚合结果完全匹配，避免类型错误

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