BigQuery 数组 - 检查数组是否包含特定值

Question

我正在尝试查看某个BigQuery数组中是否存在一组特定的项。

以下查询有效（检查一个项是否存在于数组中）：

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)

我无法完成以下操作（检查数组中是否存在多个项）：

（此查询发生错误）

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)

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
)

欢迎提出任何建议。

- user1502505

---

你是想要数组中的所有值都符合条件还是只需要有一个符合条件？ - Mikhail Berlyant

Answer 1

没有太多建议... 官方文档 建议使用exists:

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

- Sergey Geron

---

谢谢。但是我需要看看some_numbers数组中是否存在20和30这两个元素。 - user1502505

Answer 2

假设您正在寻找匹配数组[20，30]中的所有元素在目标数组（some_numbers）中都能找到的行。同时假设两个数组（匹配和目标）中均没有重复数字。

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

- Mikhail Berlyant