BigQuery Arrays - check if Array contains specific values

Question

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.

are you looking for both values to be within array or any?

Mikhail Berlyant
– Mikhail Berlyant

2021-04-30 06:24:04 +00:00
Commented Apr 30, 2021 at 6:24 — Mikhail Berlyant
– Mikhail Berlyant, Commented Apr 30, 2021 at 6:24

Sergey Geron · Accepted Answer · 2021-04-30 05:34:30Z

7

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

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

answered Apr 30, 2021 at 5:34

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1502505 Over a year ago

Thanks. But what I needed was to see if BOTH elements 20,30 exist within some_numbers array

AnhellO · Accepted Answer · 2024-05-26 19:06:50Z

2

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

edited May 26, 2024 at 19:06

AnhellO

1,0931 gold badge17 silver badges17 bronze badges

answered Apr 30, 2021 at 6:43

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Collectives™ on Stack Overflow

BigQuery Arrays - check if Array contains specific values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related