MAP_ENTRIES

Return the entries of a map as an array of key-value structs.

Category: collectionReturns: ARRAY<STRUCT<key, value>>Dialect: Standard

Syntax

MAP_ENTRIES(map)

Description

## Overview Returns the contents of a map as an ARRAY<STRUCT<key, value>>. MAP_ENTRIES is the canonical inspector for maps and is the inverse of MAP_FROM_ENTRIES. It is the preferred way to iterate over both keys and values in a single pass, and the idiomatic bridge between map-typed columns and relational processing via EXPLODE or UNNEST. Typical uses include denormalising per-row attribute maps into a key-value row set for downstream joins, serialising maps to an array payload for export formats that do not natively support maps, and feeding per-entry transforms. ## Behavior - Returns ARRAY<STRUCT<key: K, value: V>> matching the map's key and value types. - Each struct contains two fields named 'key' and 'value'. - Iteration order is not guaranteed; different invocations on the same map may produce differently-ordered arrays. - Length of the result equals the map's cardinality. - NULL values in the map are preserved as NULL value fields. - Does not mutate the input. ## Null and empty handling - NULL input map returns NULL. - Empty input map returns an empty array of ARRAY<STRUCT<K, V>>. - NULL values under real keys appear with value = NULL. - Keys cannot be NULL (the engine does not store NULL keys), so the key field is always non-NULL. ## Compatibility - Matches the array/map SQL convention for MAP_ENTRIES. MAP_FROM_ENTRIES is the inverse constructor that rebuilds a map from such an array. - Equivalent to ARRAYS_ZIP(MAP_KEYS(map), MAP_VALUES(map)), except the output struct fields here are named 'key' and 'value' and ordering between the two must be kept consistent.

Parameters

Name	Type	Description
`map`		Specifies the map to decompose into an array of entries.

Examples

-- Extract entries
SELECT MAP_ENTRIES(MAP('a', 1, 'b', 2));  -- [{key: 'a', value: 1}, {key: 'b', value: 2}]

-- Single-entry map
SELECT MAP_ENTRIES(MAP('x', 100));  -- [{key: 'x', value: 100}]

-- Empty map returns an empty array
SELECT MAP_ENTRIES(CAST(MAP() AS MAP<STRING, INT>));  -- []

-- Iterate per-session attribute maps
SELECT session_id, e.key AS attr_key, e.value AS attr_val
FROM analytics.events.user_sessions
LATERAL VIEW EXPLODE(MAP_ENTRIES(attributes)) t AS e;

-- Use to serialise a map to a known shape for export
SELECT MAP_ENTRIES(MAP('x', 1, 'y', 2)) AS pairs;

-- NULL input returns NULL
SELECT MAP_ENTRIES(CAST(NULL AS MAP<STRING, INT>));  -- NULL

Pitfalls

Entry ordering is not guaranteed. Wrap with ARRAY_SORT on an explicit key comparator when you need stable output order.
NULL values are preserved with value = NULL. To filter entries with NULL values, apply ARRAY_FILTER on the result.
NULL input returns NULL, not an empty array. Use COALESCE(col, MAP()) if the column may be NULL.
Result field names are the strings 'key' and 'value'. Downstream code must use these exact field names rather than positional access.