Match array elements of the same index in SQL / BigQuery

user_17503846 0
Viewed 145

I want to output an element inside an array that match the index during a prior match in SQL/BigQuery

For example, I have these 5 fields. Match_Home_Date and Match_Away_date are in Array(String) format.

Match_ID Match_Home Match_Away Match_Home_Date Match_Away_Date
121 101, 102 103, 121 [01-02-2021, 01-05-2021] [01-07-2021, 01-09-2021]
131 131, 140 117, 115 [02-02-2021, 02-15-2021] [02-20-2021, 02-25-2021]

I want to output a "Final Date" field in which when the Match_ID matches to either Match_Home or Match_Away, it will output the corresponding Match_Home_Date or Match_Away_Date of the same index.

Output should be:

Final Date
01-09-2021
02-02-2021
google-bigquery matching indexing arrays sql
1 Answers

Consider below approach

select Match_ID, 
  (Match_Home_Date || Match_Away_Date)[offset(
  ( select offset
    from unnest(split(Match_Home) || split(Match_Away)) id with offset 
    where trim(id)  = '' || Match_ID
  ))] as Final_Date
from your_table

if applied to sample data in your question - output is

enter image description here

You can use below cte to test above

with your_table as (
  select 121 Match_ID, '101, 102' Match_Home, '103, 121' Match_Away, ['01-02-2021', '01-05-2021'] Match_Home_Date, ['01-07-2021', '01-09-2021'] Match_Away_Date union all
  select 131, '131, 140', '117, 115', ['02-02-2021', '02-15-2021'], ['02-20-2021', '02-25-2021'] 
)
user_52219440
Related