ARRAY_UNION()

All functions > ARRAY > ARRAY_UNION()

Returns the union of two arrays, keeping the first occurrence of each element.

Signatures

Returns: An array containing all unique elements from both input arrays

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

Notes

• Walks the left array, then appends elements that appear only on the right
• Keeps the first occurrence of each element, in that order
• ARRAY_UNION(a, b) and ARRAY_UNION(b, a) can differ as arrays
• NULL stays one member, at the position of its first occurrence
• A NULL array returns NULL
• Works with arrays of any comparable type
• 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

See also

• Array functions

Examples

Typical unions

RETURN
    -- Basic union with numeric arrays
    f1 := ARRAY_UNION(ARRAY(1, 2, 3), ARRAY(2, 3, 4)),
    -- String arrays with duplicates
    f2 := ARRAY_UNION(ARRAY('apple', 'banana'), ARRAY('banana', 'cherry')),
    -- Multiple overlapping elements
    f3 := ARRAY_UNION(ARRAY('A', 'B', 'C'), ARRAY('B', 'C', 'D')),
    -- Duplicates within arrays eliminated
    f4 := ARRAY_UNION(ARRAY(1, 1, 2), ARRAY(2, 2, 3)),
    -- No overlapping elements
    f5 := ARRAY_UNION(ARRAY(1, 2), ARRAY(3, 4)),
    -- Left array first, then new elements from the right
    f6 := ARRAY_UNION(ARRAY('Z', 'A'), ARRAY('B', 'Y'))
;

Edge cases

RETURN
    -- Empty array union
    f1 := ARRAY_UNION(ARRAY()::ARRAY<BIGINT>, ARRAY(1, 2))
;