Map functions

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

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

**Returned value**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v23.4.0

Sorts the elements of a map in ascending 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**

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

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

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

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

Introduced in: v20.7.0

Collect all the keys and subtract corresponding values.

**Syntax**

```
mapSubtract(arg1[, arg2, ...])
```

**Arguments**

- `arg1[, arg2, ...]` — Maps or tuples of two arrays in which items in the first array represent keys, and the second array contains values for each key. [`Map(K, V)`](/docs/sql-reference/data-types/map) or [`Tuple(Array(T), Array(T))`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns one map or tuple, where the first array contains the sorted keys and the second array contains values. [`Map(K, V)`](/docs/sql-reference/data-types/map) or [`Tuple(Array(T), Array(T))`](/docs/sql-reference/data-types/tuple)

**Examples**

**With Map type**

```
SELECT mapSubtract(map(1, 1), map(1, 1))
```

```
{1:0}
```

**With tuple map**

```
SELECT mapSubtract(([toUInt8(1), 2], [toInt32(1), 1]), ([toUInt8(1), 2], [toInt32(2), 1]))
```

```
([1, 2], [-1, 0])
```

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

Introduced in: v22.3.0

For two maps, returns the first map with values updated on the values for the corresponding keys in the second map.

**Syntax**

```
mapUpdate(map1, map2)
```

**Arguments**

- `map1` — The map to update. [`Map(K, V)`](/docs/sql-reference/data-types/map)
- `map2` — The map to use for updating. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Returned value**

Returns `map1` with values updated from values for the corresponding keys in `map2`. [`Map(K, V)`](/docs/sql-reference/data-types/map)

**Examples**

**Basic usage**

```
SELECT mapUpdate(map('key1', 0, 'key3', 0), map('key1', 10, 'key2', 10))
```

```
{'key3':0,'key1':10,'key2':10}
```

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

Introduced in: v21.2.0

Returns the values 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 `values` subcolumn instead of the entire map.
The query `SELECT mapValues(m) FROM table` is transformed to `SELECT m.values FROM table`.

**Syntax**

```
mapValues(map)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

```
['v1','v2']
```

- [extractKeyValuePairs](#extractKeyValuePairs)- [extractKeyValuePairsWithEscaping](#extractKeyValuePairsWithEscaping)- [map](#map)- [mapAdd](#mapAdd)- [mapAll](#mapAll)- [mapApply](#mapApply)- [mapConcat](#mapConcat)- [mapContainsKey](#mapContainsKey)- [mapContainsKeyLike](#mapContainsKeyLike)- [mapContainsValue](#mapContainsValue)- [mapContainsValueLike](#mapContainsValueLike)- [mapExists](#mapExists)- [mapExtractKeyLike](#mapExtractKeyLike)- [mapExtractValueLike](#mapExtractValueLike)- [mapFilter](#mapFilter)- [mapFromArrays](#mapFromArrays)- [mapKeys](#mapKeys)- [mapPartialReverseSort](#mapPartialReverseSort)- [mapPartialSort](#mapPartialSort)- [mapPopulateSeries](#mapPopulateSeries)- [mapReverseSort](#mapReverseSort)- [mapSort](#mapSort)- [mapSubtract](#mapSubtract)- [mapUpdate](#mapUpdate)- [mapValues](#mapValues)

Was this page helpful?