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

Examples

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
    f8 := ARRAY_EXCEPT(ARRAY[]::BIGINT[], ARRAY[1, 2])  -- Empty source array
;

Result

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

Suggest changes to this page

Last update at: 2026/03/03 16:47:38

On this page

Signatures Notes Examples