Map functions

INSERT INTO tab VALUES ({'abc':'abc','def':'def'}), ({'hij':'hij','klm':'klm'});

SELECT mapExtractKeyLike(a, 'a%') FROM tab;
```

```
┌─mapExtractKeyLike(a, 'a%')─┐
│ {'abc':'abc'}              │
│ {}                         │
└────────────────────────────┘
```

## mapExtractValueLike[​](#mapExtractValueLike "Direct link to mapExtractValueLike")

Introduced in: v25.5.0

Given a map with string values and a `LIKE` pattern, this function returns a map with elements where the value matches the pattern.

**Syntax**

```
mapExtractValueLike(map, pattern)
```

**Arguments**

- `map` — Map to extract from. [`Map(K, V)`](/docs/sql-reference/data-types/map)
- `pattern` — Pattern to match values against. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a map containing elements the value matching the specified pattern. If no elements match the pattern, an empty map is returned. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

```
CREATE TABLE tab (a Map(String, String))
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO tab VALUES ({'abc':'abc','def':'def'}), ({'hij':'hij','klm':'klm'});

SELECT mapExtractValueLike(a, 'a%') FROM tab;
```

```
┌─mapExtractValueLike(a, 'a%')─┐
│ {'abc':'abc'}                │
│ {}                           │
└──────────────────────────────┘
```

## mapFilter[​](#mapFilter "Direct link to mapFilter")

Introduced in: v22.3.0

Filters a map by applying a function to each map element.

**Syntax**

```
mapFilter(func, map)
```

**Arguments**

- `func` — Lambda function. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `map` — Map to filter. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Returned value**

Returns a map containing only the elements in the map for which `func` returns something other than `0`. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

```
SELECT mapFilter((k, v) -> v > 1, map('k1', 1, 'k2', 2))
```

```
{'k2':2}
```

## mapFromArrays[​](#mapFromArrays "Direct link to mapFromArrays")

Introduced in: v23.3.0

Creates a map from an array or map of keys and an array or map of values.
The function is a convenient alternative to syntax `CAST([...], 'Map(key_type, value_type)')`.

**Syntax**

```
mapFromArrays(keys, values)
```

**Aliases**: `MAP_FROM_ARRAYS`

**Arguments**

- `keys` — Array or map of keys to create the map from. [`Array`](/docs/sql-reference/data-types/array) or [`Map`](/docs/sql-reference/data-types/map)
- `values` — Array or map of values to create the map from. [`Array`](/docs/sql-reference/data-types/array) or [`Map`](/docs/sql-reference/data-types/map)

**Returned value**

Returns a map with keys and values constructed from the key array and value array/map. [`Map`](/docs/sql-reference/data-types/map)

**Examples**

**Basic usage**

```
SELECT mapFromArrays(['a', 'b', 'c'], [1, 2, 3])
```

```
{'a':1,'b':2,'c':3}
```

**With map inputs**

```
SELECT mapFromArrays([1, 2, 3], map('a', 1, 'b', 2, 'c', 3))
```

```
{1:('a', 1), 2:('b', 2), 3:('c', 3)}
```

## mapKeys[​](#mapKeys "Direct link to mapKeys")

Introduced in: v21.2.0

Returns the keys of a given map.
This function can be optimized by enabling setting [`optimize_functions_to_subcolumns`](/docs/operations/settings/settings#optimize_functions_to_subcolumns).
With the setting enabled, the function only reads the `keys` subcolumn instead of the entire map.
The query `SELECT mapKeys(m) FROM table` is transformed to `SELECT m.keys FROM table`.

**Syntax**

```
mapKeys(map)
```

**Arguments**

- `map` — Map to extract keys from. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Returned value**

Returns array containing all keys from the map. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT mapKeys(map('k1', 'v1', 'k2', 'v2'))
```

```
['k1','k2']
```

## mapPartialReverseSort[​](#mapPartialReverseSort "Direct link to mapPartialReverseSort")

Introduced in: v23.4.0

Sorts the elements of a map in descending order with additional limit argument allowing partial sorting.
If the func function is specified, the sorting order is determined by the result of the func function applied to the keys and values of the map.

**Syntax**

```
mapPartialReverseSort([func,] limit, map)
```

**Arguments**

- `func` — Optional. Lambda function. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `limit` — Elements in the range `[1..limit]` are sorted. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `map` — Map to sort. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Returned value**

Returns a partially sorted map in descending order. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

```
SELECT mapPartialReverseSort((k, v) -> v, 2, map('k1', 3, 'k2', 1, 'k3', 2))
```

```
{'k1':3,'k3':2,'k2':1}
```

## mapPartialSort[​](#mapPartialSort "Direct link to mapPartialSort")

Introduced in: v23.4.0

Sorts the elements of a map in ascending order with additional limit argument allowing partial sorting.
If the func function is specified, the sorting order is determined by the result of the func function applied to the keys and values of the map.

**Syntax**

```
mapPartialSort([func,] limit, map)
```

**Arguments**

- `func` — Optional. Lambda function. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `limit` — Elements in the range `[1..limit]` are sorted. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `map` — Map to sort. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Returned value**

Returns a partially sorted map. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

```
SELECT mapPartialSort((k, v) -> v, 2, map('k1', 3, 'k2', 1, 'k3', 2))
```

```
{'k2':1,'k3':2,'k1':3}
```

## mapPopulateSeries[​](#mapPopulateSeries "Direct link to mapPopulateSeries")

Introduced in: v20.10.0

Fills missing key-value pairs in a map with integer keys.
To support extending the keys beyond the largest value, a maximum key can be specified.
More specifically, the function returns a map in which the keys form a series from the smallest to the largest key (or max argument if specified) with step size of 1, and corresponding values.
If no value is specified for a key, a default value is used as value.
In case keys repeat, only the first value (in order of appearance) is associated with the key.

**Syntax**

```
mapPopulateSeries(map[, max]) | mapPopulateSeries(keys, values[, max])
```

**Arguments**

- `map` — Map with integer keys. [`Map((U)Int*, V)`](/docs/sql-reference/data-types/map)
- `keys` — Array of keys. [`Array(T)`](/docs/sql-reference/data-types/array)
- `values` — Array of values. [`Array(T)`](/docs/sql-reference/data-types/array)
- `max` — Optional. Maximum key value. [`Int8`](/docs/sql-reference/data-types/int-uint) or [`Int16`](/docs/sql-reference/data-types/int-uint) or [`Int32`](/docs/sql-reference/data-types/int-uint) or [`Int64`](/docs/sql-reference/data-types/int-uint) or [`Int128`](/docs/sql-reference/data-types/int-uint) or [`Int256`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a map or a tuple of two arrays where the first has keys in sorted order, and the second values for the corresponding keys. [`Map(K, V)`](/docs/sql-reference/data-types/map) or [`Tuple(Array(UInt*), Array(Any))`](/docs/sql-reference/data-types/tuple)

**Examples**

**With Map type**

```
SELECT mapPopulateSeries(map(1, 10, 5, 20), 6)
```

```
{1:10, 2:0, 3:0, 4:0, 5:20, 6:0}
```

**With mapped arrays**

```
SELECT mapPopulateSeries([1, 2, 4], [11, 22, 44], 5)
```

```
([1, 2, 3, 4, 5], [11, 22, 0, 44, 0])
```

## mapReverseSort[​](#mapReverseSort "Direct link to mapReverseSort")

Introduced in: v23.4.0

Sorts the elements of a map in descending order.
If the func function is specified, the sorting order is determined by the result of the func function applied to the keys and values of the map.

**Syntax**

```
mapReverseSort([func,] map)
```

**Arguments**