BigQuery UNNEST查询重复记录时如何保留null历史数据

问题根因 #

查询无法返回历史行NULL值有两个核心原因：

• 逗号分隔的UNNEST()写法隐式使用INNER JOIN逻辑，只要任意一个UNNEST匹配不到结果，整行就会被过滤
• 左连接生成的lastName字段过滤条件被写在了WHERE子句中，左连接生成的NULL值会被lastName.key = 'lastName'条件直接排除，本质上把左连接退化成了内连接

修正方案 #

方案1：调整JOIN逻辑，过滤条件前移 #

去掉逗号隐式内连接的写法，所有UNNEST都显式使用LEFT JOIN，并且把key的匹配条件直接写在JOIN的ON子句中，匹配不到对应key时字段自动返回NULL，不会过滤历史行：

SELECT
  name.value.string_value AS name,
  age.value.int_value AS age,
  lastName.value.string_value AS lastName
FROM myTable t
LEFT JOIN UNNEST(users) AS name ON name.key = 'name'
LEFT JOIN UNNEST(users) AS age ON age.key = 'age'
LEFT JOIN UNNEST(users) AS lastName ON lastName.key = 'lastName'

方案2：单次UNNEST+条件聚合（性能更优） #

如果单个users数组中同一个key不会重复出现，可以只做一次数组展开，通过条件聚合提取字段，避免多次JOIN带来的性能损耗，后续新增key时只需要加一行聚合逻辑即可，维护成本更低：

SELECT
  MAX(IF(u.key = 'name', u.value.string_value, NULL)) AS name,
  MAX(IF(u.key = 'age', u.value.int_value, NULL)) AS age,
  MAX(IF(u.key = 'lastName', u.value.string_value, NULL)) AS lastName
FROM myTable t
LEFT JOIN UNNEST(users) AS u
GROUP BY t.table_unique_key -- 替换为当前表的行唯一标识字段即可

注意：如果单条记录的users数组中存在重复key，方案2会返回同key下排序后的最大值，这类场景优先使用方案1。

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