如何使用SQL判断BigQuery数组类型列中是否存在指定值

你可以通过两种方式实现该需求，返回结果和你预期的输出完全一致：

方案1：通用兼容写法（EXISTS + UNNEST） #

WITH sequences AS
  (SELECT [0, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT 
  some_numbers,
  EXISTS(
    SELECT 1 FROM UNNEST(some_numbers) AS num 
    WHERE num IN (3, 10)
  ) AS `exists`
FROM sequences

该写法逻辑通用，EXISTS子查询只要匹配到第一个符合条件的元素就会立刻返回True，无需遍历全量数组，性能表现更好。注意exists是SQL保留关键字，作为列名使用时需要用反引号包裹转义。

方案2：BigQuery原生数组运算符写法（更简洁） #

WITH sequences AS
  (SELECT [0, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT 
  some_numbers,
  some_numbers && [3, 10] AS `exists`
FROM sequences

&&是BigQuery提供的数组重叠运算符，用于直接判断两个数组是否存在公共元素，只要有一个公共元素就返回True，写法更简洁易读。

内容的提问来源于stack exchange，提问作者loadbox