ARRAY_MERGE(...)

All functions > ARRAY OF ROWS > ARRAY_MERGE(...)

Merges multiple arrays of rows element-wise into a single array of combined rows, with optional join on common fields.

Syntax

ARRAY_MERGE(array_of_rows [, array_of_rows ...] [ JOIN ON field [, field ...] ])

ARRAY_MERGE(array_of_rows [, array_of_rows ...] [ JOIN ON field [, field ...] ])

Notes

• Requires at least 2 arrays of rows to merge
• Merges rows element-wise by position (first with first, second with second, etc.)
• If arrays have different lengths, shorter arrays are padded with NULL values
• All field names must be unique across all rows
• Result array length equals the longest input array
• Optional JOIN ON clause merges rows by joining on one or many common fields

Examples

SELECT
    -- Merge two arrays, second array is shorter and padded with NULLs
    f1 := ARRAY_MERGE(
        ARRAY(ROW(1 AS col1, 'a' AS col2), ROW(2 AS col1, 'b' AS col2)),
        ARRAY(ROW(1e0 AS col3, TRUE AS col4))
    ),
    -- Merge three arrays with different lengths
    f2 := ARRAY_MERGE(
        ARRAY(ROW(1 AS col1, 'a' AS col2), ROW(2 AS col1, 'b' AS col2)),
        ARRAY(ROW(1e0 AS col3, TRUE AS col4)),
        ARRAY(ROW(1 AS col5))
    )
;