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

Arguments¶

value_expr

Value to find in array.

• If array is a semi-structured array, value_expr must evaluate to a VARIANT.
• If array is a structured array, value_expr must evaluate to a type that is comparable to the type of the 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:

  • String & binary
  • Date & time

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

  Copy codeExpand code block

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

  Show lessSee more

  Scroll to top

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

+------------------------------------------------------------------+
| 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'));

+----------------------------------------------------------------------+
| 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'));

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

+-----------------------------------------------------+
| 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')));

+---------------------------------------------------------------------------------+
| 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')));

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

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

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