ARRAY_EXCEPT()

All functions > ARRAY > ARRAY_EXCEPT()

Returns the difference of two arrays using set semantics.

Signatures

Returns: An array containing elements from array1 that do not appear in array2

ARRAY_EXCEPT(array1: ARRAY<T>, array2: ARRAY<T>) → ARRAY<T>

sql

Parameter	Type	Required	Description
`array1`	`ARRAY<T>`	Yes	The source array from which elements will be removed
`array2`	`ARRAY<T>`	Yes	The array containing elements to be removed from array1

Notes

Uses set semantics: removes ALL occurrences of matching elements
Performs set subtraction (array1 - array2)
Result contains only unique elements from array1 not found in array2
Result is sorted lexicographically
Empty arrays are handled gracefully
Supports ARRAY<ROW> and ARRAY<ARRAY<T>>: both arrays must have the same element type; mismatched schemas raise a user error

Examples

Set subtraction

FeatureQL

SELECT
    f1 := ARRAY_EXCEPT(ARRAY['A', 'B'], ARRAY['B', 'C']), -- Basic exclusion
    f2 := ARRAY_EXCEPT(ARRAY['A', 'B', 'C'], ARRAY['B', 'C']), -- Excluding multiple elements
    f3 := ARRAY_EXCEPT(ARRAY['A', 'B', 'B', 'B', 'C'], ARRAY['B']), -- Set exclusion: removes ALL 'B's
    f4 := ARRAY_EXCEPT(ARRAY['A', 'A', 'B'], ARRAY['A', 'B', 'B']), -- Set exclusion: removes all 'A's and 'B's
    f5 := ARRAY_EXCEPT(ARRAY[1, 1, 2, 2, 2], ARRAY[1, 2, 2]), -- Set exclusion: removes all 1's and 2's
    f6 := ARRAY_EXCEPT(ARRAY['A', 'B'], ARRAY['A', 'B']), -- Complete removal
    f7 := ARRAY_EXCEPT(ARRAY[1, 2, 3], ARRAY[4, 5]) -- No elements to remove
;

Result

f1 ARRAY	f2 ARRAY	f3 ARRAY	f4 ARRAY	f5 ARRAY	f6 ARRAY	f7 ARRAY
[A]	[A]	[A, C]	[]	[]	[]	[1, 2, 3]

Edge cases

FeatureQL

SELECT
    f1 := ARRAY_EXCEPT(ARRAY[]::BIGINT[], ARRAY[1, 2]) -- Empty source array
;

Result

f1 ARRAY
[]

Suggest changes to this page

Last update at: 2026/05/26 17:22:09

On this page

Signatures Notes See also Examples Set subtraction Edge cases