BigQuery按索引匹配多数组元素：关联ID与对应描述

你的当前写法会对两个拆分后的数组执行笛卡尔积，导致每个ID对应所有描述，无法实现按索引匹配的需求。正确的做法是利用WITH OFFSET获取元素的索引，再通过索引关联两个数组的对应元素，同时处理拆分后元素的空格问题。

修正后的查询代码 #

WITH cte AS (
  SELECT 1 AS id, 'ID1, ID2, ID3' AS letters, 'DESC1, DESC2, DESC3' AS words 
  UNION ALL 
  SELECT 2 AS id, 'ID4, ID5, ID6' AS letters, 'DESC4, DESC5, DESC6' AS words
)
SELECT 
  id,
  TRIM(split_letter) AS split_letter,
  TRIM(split_word) AS split_word
FROM cte,
  UNNEST(SPLIT(letters)) AS split_letter WITH OFFSET AS letter_idx,
  UNNEST(SPLIT(words)) AS split_word WITH OFFSET AS word_idx
WHERE letter_idx = word_idx

关键说明 #

• WITH OFFSET：为拆分后的数组每个元素分配从0开始的索引，确保能按原顺序匹配对应元素
• WHERE letter_idx = word_idx：通过索引相等关联ID和对应的描述，避免笛卡尔积
• TRIM()：去除拆分后元素前后的空格（原字符串中逗号后带有空格，拆分后元素会残留空格）

输出结果 #

id  split_letter  split_word
1   ID1           DESC1
1   ID2           DESC2
1   ID3           DESC3
2   ID4           DESC4
2   ID5           DESC5
2   ID6           DESC6

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