类别：

半结构化和结构化数据函数 （数组/对象）

ARRAY_CONTAINS

如果在指定的数组中找到指定值，则返回 TRUE。

语法

ARRAY_CONTAINS( <value_expr> , <array> )

实参

value_expr

要在 array 中查找的值。

array

要搜索的数组。

返回

此函数返回 BOOLEAN 类型的值或 NULL：

  • 函数返回 TRUE，前提是 value_expr 存在于 array 中，包括以下情况：

    • value_expr 实参为 NULL，并且数组 (undefined) 中有 SQL NULL 值。

    • value_expr 实参为 JSON null，并且数组 (null) 中有 JSON null 值。

  • 函数返回 FALSE，前提是 value_expr 不存在于 array 中，包括以下情况：value_expr 实参是 JSON null，并且数组中没有 JSON null 值。

  • 如果 value_expr 实参为 NULL 且数组中没有 SQL NULL 值，则函数返回 NULL。

有关数组中 NULL 值的更多信息，请参阅 NULL 值。

使用说明

  • 该函数不支持在 value_expr 中使用通配符。但您可以使用 ARRAY_TO_STRING 函数将数组转换为字符串，然后使用通配符搜索字符串。例如，您可以使用通配符，利用 [ NOT ] LIKE 和 REGEXP_LIKE 函数搜索返回的字符串。

  • 如果 array 是半结构化数组，则需要将以下数据类型的 value_expr 值 显式转换 为 VARIANT 值：

    以下示例将字符串值显式转换为 VARIANT 值：

    SELECT ARRAY_CONTAINS('mystring2'::VARIANT, ARRAY_CONSTRUCT('mystring1', 'mystring2'));

    其他数据类型的值不需要显式转换。

示例

以下查询在 SELECT 列表中使用 ARRAY_CONTAINS 函数。

在此示例中，函数返回 TRUE，因为 value_expr 实参是 'hello'，并且数组中包含存储字符串 'hello' 的 VARIANT 值：

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hello', 'hi'));

+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True                                                             |
+------------------------------------------------------------------+

在此示例中，函数返回 FALSE，因为 value_expr 实参是 'hello'，但数组中不包含存储字符串 'hello' 的 VARIANT 值：

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));

+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False                                                                |
+----------------------------------------------------------------------+

在此示例中，函数返回 NULL，因为 value_expr 实参是 NULL，但数组中不包含 SQL NULL 值：

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', 'bonjour'));

+----------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------|
| NULL                                                     |
+----------------------------------------------------------+

在此示例中，函数返回 TRUE，因为 value_expr 实参是 NULL，并且数组中包含 SQL NULL 值：

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', NULL));

+-----------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', NULL)) |
|-----------------------------------------------------|
| True                                                |
+-----------------------------------------------------+

在此示例中，函数返回 TRUE，因为 value_expr 实参是 JSON null 值，并且数组中包含 JSON null 值：

SELECT ARRAY_CONTAINS(PARSE_JSON('null'), ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));

+---------------------------------------------------------------------------------+
| ARRAY_CONTAINS(PARSE_JSON('NULL'), ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|---------------------------------------------------------------------------------|
| True                                                                            |
+---------------------------------------------------------------------------------+

在此示例中，函数返回 NULL，因为 value_expr 实参是 NULL，但数组中不包含 SQL NULL 值（尽管它包含 JSON null 值）：

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));

+-------------------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|-------------------------------------------------------------------|
| NULL                                                              |
+-------------------------------------------------------------------+

以下查询在 WHERE 子句中使用 ARRAY_CONTAINS 函数。首先，创建一个包含 ARRAY 列的表并插入数据：

CREATE OR REPLACE TABLE array_example (id INT, array_column ARRAY);

INSERT INTO array_example (id, array_column)
  SELECT 1, ARRAY_CONSTRUCT(1, 2, 3);

INSERT INTO array_example (id, array_column)
  SELECT 2, ARRAY_CONSTRUCT(4, 5, 6);

SELECT * FROM array_example;

+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  1 | [            |
|    |   1,         |
|    |   2,         |
|    |   3          |
|    | ]            |
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+

运行一个查询，指定要为 value_expr 查找的值，以及要为 array 查找的 ARRAY 列：

SELECT * FROM array_example WHERE ARRAY_CONTAINS(5, array_column);

+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+