Google Big Query中多UNNEST操作后如何去除重复结果？

你当前的查询会产生笛卡尔积结果，原因是同时对两个独立的JSON数组执行UNNEST操作时，没有关联它们的位置关系，相当于把a_col的每个元素和b_col的每个元素两两组合，最终得到9条重复数据，而非你需要的一一对应结果。

以下是修正后的SQL代码，能实现数组元素按顺序一一匹配：

WITH
  make_table AS (
  SELECT
    1 AS id,
    '[{"key": "value1"}, {"key": "value2"}, {"key": "value3"}]' AS a_col,
    '[1,2,3]' AS b_col 
)
SELECT
  mt.id,
  JSON_EXTRACT_SCALAR(a_col_individual, '$.key') AS `key`,
  JSON_EXTRACT_SCALAR(b_col_individual, '$') AS `value`
FROM
  make_table AS mt
JOIN UNNEST(JSON_EXTRACT_ARRAY(mt.a_col)) AS a_col_individual WITH OFFSET a_offset
ON TRUE
JOIN UNNEST(JSON_EXTRACT_ARRAY(mt.b_col)) AS b_col_individual WITH OFFSET b_offset
ON a_offset = b_offset

代码说明 #

• 使用WITH OFFSET获取每个数组元素的索引位置，这个索引对应元素在原数组中的顺序；
• 通过a_offset = b_offset的关联条件，确保两个数组中同位置的元素一一匹配，从而得到无重复的对应结果。

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