---
title: Tuple Map Functions reference
meta:
    description: Functions for tuple maps.
headingMaxLevels: 2
---

# Tuple map functions

The following functions are available for tuple maps.

## map

Constructs a map data type from a list of key-value pairs provided as arguments.

### Syntax

```sql
map(key1, value1[, key2, value2, ...])
```

### Arguments

- `key_n`: Type. The keys for the map entries. Any type supported as a key type of a Map.
- `value_n`: Type. The values for the map entries. Any type supported as a value type of a Map.

### Returns

A map containing `key:value` pairs. `Map(key, value)`.

### Example

```sql
SELECT map('key1', number, 'key2', number * 2) FROM numbers(3)
```

Result:

```result
┌─map('key1', number, 'key2', multiply(number, 2))─┐
│ {'key1':0,'key2':0}                              │
│ {'key1':1,'key2':2}                              │
│ {'key1':2,'key2':4}                              │
└──────────────────────────────────────────────────┘
```

## mapFromArrays

Generates a map by pairing elements from a keys array and a values array.

Alias: `MAP_FROM_ARRAYS(keys, values)`

### Syntax

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

### Arguments

- `keys`: Array or Map. The source for the map's keys. If an array, Tinybird accepts `Array(Nullable(T))` or `Array(LowCardinality(Nullable(T)))` as its type as long as it doesn't contain `NULL` values.
- `values`: Array or Map. The source for the map's values.

### Returns

A map with keys and values constructed from the provided key and value arrays or maps.

### Example

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

Result:

```result
┌─mapFromArrays(['a', 'b', 'c'], [1, 2, 3])─┐
│ {'a':1,'b':2,'c':3}                       │
└───────────────────────────────────────────┘
```

`mapFromArrays` also accepts arguments of type Map. These are cast to array of tuples during execution.

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

Result:

```result
┌─mapFromArrays([1, 2, 3], map('a', 1, 'b', 2, 'c', 3))─┐
│ {1:('a',1),2:('b',2),3:('c',3)}                       │
└───────────────────────────────────────────────────────┘
```

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

Result:

```result
┌─mapFromArrays(map('a', 1, 'b', 2, 'c', 3), [1, 2, 3])─┐
│ {('a',1):1,('b',2):2,('c',3):3}                       │
└───────────────────────────────────────────────────────┘
```

## extractKeyValuePairs

Parses a string containing key-value pairs into a `Map(String, String)`. It can handle various delimiters and quoted values, making it suitable for parsing log-like data.

Alias:
- `str_to_map`
- `mapFromString`

### Syntax

```sql
extractKeyValuePairs(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]])
```

### Arguments

- `data`: String or FixedString. The input string from which to extract key-value pairs.
- `key_value_delimiter`: String or FixedString. An optional single character that separates keys from values. Defaults to `:`.
- `pair_delimiters`: String or FixedString. An optional set of characters that separate key-value pairs. Defaults to ` `, `,`, and `;`.
- `quoting_character`: String or FixedString. An optional single character used for quoting keys and values. Defaults to `"`.

### Returns

A map of key-value pairs. `Map(String, String)`.

### Examples

Query

```sql
SELECT extractKeyValuePairs('name:neymar, age:31 team:psg,nationality:brazil') as kv
```

Result:

```result
┌─kv──────────────────────────────────────────────────────────────────────┐
│ {'name':'neymar','age':'31','team':'psg','nationality':'brazil'}        │
└─────────────────────────────────────────────────────────────────────────┘
```

With a single quote `'` as quoting character:

```sql
SELECT extractKeyValuePairs('name:\'neymar\';\'age\':31;team:psg;nationality:brazil,last_key:last_value', ':', ';,', '\'') as kv
```

Result:

```result
┌─kv───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {'name':'neymar','age':'31','team':'psg','nationality':'brazil','last_key':'last_value'}                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

Escape sequences without escape sequences support:

```sql
SELECT extractKeyValuePairs('age:a\\x0A\\n\\0') AS kv
```

Result:

```result
┌─kv─────────────────────┐
│ {'age':'a\\x0A\\n\\0'} │
└────────────────────────┘
```

To restore a map string key-value pairs serialized with `toString`:

```sql
SELECT
    map('John', '33', 'Paula', '31') AS m,
    toString(m) as map_serialized,
    extractKeyValuePairs(map_serialized, ':', ',', '\'') AS map_restored
```

Result:

```result
Row 1:
──────
m:              {'John':'33','Paula':'31'}
map_serialized: {'John':'33','Paula':'31'}
map_restored:   {'John':'33','Paula':'31'}
```

## extractKeyValuePairsWithEscaping

Similar to `extractKeyValuePairs`, this function converts a string of key-value pairs into a `Map(String, String)`, but it also supports common escape sequences within the string.

### Syntax

```sql
extractKeyValuePairsWithEscaping(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]])
```

### Arguments

- `data`: String or FixedString. The input string from which to extract key-value pairs.
- `key_value_delimiter`: String or FixedString. An optional single character that separates keys from values. Defaults to `:`.
- `pair_delimiters`: String or FixedString. An optional set of characters that separate key-value pairs. Defaults to ` `, `,`, and `;`.
- `quoting_character`: String or FixedString. An optional single character used for quoting keys and values. Defaults to `"`.

### Returns

A map of key-value pairs, with escape sequences processed. `Map(String, String)`.

### Example

Escape sequences with escape sequence support turned on:

```sql
SELECT extractKeyValuePairsWithEscaping('age:a\\x0A\\n\\0') AS kv
```

Result:

```result
┌─kv────────────────┐
│ {'age':'a\n\n\0'} │
└───────────────────┘
```

## mapAdd

Combines multiple maps or array-tuples by summing the values for common keys. If a key exists in multiple inputs, its values are added together.

### Syntax

```sql
mapAdd(arg1, arg2 [, ...])
```

### Arguments

- `arg1`, `arg2`, ...: Map or Tuple(Array, Array). Each argument can be a map or a tuple where the first array contains keys and the second array contains corresponding values. Key types must be consistent across all arguments, and value types are promoted to a common numeric type (`Int64`, `UInt64`, or `Float64`).

### Returns

A single map or tuple where keys are sorted and values are the sum of corresponding values from the input arguments.

### Example

Query with `Map` type:

```sql
SELECT mapAdd(map(1,1), map(1,1))
```

Result:

```result
┌─mapAdd(map(1, 1), map(1, 1))─┐
│ {1:2}                        │
└──────────────────────────────┘
```

Query with a tuple:

```sql
SELECT mapAdd(([toUInt8(1), 2], [1, 1]), ([toUInt8(1), 2], [1, 1])) as res, toTypeName(res) as type
```

Result:

```result
┌─res───────────┬─type───────────────────────────────┐
│ ([1,2],[2,2]) │ Tuple(Array(UInt8), Array(UInt64)) │
└───────────────┴────────────────────────────────────┘
```

## mapSubtract

Combines multiple maps or array-tuples by subtracting the values for common keys.

### Syntax

```sql
mapSubtract(Tuple(Array, Array), Tuple(Array, Array) [, ...])
```

### Arguments

- `arg1`, `arg2`, ...: Map or Tuple(Array, Array). Each argument can be a map or a tuple where the first array contains keys and the second array contains corresponding values. Key types must be consistent across all arguments, and value types are promoted to a common numeric type (`Int64`, `UInt64`, or `Float64`).

### Returns

A single map or tuple where keys are sorted and values are the result of subtracting corresponding values from the input arguments.

### Example

Query with `Map` type:

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

Result:

```result
┌─mapSubtract(map(1, 1), map(1, 1))─┐
│ {1:0}                             │
└───────────────────────────────────┘
```

Query with a tuple map:

```sql
SELECT mapSubtract(([toUInt8(1), 2], [toInt32(1), 1]), ([toUInt8(1), 2], [toInt32(2), 1])) as res, toTypeName(res) as type
```

Result:

```result
┌─res────────────┬─type──────────────────────────────┐
│ ([1,2],[-1,0]) │ Tuple(Array(UInt8), Array(Int64)) │
└────────────────┴───────────────────────────────────┘
```

## mapPopulateSeries

Fills in missing integer keys in a map or array-tuple to create a continuous series of keys. You can optionally specify a maximum key value to extend the series. Missing values are filled with a default (usually zero).

### Syntax

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

### Arguments

- `map`: Map. A map with integer keys.
- `keys`: Array(Int). An array of integer keys.
- `values`: Array(Int). An array of values corresponding to the keys.
- `max`: Int8, Int16, Int32, Int64, Int128, Int256. An optional maximum key value to ensure the series extends up to this point.

### Returns

A map or a tuple of two arrays (sorted keys and corresponding values) with the series populated.

### Example

Query with `Map` type:

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

Result:

```result
┌─mapPopulateSeries(map(1, 10, 5, 20), 6)─┐
│ {1:10,2:0,3:0,4:0,5:20,6:0}             │
└─────────────────────────────────────────┘
```

Query with mapped arrays:

```sql
SELECT mapPopulateSeries([1,2,4], [11,22,44], 5) AS res, toTypeName(res) AS type
```

Result:

```result
┌─res──────────────────────────┬─type──────────────────────────────┐
│ ([1,2,3,4,5],[11,22,0,44,0]) │ Tuple(Array(UInt8), Array(UInt8)) │
└──────────────────────────────┴───────────────────────────────────┘
```

## mapContains

Checks if a specified key exists within a given map.

### Syntax

```sql
mapContains(map, key)
```

### Arguments

- `map`: Map. The map to search within.
- `key`: Type. The key to look for. Its type must match the key type of the `map`.

### Returns

`UInt8`. Returns `1` if the `map` contains the `key`, otherwise `0`.

### Example

Query:

```sql
SELECT mapContains(a, 'name')
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['name', 'age'], ['eleven', '11']), (['number', 'position'], ['twelve', '6.0'])) )
```

Result:

```result
┌─mapContains(a, 'name')─┐
│                      1 │
│                      0 │
└────────────────────────┘
```

## mapKeys

Extracts all keys from a map and returns them as an array.

### Syntax

```sql
mapKeys(map)
```

### Arguments

- `map`: Map. The input map.

### Returns

Array. An array containing all the keys present in the `map`.

### Example

Query:

```sql
SELECT mapKeys(a)
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['name', 'age'], ['eleven', '11']), (['number', 'position'], ['twelve', '6.0'])) )
```

Result:

```result
┌─mapKeys(a)────────────┐
│ ['name','age']        │
│ ['number','position'] │
└───────────────────────┘
```

## mapValues

Extracts all values from a map and returns them as an array.

### Syntax

```sql
mapValues(map)
```

### Arguments

- `map`: Map. The input map.

### Returns

Array. An array containing all the values present in the `map`.

### Example

Query:

```sql
SELECT mapValues(a)
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['name', 'age'], ['eleven', '11']), (['number', 'position'], ['twelve', '6.0'])) )
```

Result:

```result
┌─mapValues(a)─────┐
│ ['eleven','11']  │
│ ['twelve','6.0'] │
└──────────────────┘
```

## mapContainsKeyLike

Checks if any key in a map matches a specified SQL `LIKE` pattern.

### Syntax

```sql
mapContainsKeyLike(map, pattern)
```

### Arguments

- `map`: Map. The map to search within.
- `pattern`: String. The `LIKE` pattern to match against the map keys.

### Returns

`UInt8`. Returns `1` if any key in the `map` matches the pattern, otherwise `0`.

### Example

Query:

```sql
SELECT mapContainsKeyLike(a, 'a%')
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['abc', 'def'], ['abc', 'def']), (['hij', 'klm'], ['hij', 'klm'])) )
```

Result:

```result
┌─mapContainsKeyLike(a, 'a%')─┐
│                           1 │
│                           0 │
└─────────────────────────────┘
```

## mapExtractKeyLike

Filters a map, returning a new map that contains only the key-value pairs where the key matches a specified SQL `LIKE` pattern.

### Syntax

```sql
mapExtractKeyLike(map, pattern)
```

### Arguments

- `map`: Map. The input map.
- `pattern`: String. The `LIKE` pattern to match against the map keys.

### Returns

Map. A new map containing only the entries whose keys match the specified pattern. Returns an empty map if no keys match.

### Example

Query:

```sql
SELECT mapExtractKeyLike(a, 'a%')
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['abc', 'def'], ['abc', 'def']), (['hij', 'klm'], ['hij', 'klm'])) )
```

Result:

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

## mapApply

Transforms each key-value pair in a map by applying a lambda function to them.

### Syntax

```sql
mapApply(func, map)
```

### Arguments

- `func`: Lambda function. A lambda function that takes the key and value of each map entry as arguments and returns a new key-value pair.
- `map`: Map. The input map.

### Returns

Map. A new map where each key-value pair is the result of applying the `func` to the corresponding entry in the original map.

### Example

Query:

```sql
SELECT mapApply((k, v) -> (k, v * 10), _map) AS r
FROM
(
    SELECT map('key1', number, 'key2', number * 2) AS _map
    FROM numbers(3)
)
```

Result:

```result
┌─r─────────────────────┐
│ {'key1':0,'key2':0}   │
│ {'key1':10,'key2':20} │
│ {'key1':20,'key2':40} │
└───────────────────────┘
```

## mapFilter

Creates a new map containing only the key-value pairs from the original map for which a provided lambda function returns a non-zero (true) value.

### Syntax

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

### Arguments

- `func`: Lambda function. A lambda function that takes the key and value of each map entry as arguments and returns a boolean (or integer convertible to boolean).
- `map`: Map. The input map.

### Returns

Map. A new map containing only the entries that satisfy the condition defined by `func`.

### Example

Query:

```sql
SELECT mapFilter((k, v) -> ((v % 2) = 0), _map) AS r
FROM
(
    SELECT map('key1', number, 'key2', number * 2) AS _map
    FROM numbers(3)
)
```

Result:

```result
┌─r───────────────────┐
│ {'key1':0,'key2':0} │
│ {'key2':2}          │
│ {'key1':2,'key2':4} │
└─────────────────────┘
```

## mapUpdate

Merges two maps, updating the values of the first map with corresponding values from the second map where keys overlap. New keys from the second map are added to the result.

### Syntax

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

### Arguments

- `map1`: Map. The base map to be updated.
- `map2`: Map. The map containing new or updated key-value pairs.

### Returns

Map. A new map based on `map1`, with values for common keys overwritten by `map2`'s values, and new keys from `map2` added.

### Example

Query:

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

Result:

```result
┌─map────────────────────────────┐
│ {'key3':0,'key1':10,'key2':10} │
└────────────────────────────────┘
```

## mapConcat

Combines multiple maps into a single map. If duplicate keys exist across the input maps, all entries are included, but only the first occurrence of a key is accessible via direct key lookup.

### Syntax

```sql
mapConcat(maps)
```

### Arguments

- `maps`: Map. One or more maps to concatenate.

### Returns

Map. A single map containing all key-value pairs from the input maps.

### Examples

Query:

```sql
SELECT mapConcat(map('key1', 1, 'key3', 3), map('key2', 2)) AS map
```

Result:

```result
┌─map──────────────────────────┐
│ {'key1':1,'key3':3,'key2':2} │
└──────────────────────────────┘
```

Query:

```sql
SELECT mapConcat(map('key1', 1, 'key2', 2), map('key1', 3)) AS map, map['key1']
```

Result:

```result
┌─map──────────────────────────┬─elem─┐
│ {'key1':1,'key2':2,'key1':3} │    1 │
└──────────────────────────────┴──────┘
```

## mapExists

Checks if at least one key-value pair in a map satisfies a condition defined by a lambda function.

### Syntax

```sql
mapExists([func,] map)
```

### Arguments

- `func`: Lambda function. An optional lambda function that takes the key and value of each map entry.
- `map`: Map. The input map.

### Returns

`UInt8`. Returns `1` if `func` returns a non-zero value for any key-value pair, otherwise `0`.

{% callout type="info" %}
`mapExists` is a higher-order function.
You can pass a lambda function to it as the first argument.
{% /callout %}

### Example

Query:

```sql
SELECT mapExists((k, v) -> (v = 1), map('k1', 1, 'k2', 2)) AS res
```

Result:

```result
┌─res─┐
│   1 │
└─────┘
```

## mapAll

Checks if all key-value pairs in a map satisfy a condition defined by a lambda function.

### Syntax

```sql
mapAll([func,] map)
```

### Arguments

- `func`: Lambda function. An optional lambda function that takes the key and value of each map entry.
- `map`: Map. The input map.

### Returns

`UInt8`. Returns `1` if `func` returns a non-zero value for all key-value pairs, otherwise `0`.

{% callout type="info" %}
Note that the `mapAll` is a [higher-order function](.#higher-order-functions).
You can pass a lambda function to it as the first argument.
{% /callout %}

### Example

Query:

```sql
SELECT mapAll((k, v) -> (v = 1), map('k1', 1, 'k2', 2)) AS res
```

Result:

```result
┌─res─┐
│   0 │
└─────┘
```

## mapSort

Sorts the key-value pairs within a map in ascending order based on their keys. An optional lambda function can be provided to define a custom sorting logic.

### Syntax

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

### Arguments

- `func`: Lambda function. An optional lambda function that takes the key and value of each map entry to determine the sorting order.
- `map`: Map. The input map to sort.

### Returns

Map. A new map with its key-value pairs sorted.

### Examples

```sql
SELECT mapSort(map('key2', 2, 'key3', 1, 'key1', 3)) AS map
```

Result:

```result
┌─map──────────────────────────┐
│ {'key1':3,'key2':2,'key3':1} │
└──────────────────────────────┘
```

```sql
SELECT mapSort((k, v) -> v, map('key2', 2, 'key3', 1, 'key1', 3)) AS map
```

Result:

```result
┌─map──────────────────────────┐
│ {'key3':1,'key2':2,'key1':3} │
└──────────────────────────────┘
```

## mapPartialSort

Sorts a specified number of elements in a map in ascending order. An optional lambda function can be used to define a custom sorting criterion.

### Syntax

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

### Arguments

- `func`: Lambda function. An optional lambda function to apply to the keys and values of the map for custom sorting.
- `limit`: (U)Int. The number of elements (from 1 up to `limit`) to sort.
- `map`: Map. The map to partially sort.

### Returns

Map. A partially sorted map.

### Example

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

Result:

```result
┌─mapPartialSort(lambda(tuple(k, v), v), 2, map('k1', 3, 'k2', 1, 'k3', 2))─┐
│ {'k2':1,'k3':2,'k1':3}                                                    │
└───────────────────────────────────────────────────────────────────────────┘
```

## mapReverseSort

Sorts the key-value pairs within a map in descending order based on their keys. An optional lambda function can be provided to define a custom sorting logic.

### Syntax

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

### Arguments

- `func`: Lambda function. An optional lambda function that takes the key and value of each map entry to determine the sorting order.
- `map`: Map. The input map to sort.

### Returns

Map. A new map with its key-value pairs sorted in descending order.

### Examples

```sql
SELECT mapReverseSort(map('key2', 2, 'key3', 1, 'key1', 3)) AS map
```

Result:

```result
┌─map──────────────────────────┐
│ {'key3':1,'key2':2,'key1':3} │
└──────────────────────────────┘
```

```sql
SELECT mapReverseSort((k, v) -> v, map('key2', 2, 'key3', 1, 'key1', 3)) AS map
```

Result:

```result
┌─map──────────────────────────┐
│ {'key1':3,'key2':2,'key3':1} │
└──────────────────────────────┘
```

## mapPartialReverseSort

Sorts a specified number of elements in a map in descending order. An optional lambda function can be used to define a custom sorting criterion.

### Syntax

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

### Arguments

- `func`: Lambda function. An optional lambda function to apply to the keys and values of the map for custom sorting.
- `limit`: (U)Int. The number of elements (from 1 up to `limit`) to sort.
- `map`: Map. The map to partially sort.

### Returns

Map. A partially sorted map in descending order.

### Example

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

Result:

```result
┌─mapPartialReverseSort(lambda(tuple(k, v), v), 2, map('k1', 3, 'k2', 1, 'k3', 2))─┐
│ {'k1':3,'k3':2,'k2':1}                                                           │
└──────────────────────────────────────────────────────────────────────────────────┘
```