MAP_CONCAT

Merge two or more maps into a single map; later values win on duplicate keys.

Category: collectionReturns: MAP<K, V>Dialect: Standard

Syntax

MAP_CONCAT(map1, map2, ...)

Description

## Overview Returns a new map formed by merging two or more input maps. When keys collide, the value from the map that appears later in the argument list wins. MAP_CONCAT is the canonical operator for composing a base map with overrides and for merging multiple partial attribute maps into a single enriched map. Typical uses include overlaying per-row attribute maps with static defaults, combining maps produced by multiple subqueries into a single payload, and layering a dynamic override map on top of a base configuration map. ## Behavior - Returns a MAP<K, V> whose keys are the union of the input maps' keys. - Duplicate keys resolve as last-write-wins: the value from the rightmost map containing the key is retained. - Key and value types must be compatible across inputs; the result types are the common super-types. - Key ordering in the result is not guaranteed; callers that need stable ordering should sort MAP_KEYS after merging. - Runs in O(total entries). The inputs are never mutated. ## Null and empty handling - NULL input map (any argument) returns NULL. - Empty maps contribute no entries and are effectively no-ops. - NULL values stored in an input map are preserved in the result under their key. - Keys must be non-NULL; NULL keys cannot be stored in a map in this engine. ## Compatibility - Matches the array/map SQL convention for MAP_CONCAT with last-write-wins semantics. - Equivalent to MAP_FROM_ENTRIES(CONCAT(MAP_ENTRIES(map1), MAP_ENTRIES(map2), ...)) after applying deduplication by key, but far more efficient as a direct operator.

Parameters

Name	Type	Description
`maps`		Specifies two or more maps to merge. All maps must have compatible key and value types. Duplicate keys resolve to the value from the last map that contains the key.

Examples

-- Merge two disjoint maps
SELECT MAP_CONCAT(MAP('a', 1, 'b', 2), MAP('c', 3));  -- {a: 1, b: 2, c: 3}

-- Overlapping keys resolve last-write-wins
SELECT MAP_CONCAT(MAP('a', 1, 'b', 2), MAP('b', 99, 'c', 3));  -- {a: 1, b: 99, c: 3}

-- Merge per-session attribute map with a static tag overlay
SELECT session_id, MAP_CONCAT(attributes, MAP('source', 'warehouse')) AS enriched_attrs
FROM analytics.events.user_sessions;

-- Merge three maps
SELECT MAP_CONCAT(MAP('x', 1), MAP('y', 2), MAP('z', 3));  -- {x: 1, y: 2, z: 3}

-- Merging with an empty map is a no-op
SELECT MAP_CONCAT(MAP('a', 1), CAST(MAP() AS MAP<STRING, INT>));  -- {a: 1}

-- NULL input propagates
SELECT MAP_CONCAT(CAST(NULL AS MAP<STRING, INT>), MAP('a', 1));  -- NULL

Pitfalls

Last-write-wins resolution means argument order matters. Callers that need first-write-wins must reverse the argument order.
A NULL input map propagates NULL to the whole result. Use COALESCE(col, MAP()) to degrade NULL inputs to empty maps.
Map key ordering in the output is not guaranteed. Do not assume a particular iteration order for downstream code.
Key and value types must be compatible across inputs. Mixing STRING and INT keys raises a type error rather than widening to a variant type.