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

Diagram(
  Sequence(
    Terminal("ARRAY_MERGE"),
    Terminal("("),
    NonTerminal("array_of_rows"),
    Terminal(","),
    OneOrMore(NonTerminal("array_of_rows"), Terminal(",")),
    Choice(0, Skip(),
      Sequence(
        Terminal("JOINED ON"),
        NonTerminal("field"),
        Terminal(","),
        OneOrMore(NonTerminal("field"), Terminal(",")),
      )
    ),
    Terminal(")"),
  )
)

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 JOINED 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
;