如何在UNNEST语句中强制转换数组元素

Question

我在BigQuery中有一个类型为ARRAY[STRING]的REPEATED列，我想在它上面创建一个过滤器：

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

除了我不想查找"1"之外，我想将列event_list转换为类型为ARRAY[INT]的integer_event_list，然后有一个类似于WHERE 1 IN UNNEST(integer_event_list)的子句。我该如何做到这一点？

Answer 1

下面是BigQuery标准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)