sql - How to cast array elements within UNNEST statement

Question

I have a REPEATED column of type ARRAY[STRING] in BigQuery, and I want to create a filter on it that does this :

SELECT campaign,
       event_list
FROM `adobe_analytics.raw_data_20200*`
WHERE campaign IS NOT NULL
AND "1" IN UNNEST(split(event_list,","))

Except I don't want to look for "1", I want to convert the column event_list into integer_event_list of type ARRAY[INT] and then have a clause like this WHERE 1 IN UNNEST(integer_event_list). How do I achieve this?

Answer 1

Below is for BigQuery Standard SQL

SELECT campaign, event_list
FROM `adobe_analytics.raw_data_20200*`
WHERE campaign IS NOT NULL
AND 1 IN (SELECT CAST(e AS INT64) FROM UNNEST(SPLIT(event_list,",")) e)