CODEWITHSUNDEEP
arraysgoogle-bigquery
hselbie
hselbie

Reputation: 1879

Check to see if Multiple Elements in BigQuery ARRAY

For bigquery standard sql

I'm trying to find out the appropriate syntax for finding if an array contains 2 or more string elements.

e.g. if an array = ["5","6","7","8"]

Desired case would be something like,

case "7" OR "8" in unnest(myArray)  WHEN TRUE THEN 
    'value is in array'
    ELSE 'value is not in array' end

I can get single values to return as True but not multiple, i.e. this works fine 

case "7" in unnest(myArray)  WHEN TRUE THEN 
        'value is in array'
        ELSE 'value is not in array' end

I can also get a nested case statement working, but what i really need is OR syntax not if this is true move onto the nxt value.

I can'd find this anywhere in the docs. Any wizards out there willing to help?

Upvotes: 3

Views: 4831

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

You can use below version 

CASE (SELECT COUNT(1) FROM UNNEST(myArray) el WHERE el IN ("7", "8")) > 0 
  WHEN TRUE THEN 'value is in array'
  ELSE 'value is not in array' 
END result

Obviously you can use simple 

CASE "7" in UNNEST(myArray) or "8" in UNNEST(myArray) WHEN ...

but this will become too heavy if you have more than just two strings to lookup, while the first option does not have this "problem"

Finally you can use below "streamlined" version with UDF 

#standardSQL
CREATE TEMP FUNCTION isInArray(arr1 ANY TYPE, arr2 ANY TYPE) AS (
  (SELECT COUNT(1) FROM UNNEST(arr1) el JOIN UNNEST(arr2) el USING(el)) > 0 
SELECT *, 
  CASE isInArray(myArray, ["7", "8"])
    WHEN TRUE THEN 'value is in array'
    ELSE 'value is not in array' 
  END result  
FROM `project.dataset.table`

Upvotes: 6

Related Questions