> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MAP_COALESCE_EXTRACT

> Returns the first non-NULL matching map value according to map and key priority.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar function</span></div>

NULL maps and NULL matched values are skipped; returns NULL when no non-NULL matching value remains.

## Syntax

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
map_coalesce_extract(map, ..., key, ...)
```

## Arguments

Type notation used below:

* 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.

| Name | Type | Required | Description |
| - | - | - | - |
| map | MAP\<K,V> or ARRAY\<STRUCT\<key: K, value: V>> | Yes | One or more leading maps with identical key/value types. ARRAY/STRUCT keys retain their declared element/field types; a key must not itself have the MAP or ARRAY\<STRUCT\<key,value>> shape reserved for leading maps. A lookup key must have the map's key type after conversion. Map keys must be non-NULL. Nested values retain their declared element/field types. This argument can repeat. |
| key | K | Yes | One or more trailing candidate keys in priority order, with the map key SQL type. ARRAY/STRUCT keys retain their declared element/field types; a key must not itself have the MAP or ARRAY\<STRUCT\<key,value>> shape reserved for leading maps. A lookup key must have the map's key type after conversion. Map keys must be non-NULL. This argument can repeat. |

## 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

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | No |
| Exact result | Yes |

## 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

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT map_coalesce_extract(map_from_arrays(ARRAY['a', 'b'], ARRAY[1, 2]), 'b', 'a') AS "value";
```

| value |
| - |
| 2 |

### Skip a NULL map value

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT map_coalesce_extract(map('a', CAST(NULL AS INT), 'b', 20), 'a', 'b') AS first_non_null;
```

| first\_non\_null |
| - |
| 20 |

## 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.