Functions/Lists & arrays/array_contains
Lists & arrays Scalar

array_contains()

Returns true if the list contains the element.

Alias of list_contains()

Arguments & returns

array_contains(list, element)

Scalar
Overload link listelement Returns
#01 ANY[]ANY BOOLEAN

Example

array_contains example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

Catalog examples

SELECT
  list_contains([1, 2, NULL], 1);

In other engines

Apache Hivearray_contains()

Same function

Snowflake

MAP_CONTAINS_KEY('k1', {'k1': 'v1', 'k2': 'v2', 'k3': 'v3'}::MAP(VARCHAR, VARCHAR))
In DuckDB
ARRAY_CONTAINS(MAP_KEYS(CAST({'k1': 'v1', 'k2': 'v2', 'k3': 'v3'} AS MAP(TEXT, TEXT))), 'k1')

SQLGlot example ↗

Used in larger rewrites 1

These examples use array_contains as one part of a larger SQL translation.

BigQueryunnest()

SELECT * FROM a WHERE b IN UNNEST([1, 2, 3])
In DuckDB
SELECT * FROM a WHERE CASE WHEN [1, 2, 3] IS NULL OR ARRAY_LENGTH([1, 2, 3]) = 0 THEN FALSE WHEN ARRAY_CONTAINS([1, 2, 3], b) THEN TRUE WHEN b IS NULL OR ARRAY_LENGTH([1, 2, 3]) <> LIST_COUNT([1, 2, 3]) THEN NULL ELSE FALSE END

SQLGlot example ↗