MAP_VALUES

Return the values of a map as an array.

Category: collectionReturns: ARRAY<V>Dialect: Standard

Syntax

MAP_VALUES(map)

Description

## Overview Returns the values stored in a map as an ARRAY<V>. MAP_VALUES is the companion of MAP_KEYS and is the preferred way to inspect or aggregate the value side of a map-typed column without iterating over entries. Typical uses include summing or averaging the value side of a per-row metric map, passing the value list to downstream array-level aggregates, and feeding values into an EXPLODE for row-level processing. ## Behavior - Returns an ARRAY<V> whose length equals the map's cardinality. - Iteration order is not guaranteed; the returned order matches MAP_KEYS on the same invocation only when the engine does not separately recompute the two arrays. - NULL values in the map are preserved as NULL elements. - Works on any value type, including STRUCT, ARRAY, and nested MAP. - Does not mutate the input. ## Null and empty handling - NULL input map returns NULL. - Empty input map returns an empty array of the value type. - NULL values inside the map are preserved as NULL elements. - The result array can contain NULLs even when the map is non-empty. ## Compatibility - Matches the array/map SQL convention for MAP_VALUES. Equivalent to TRANSFORM(MAP_ENTRIES(m), e -> e.value).

Parameters

Name	Type	Description
`map`		Specifies the map whose values are extracted as an array.

Examples

-- Extract values
SELECT MAP_VALUES(MAP('a', 1, 'b', 2, 'c', 3));  -- [1, 2, 3]

-- NULL values are included
SELECT MAP_VALUES(MAP('x', 10, 'y', CAST(NULL AS INT)));  -- [10, NULL]

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

-- NULL map returns NULL
SELECT MAP_VALUES(CAST(NULL AS MAP<STRING, INT>));  -- NULL

-- Sum the values of a per-session metric map
SELECT session_id, ARRAY_SUM(MAP_VALUES(metric_map)) AS total
FROM analytics.events.user_sessions;

-- Enumerate per-row values for downstream filtering
SELECT session_id, v
FROM analytics.events.user_sessions
LATERAL VIEW EXPLODE(MAP_VALUES(attributes)) t AS v;

Pitfalls

Value ordering is not guaranteed and may not align with MAP_KEYS ordering on the same input. Use MAP_ENTRIES when keys and values must be read as pairs.
NULL values are preserved in the output array. Wrap with ARRAY_COMPACT if consumers cannot tolerate NULL elements.
NULL map returns NULL, not an empty array. Use COALESCE(col, MAP()) if NULL and empty should behave identically.
For complex value types the result elements carry the declared value type. Access nested fields with standard field syntax rather than casting.