Bigquery – match items in multiple arrays by index

December 1, 2022

I have a scenario where in a row I have ids concatenated in one string column and their matching descriptions represented as concatenated string in another string column.

I’m looking for a way to select the ID and its matching Description.

(Match is by matching indexes. First item in ID arrays matches first item in description arrays etc.)
I have the first part, of splitting it to an array then unnesting it, figured out.

Not sure how to proceed from there.

Given this example:

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, Split_Letters, Split_Words 
FROM cte, 
    UNNEST(SPLIT(letters)) AS Split_Letters, 
    UNNEST(SPLIT(words)) AS Split_Words

The desired output would be:

ID1, DESC1
ID2, DESC2
ID3, DESC3
ID4, DESC4
ID5, DESC5
ID6, DESC6

>Solution :

Consider below query.

WITH cte AS (
  SELECT 1 AS id, 'a, b, c' AS letters, 'apple, banana, cucumber' AS words 
   UNION ALL 
  SELECT 2 AS id, 'd, e, f' AS letters, 'dog, elephant, frog' AS words
)
SELECT id, letter, word, letter || ', ' || word AS output
  FROM cte, 
       UNNEST(SPLIT(letters, ', ')) AS letter WITH OFFSET o1 
  JOIN UNNEST(SPLIT(words, ', ')) AS word WITH OFFSET o2
    ON o1 = o2;

*Query results