sql 扫描数组

扫描数组
如需检查数组是否包含特定值，请结合使用 IN 运算符和 UNNEST。如需检查数组是否包含与某条件匹配的值，请结合使用 EXISTS 函数和 UNNEST。

扫描特定值
如需扫描数组中的特定值，请将 IN 运算符与 UNNEST 结合使用。

示例

以下示例在数组中包含数字 2 时返回 true。

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

+----------------+
| contains_value |
+----------------+
| true           |
+----------------+

对于一个表，要返回数组列中包含特定值的行，请使用 WHERE 子句过滤 IN UNNEST 的结果。

示例

以下示例返回数组列中包含值 2 的行的 id 值。

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE 2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;

+---------------+
| matching_rows |
+---------------+
| 1             |
| 2             |
+---------------+

扫描满足条件的值
如需扫描数组以查找与条件相匹配的值，请使用 UNNEST 返回包含数组中元素的表，使用 WHERE 过滤子查询中的结果表，然后使用 EXISTS 检查过滤后的表是否包含任何行。

示例

以下示例返回包含大于 5 的值的数组列中行的 id 值。

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows FROM sequences
WHERE EXISTS (SELECT *
              FROM UNNEST(some_numbers) AS x
              WHERE x > 5);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+