Match array elements of the same index in SQL / BigQuery

Question

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

Mikhail Berlyant · Accepted Answer · 2021-11-25 04:30:41Z

3

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

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'] 
)

answered Nov 25, 2021 at 4:30

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

6 Comments

Jay Lorenz Dungog Over a year ago

Thanks for the response, I tried it with your sample data and it works. But I tried it with my data, it didn't because maybe all my columns are STRING (my original data set are STRING, I just simplified for the question here). Match_ID, Match_Home, Match_Away are STRING, Match_Home_Date and Match_Away_Date are ARRAY (STRING)

Mikhail Berlyant Over a year ago

If all your columns are string - Just simply add SPLIT in second line - like split(Match_Home_Date) || split(Match_Away_Date) - please try now and let me know!

Jay Lorenz Dungog Over a year ago

Hi Mikhail, it still doesn't work bec the data in second line (Match_Home_Date and Match_Away_Date) are ARRAY<STRING> data. I think SPLIT only works for STRING right?

Mikhail Berlyant Over a year ago

that's right! split works with string! have you tried with sample data in my answer? that sample data is based on how you described your data in your question! so obviously - if your real data is different - you should do respective adjustments!

Jay Lorenz Dungog Over a year ago

|

Collectives™ on Stack Overflow

Match array elements of the same index in SQL / BigQuery

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related