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
    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 two arrays, second array is shorter and padded with NULLs
    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)]) -- Merge three arrays with different lengths
;