BigQuery Arrays - check if Array contains specific values

user_1502505 0
Viewed 4993

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-bigquery google-cloud-platform
2 Answers

Assuming you are looking for rows where ALL elements in match array [20, 30] are found in target array (some_numbers). Also assuming no duplicate numbers in both (match and target) arrays

select id, some_numbers
from sequences a,
unnest([struct([20, 30] as match)]) b
where (
  select count(1) = array_length(match) 
  from a.some_numbers num
  join b.match num
  using(num)
)
user_52219440

Not much to suggest... Official docs suggest to use exists:

WHERE EXISTS (SELECT *
              FROM UNNEST(some_numbers) AS s
              WHERE s in (20,30));
user_134735250
Related