Skip to main content

array_contains

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

Syntax​

array_contains( <array> , <value_expr> )

Arguments​

<array>​

The array to search.

<value_expr>​

Value to find in <array>. The value is of variant type.

Returns​

This function returns a value of boolean type or NULL:

  • The function returns TRUE if <value_expr> is present in <array>.
  • The function returns FALSE if <value_expr> is not present in <array>.
  • The function returns NULL if either <value_expr> or <array> is NULL.
  • The function returns NULL if <array> is not an ARRAY.

Examples​

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

SELECT
    array_contains(ARRAY_CONSTRUCT('hello', 'hi'), 'hello'::variant) AS r1,
    array_contains(ARRAY_CONSTRUCT('hola', 'bonjour'), 'hello'::variant) AS r2,
    array_contains(ARRAY_CONSTRUCT('hola', 'bonjour'), NULL) AS r3,
    array_contains(ARRAY_CONSTRUCT('hola', NULL), NULL) AS r4,
    array_contains(NULL, 'hello'::variant) AS r5,
    array_contains({"key": "value"}, 'hello'::variant) AS r6;
+------+-------+------+------+------+------+
| r1   | r2    | r3   | r4   | r5   | r6   |
+------+-------+------+------+------+------+
| true | false | NULL | NULL | NULL | NULL |
+------+-------+------+------+------+------+
On this page