Categories:

Semi-structured and structured data functions (Array/Object)

ARRAY_CONTAINS¶

Returns TRUE if the specified value is found in the specified array.

Syntax¶

ARRAY_CONTAINS( <value_expr> , <array> )
Copy

Arguments¶

value_expr

Value to find in array.

array

The array to search.

Returns¶

This function returns a value of BOOLEAN type or NULL:

  • The function returns TRUE if value_expr is present in array, including the following cases:

    • When the value_expr argument is NULL and there is a SQL NULL value in the array (undefined).

    • When the value_expr argument is JSON null and there is a JSON null value in the array (null).

  • The function returns FALSE if value_expr isn’t present in array, including when the value_expr argument is JSON null and there are no JSON null values in the array.

  • The function returns NULL if the value_expr argument is NULL and there are no SQL NULL values in the array.

For more information about NULL values in arrays, see NULL values.

Usage notes¶

  • The function does not support wildcards in value_expr. However, you can use the ARRAY_TO_STRING function to convert an array to a string, then search the string with wildcard characters. For example, you can specify wildcards to search the returned string using the [ NOT ] LIKE and REGEXP_LIKE functions.

  • If array is a semi-structured array, explicit casting of the value_expr value to a VARIANT value is required for values of the following data types:

    The following example explicitly casts a string value to a VARIANT value:

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

    Explicit casting isn’t required for values of other data types.

Examples¶

The following queries use the ARRAY_CONTAINS function in a SELECT list.

In this example, the function returns TRUE because the value_expr argument is 'hello' and the array contains a VARIANT value that stores the string 'hello':

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hello', 'hi'));
Copy
+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True                                                             |
+------------------------------------------------------------------+

In this example, the function returns FALSE because the value_expr argument is 'hello' but the array doesn’t contain a VARIANT value that stores the string 'hello':

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));
Copy
+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False                                                                |
+----------------------------------------------------------------------+

In this example, the function returns NULL because the value_expr argument is NULL but the array doesn’t contain a SQL NULL value:

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', 'bonjour'));
Copy
+----------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------|
| NULL                                                     |
+----------------------------------------------------------+

In this example, the function returns TRUE because the value_expr argument is NULL and the array contains a SQL NULL value:

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', NULL));
Copy
+-----------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', NULL)) |
|-----------------------------------------------------|
| True                                                |
+-----------------------------------------------------+

In this example, the function returns TRUE because the value_expr argument is a JSON null value and the array contains a JSON null value:

SELECT ARRAY_CONTAINS(PARSE_JSON('null'), ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));
Copy
+---------------------------------------------------------------------------------+
| ARRAY_CONTAINS(PARSE_JSON('NULL'), ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|---------------------------------------------------------------------------------|
| True                                                                            |
+---------------------------------------------------------------------------------+

In this example, the function returns NULL because the value_expr argument is NULL but the array doesn’t contain a SQL NULL value (although it does contain a JSON null value):

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));
Copy
+-------------------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|-------------------------------------------------------------------|
| NULL                                                              |
+-------------------------------------------------------------------+

The following query uses the ARRAY_CONTAINS function in a WHERE clause. First, create a table with an ARRAY column and insert data:

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;
Copy
+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  1 | [            |
|    |   1,         |
|    |   2,         |
|    |   3          |
|    | ]            |
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+

Run a query that specifies the value to find for value_expr and the ARRAY column for array:

SELECT * FROM array_example WHERE ARRAY_CONTAINS(5, array_column);
Copy
+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+