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

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

Result

f1 ARRAY
[]

Suggest changes to this page

Last update at: 2026/06/20 10:08:10

On this page

Signatures Notes See also Examples Set subtraction Edge cases