Bigquery - match items in multiple arrays by index

Question

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

What's the reason why a is in pair with apple? Is it being string prefix or being at same index in array? — Tomáš Záluský
– Tomáš Záluský, Commented Dec 1, 2022 at 13:35
By index. Now that I think about it - the way I've presented the example is confusing. I'll modify it. — Dina Kleper
– Dina Kleper, Commented Dec 1, 2022 at 13:39

Jaytiger · Accepted Answer · 2022-12-01 13:40:00Z

1

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

answered Dec 1, 2022 at 13:40

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bigquery - match items in multiple arrays by index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related