检查BigQuery数组中是否有多个元素

Question

针对BigQuery标准SQL

我正在尝试找到适当的语法，以确定数组是否包含2个或更多字符串元素。

例如，如果一个数组=["5","6","7","8"]

期望的情况应该是这样的：

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

我可以让单个值返回为True，但无法让多个值返回为True，即这个没有问题：

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

我可以让嵌套的条件语句工作起来，但我真正需要的是OR语法，而不仅仅是如果为真则进入下一个值。

我找不到文档中有关此的任何说明。是否有任何高手愿意帮忙？

- hselbie

Answer 1

您可以使用以下版本。

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    

显然，您可以使用简单的

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

但是如果您需要查找超过两个字符串，第一种选择会变得太繁重，而第二种选择则没有这个“问题”。

最后，您可以使用以下具有用户定义函数的“简化”版本。

#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` 

- Mikhail Berlyant