MAP_COALESCE_EXTRACT

​

Syntax

​

Scalar

map_coalesce_extract(map, ..., key, ...)

​

Arguments

• K is the map key type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY or STRUCT.
• V is the map value type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.

​

Returns

• Type: Common map value type V
• Possible values: The first successful map/key match.
• NULL behavior: NULL maps and NULL matched values are skipped; returns NULL when no non-NULL matching value remains.
• Shape: Preserves the selected map value shape/type; nested ARRAY or STRUCT values retain their elements/fields.
• Ordering: Copies the selected value without reordering a nested container.

​

Properties

​

Usage notes

• A NULL map contributes no value, so later maps are still tried.
• Map operands are tried from left to right; within a map, candidate keys are tried in their listed priority order. A duplicated stored key uses the first non-NULL matching entry in encounter order.
• A typed NULL candidate key cannot match valid MAP keys, which are non-NULL. In an ARRAY<STRUCT<key,value>> representation with nullable keys, a typed NULL key can match a NULL stored key.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• For ARRAY-valued keys or values, construct the input map with MAP_FROM_ARRAYS. The ordinary two-argument MAP constructor can interpret ARRAY arguments as parallel key/value lists.

​

Examples

​

Typical behavior

SELECT map_coalesce_extract(map_from_arrays(ARRAY['a', 'b'], ARRAY[1, 2]), 'b', 'a') AS "value";

​

Skip a NULL map value

SELECT map_coalesce_extract(map('a', CAST(NULL AS INT), 'b', 20), 'a', 'b') AS first_non_null;

​

Limitations

• All maps must have exactly the same key and value types as the first map. Mixed value types are not widened automatically. Cast NULL candidate keys to the map’s key type.
• For maps represented as ARRAY<STRUCT<key, value>>, each entry must be a non-NULL struct. NULL entry structs can give unreliable results.
• The final key argument cannot itself be a MAP or an array of two-field structs. Those types cannot be distinguished from the preceding map arguments.