CODEWITHSUNDEEP
sqlgoogle-bigquery
Imad
Imad

Reputation: 2741

How to cast array elements within UNNEST statement

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?

Upvotes: 0

Views: 1382

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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)

Upvotes: 1

Related Questions