在BigQuery中同时展开两个数组并按对应位置关联元素的实现方法

你遇到的这个需求其实是BigQuery里很常见的数组展开并关联对应元素的场景，我来给你一步步拆解实现方法：

首先注意到你的some_sources字段是字符串包裹的JSON数组（比如"["a","b"]"），而不是BigQuery原生的数组类型，所以第一步得先把它转换成可操作的数组，然后再和time_per_source数组按位置一一对应展开。

这里的核心技巧是利用UNNEST(... WITH OFFSET)语法——它在展开数组的同时，会给每个元素带上它在原数组中的位置索引（从0开始），我们就可以通过这个索引来匹配两个数组的对应元素。

完整实现SQL #

假设你的原表名为your_table，可以用下面的查询来得到你想要的结果：

SELECT
  original.id1,
  original.id2,
  original.some_sources,
  original.time_per_source,
  source,
  time
FROM
  your_table AS original,
  UNNEST(JSON_EXTRACT_ARRAY(original.some_sources)) AS source WITH OFFSET AS source_offset,
  UNNEST(original.time_per_source) AS time WITH OFFSET AS time_offset
WHERE
  source_offset = time_offset

关键部分解释 #

1. 转换字符串数组为原生数组：JSON_EXTRACT_ARRAY(original.some_sources)会把"["a","b"]"这种字符串格式的JSON数组，转换成BigQuery能识别的原生数组["a", "b"]。如果担心部分数据格式错误导致查询失败，可以换成SAFE.JSON_EXTRACT_ARRAY，它会在格式错误时返回NULL而不是报错。
2. 带偏移量展开数组：UNNEST(...) WITH OFFSET会为每个展开的元素生成一个位置索引，比如数组["a","b"]展开后，a对应索引0，b对应索引1；time_per_source数组[0,31345]展开后，0对应索引0，31345对应索引1。
3. 匹配对应位置元素：通过WHERE source_offset = time_offset，确保每个source和对应的time是原数组中同一位置的元素，完美解决元素对应问题。

如果不需要保留原表中的数组字段（some_sources和time_per_source），直接在SELECT里去掉这两项即可。

备注：内容来源于stack exchange，提问作者Asmita Poddar