SQL/BigQuery: Output data that match STRING array elements of same index

Question

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

The objective is to match the MATCH_ID to either MATCH_HOME or MATCH_AWAY, then output the date (as MATCH_DATE) from MATCH_HOME_DATE (if it matches an element in MATCH_HOME) or MATCH_AWAY_DATE (if it matches an element in MATCH_AWAY). The matching date should have the same index with where it matched in MATCH_HOME or MATCH_DATE

For example, I have these 5 fields (and their data types).

MATCH_ID (STRING)	MATCH_HOME (STRING)	MATCH_AWAY (STRING)	MATCH_HOME_DATE (ARRAY(STRING))	MATCH_AWAY_DATE (ARRAY(STRING))
19.56V.25	19.56V.25, 19.52X.26	19.48W.27, 19.35R.28	[2021-10-30,2021-10-31]	[2021-11-05,2021-11-06]
19.24C.17	19.25S.19, 19.15M.17	19.21A.19, 19.24C.17	[2021-11-27,2021-11-28]	[2021-12-11,2021-12-12]

Output MATCH_DATE should be:

|MATCH_DATE |
|:---- |
| 2021-10-30 |
| 2021-12-12 |

Since line 1 matches with 1st element of MATCH_HOME and line 2 matches with 2nd element of MATCH_AWAY.

isn't it similar question to this stackoverflow.com/questions/70105670/… that you asked ? — Mr.Batra
– Mr.Batra, Commented Nov 29, 2021 at 7:43
I think it is EXACTLY SAME question and solution provided there works perfectly here! Can you explain please what is the difference here!! — Mikhail Berlyant
– Mikhail Berlyant, Commented Nov 29, 2021 at 17:52
Hi! The main difference is that the data I provided in the first post was too simplistic and not emphasizing the real type of data I am dealing with. i am having difficulty dealing with STRING and ARRAY(STRING) datasets so I am indicating it in this question. — Jay Lorenz Dungog
– Jay Lorenz Dungog, Commented Nov 30, 2021 at 2:19

Timogavk · Accepted Answer · 2021-11-29 06:10:50Z

-1

Need to convert strings into array. Then JOIN UNNESTed arrays by indexes.

WITH t AS (
    SELECT
    '19.56V.25' MATCH_ID,
    '19.56V.25, 19.52X.26' MATCH_HOME,
    '19.48W.27, 19.35R.28' MATCH_AWAY,
    ['2021-10-30',
    '2021-10-31'] MATCH_HOME_DATE,
    ['2021-11-05',
    '2021-11-06'] MATCH_AWAY_DATE
    UNION ALL
    SELECT
    '19.24C.17',
    '19.25S.19, 19.15M.17',
    '19.21A.19, 19.24C.17',
    ['2021-11-27',
    '2021-11-28'],
    ['2021-12-11',
    '2021-12-12']
)
SELECT MATCH_ID, arr_1 MATCH_HOME, arr_2 MATCH_AWAY, MATCH_HOME_DATE, MATCH_AWAY_DATE FROM (
    SELECT * , 
    SPLIT(MATCH_HOME, ', ') MATCH_HOME_arr, 
    SPLIT(MATCH_AWAY, ', ') MATCH_AWAY_arr 
    FROM t
)
JOIN UNNEST(MATCH_HOME_arr) arr_1 WITH OFFSET 
JOIN UNNEST(MATCH_AWAY_arr) arr_2 WITH OFFSET USING (OFFSET)
JOIN UNNEST(MATCH_HOME_DATE) MATCH_HOME_DATE WITH OFFSET USING (OFFSET)
JOIN UNNEST(MATCH_AWAY_DATE) MATCH_AWAY_DATE WITH OFFSET USING (OFFSET)

answered Nov 29, 2021 at 6:10

Timogavk

9091 gold badge9 silver badges27 bronze badges

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

1 Comment

Jay Lorenz Dungog Over a year ago

Hi Timo, it looks like it's very different from what I would like to do. i wanted to do some matching, without breaking down data sets (as much as possible).

Collectives™ on Stack Overflow

SQL/BigQuery: Output data that match STRING array elements of same index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related