CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
user1502505
user1502505

Reputation: 884

BigQuery Arrays - check if Array contains specific values

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.

Upvotes: 4

Views: 19811

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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)
    )

Upvotes: 2

Sergey Geron
Sergey Geron

Reputation: 10152

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

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

Upvotes: 7

Related Questions