MAP_CONTAINS_KEY

Test whether a map contains a given key.

Category: collectionReturns: BOOLEANDialect: Standard

Syntax

MAP_CONTAINS_KEY(map, key)

Description

## Overview Returns true if the map contains the specified key, false otherwise. MAP_CONTAINS_KEY is the canonical membership predicate for map-typed columns and is the only function that can distinguish "key absent" from "key present with NULL value" for map access patterns. Typical uses include gating rows on whether a per-row attribute map carries a given attribute, and implementing safe "get or default" logic by checking membership before reading. ## Behavior - Returns BOOLEAN (true, false, or NULL). - Equality for keys follows SQL comparison rules; for complex keys (STRUCT, ARRAY) the comparison is structural. - Runs in O(1) amortised for hash-backed maps and O(log n) for ordered maps, depending on the internal representation. - Does not access or decode the value; this is a key-only test. - Never mutates the input. ## Null and empty handling - NULL map returns NULL. - Empty map returns false for any key. - NULL key argument returns NULL; NULL keys cannot be stored in a map in this engine, so NULL as a target is effectively unresolvable. - Maps never contain NULL keys, so MAP_CONTAINS_KEY never reports true for a NULL target. ## Compatibility - Matches the array/map SQL convention for MAP_CONTAINS_KEY. Engines that use different spelling (for example HAS_KEY) provide equivalent semantics. - Equivalent to ARRAY_CONTAINS(MAP_KEYS(map), key) but avoids materialising the key array.

Parameters

Name	Type	Description
`map`		Specifies the map to search.
`key`		Specifies the key to search for. Must be type-compatible with the map's key type.

Examples

-- Key present
SELECT MAP_CONTAINS_KEY(MAP('a', 1, 'b', 2), 'a');  -- true

-- Key absent
SELECT MAP_CONTAINS_KEY(MAP('a', 1, 'b', 2), 'z');  -- false

-- Integer keys
SELECT MAP_CONTAINS_KEY(MAP(1, 'x', 2, 'y'), 2);  -- true

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

-- Filter per-session attribute maps that carry a campaign key
SELECT session_id
FROM analytics.events.user_sessions
WHERE MAP_CONTAINS_KEY(attributes, 'campaign_id');

-- Use MAP_CONTAINS_KEY to distinguish missing key from NULL value
SELECT session_id,
       CASE
         WHEN NOT MAP_CONTAINS_KEY(attributes, 'region') THEN 'unknown'
         WHEN ELEMENT_AT(attributes, 'region') IS NULL THEN 'null_region'
         ELSE ELEMENT_AT(attributes, 'region')
       END AS region
FROM analytics.events.user_sessions;

Pitfalls

MAP_CONTAINS_KEY returns true even when the stored value is NULL. It distinguishes "key present with NULL value" from "key absent", which ELEMENT_AT alone cannot.
A NULL key argument returns NULL, not false. This engine's maps never contain NULL keys, so matching a NULL target is effectively impossible.
NULL map propagates NULL. Wrap with COALESCE(col, MAP()) for a definite boolean when the column may be NULL.
Structural equality applies for complex keys. Two STRUCT keys that differ only in NULL field positions are distinct and do not match.