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.

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:

The function returns TRUE if value_expr is present in array.
The function returns FALSE if value_expr is not present in array.

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.

Examples¶

The following queries use the ARRAY_CONTAINS function:

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

Copy

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

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

Copy

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