ARRAY_REMOVE()

All functions > ARRAY > ARRAY_REMOVE()

Returns an array with all occurrences of the specified element removed.

Signatures

Returns: A new array with all occurrences of the specified element removed

ARRAY_REMOVE(array: ARRAY<T>, element: T) → ARRAY<T>
sql
ParameterTypeRequiredDescription
arrayARRAY<T>YesThe input array from which to remove elements
elementTYesThe element to remove from the array

Notes

  • Removes ALL occurrences of the specified element
  • Result element order is normalized (often sorted) so behavior is stable across backends
  • Returns empty array if all elements are removed
  • Can be used to remove NULL values from arrays
  • Supports ARRAY<ROW> and ARRAY<ARRAY<T>>: element equality uses IS DISTINCT FROM (exact structural match)

See also

Examples

FeatureQL
SELECT
    f1 := ARRAY_REMOVE(ARRAY[1, 2, 2, 3, 2], 2), -- Remove all occurrences of 2
    f2 := ARRAY_REMOVE(ARRAY['A', 'B', 'A', 'C'], 'A'), -- Remove all occurrences of 'A'
    f3 := ARRAY_REMOVE(ARRAY[1, 2, 3], 4), -- Element not in array
    f4 := ARRAY_REMOVE(ARRAY[5, 5, 5], 5), -- Remove all elements
    f5 := ARRAY_REMOVE(ARRAY['apple', 'banana'], 'apple'), -- Remove specific string
    f6 := ARRAY_REMOVE(ARRAY[]::BIGINT[], 1), -- Empty array
    f7 := ARRAY_REMOVE(ARRAY[1, NULL::BIGINT, 2, NULL::BIGINT], NULL::BIGINT) -- Remove NULL values
;
Result
f1 ARRAYf2 ARRAYf3 ARRAYf4 ARRAYf5 ARRAYf6 ARRAYf7 ARRAY
[1, 3][B, C][1, 2, 3][][banana][][1, 2]

Suggest changes to this page
Last update at: 2026/05/26 17:22:09