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