CONTAINS()

All functions > ARRAY > CONTAINS()

Returns TRUE if the array contains the specified element.

Signatures

Returns: TRUE if the element exists in the array, FALSE otherwise

CONTAINS(array: ARRAY<T>, element: T) → BOOLEAN
sql
ParameterTypeRequiredDescription
arrayARRAY<T>YesThe array to search in
elementTYesThe element to check for existence

Notes

  • Returns boolean value indicating element presence
  • Uses exact match comparison (case-sensitive for strings)
  • Returns NULL if checking for NULL element
  • Empty arrays always return FALSE
  • More efficient than ARRAY_POSITION for simple existence checks
  • Supports ARRAY<ROW> and ARRAY<ARRAY<T>>: element and array must share the exact same type; mismatched schemas raise a user error

Aliases

  • ARRAY_CONTAINS

See also

Examples

FeatureQL
SELECT
    -- Element exists
    f1 := CONTAINS(ARRAY(1, 2, 3, 4), 3),
    -- String element exists
    f2 := CONTAINS(ARRAY('A', 'B', 'C'), 'B'),
    -- Element does not exist
    f3 := CONTAINS(ARRAY(1, 2, 3), 5),
    -- String not found
    f4 := CONTAINS(ARRAY('apple', 'banana'), 'cherry'),
    -- Duplicate element found
    f5 := CONTAINS(ARRAY(1, 2, 2, 3), 2),
    -- NULL element is NULL
    f6 := CONTAINS(ARRAY(1, NULL::BIGINT, 3), NULL::BIGINT),
    -- Empty array
    f7 := CONTAINS(ARRAY()::BIGINT[], 1),
    -- Case-sensitive comparison
    f8 := CONTAINS(ARRAY('Hello', 'World'), 'hello')
;
Result
f1 BOOLEANf2 BOOLEANf3 BOOLEANf4 BOOLEANf5 BOOLEANf6 BOOLEANf7 BOOLEANf8 BOOLEAN
truetruefalsefalsetruenullfalsefalse

Suggest changes to this page
Last update at: 2026/06/20 10:08:10