BigQuery：如何将数组元素合并到结构体数组中调整数据结构？

你完全可以不用手动枚举properties里的每个字段，BigQuery支持直接展开结构体的所有字段来合并到新的结构体中，这样不管properties后续新增或修改字段，查询都能自动适配。

直接修改你的查询，把聚合逻辑改成将每个timestamp与对应的properties字段合并成单个结构体，再聚合为数组即可：

WITH events AS (
 SELECT "customer_1" AS customer_id, "timestamp_1" AS timestamp, STRUCT("iphone" AS device_model, "uk" AS country ) AS properties
 UNION ALL
 SELECT "customer_2" AS customer_id, "timestamp_2" AS timestamp, STRUCT("android" AS device_model, "us" AS country) AS properties
 UNION ALL
 SELECT "customer_2" AS customer_id, "timestamp_3" AS timestamp, STRUCT("iphone" AS device_model, "sweden" AS country) AS properties
)
SELECT 
  customer_id,
  ARRAY_AGG(STRUCT(timestamp, properties.*)) AS event
FROM events
GROUP BY customer_id

为什么这个写法更优？ #

• 无需依赖Properties的Schema：使用properties.*会自动展开结构体的所有字段，不用手动写properties.device_model、properties.country这类字段，即使后续properties新增字段，这个查询也不用修改。
• 逻辑更简洁：省去了子查询的嵌套，直接在ARRAY_AGG里完成结构体的合并，代码更易读维护。

执行这个查询后，得到的结果就是你想要的结构：每个event数组元素都包含对应的timestamp和完整的properties字段，完全匹配你的目标输出。

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