BigQuery Arrays - check if Array contains specific values
23:01 29 Apr 2021

I'm trying to see if a certain set of items exist within a BigQuery Array.

Below query works (Checking if a 1 item exists within an array):

WITH sequences AS
(
    SELECT 1 AS id, [10,20,30,40] AS some_numbers
        UNION ALL 
    SELECT 2 AS id, [20,30,40,50] AS some_numbers
        UNION ALL 
    SELECT 3 AS id, [40,50,60,70] AS some_numbers
)
SELECT id, some_numbers 
FROM sequences 
WHERE 20 IN UNNEST(some_numbers)

What I'm not able to do is below (Checking if a more than 1 item exists within an array):

(This query errors)

WITH sequences AS
(
    SELECT 1 AS id, [10,20,30,40] AS some_numbers
        UNION ALL 
    SELECT 2 AS id, [20,30,40,50] AS some_numbers
        UNION ALL 
    SELECT 3 AS id, [40,50,60,70] AS some_numbers
)
SELECT id, some_numbers 
FROM sequences 
WHERE (20,30) IN UNNEST(some_numbers)

I managed to find below workaround, but I feel there's a better way to do this:
WITH sequences AS
(
    SELECT 1 AS id, [10,20,30,40] AS some_numbers
        UNION ALL 
    SELECT 2 AS id, [20,30,40,50] AS some_numbers
        UNION ALL 
    SELECT 3 AS id, [40,50,60,70] AS some_numbers
)

SELECT id, some_numbers  
FROM  sequences
WHERE (
    
    ( 
        SELECT COUNT(1)
        FROM UNNEST(some_numbers) s
        WHERE s in (20,30)
    ) > 1
)

Any suggestions are appreciated.

google-cloud-platform google-bigquery