Map functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/tuple-map-functions.md)

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

Introduced in: v23.4.0

Extracts key-value pairs from any string. The string does not need to be 100% structured in a key value pair format;

It can contain noise (e.g. log files). The key-value pair format to be interpreted should be specified via function arguments.

A key-value pair consists of a key followed by a `key_value_delimiter` and a value. Quoted keys and values are also supported. Key value pairs must be separated by pair delimiters.

**Syntax**

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

**Arguments**

- `data` - String to extract key-value pairs from. [String](/docs/sql-reference/data-types/string) or [FixedString](/docs/sql-reference/data-types/fixedstring).
- `key_value_delimiter` - Character to be used as delimiter between the key and the value. Defaults to `:`. [String](/docs/sql-reference/data-types/string) or [FixedString](/docs/sql-reference/data-types/fixedstring).
- `pair_delimiters` - Set of character to be used as delimiters between pairs. Defaults to `\space`, `,` and `;`. [String](/docs/sql-reference/data-types/string) or [FixedString](/docs/sql-reference/data-types/fixedstring).
- `quoting_character` - Character to be used as quoting character. Defaults to `"`. [String](/docs/sql-reference/data-types/string) or [FixedString](/docs/sql-reference/data-types/fixedstring).
- `unexpected_quoting_character_strategy` - Strategy to handle quoting characters in unexpected places during `read_key` and `read_value` phase. Possible values: `invalid`, `accept` and `promote`. Invalid will discard key/value and transition back to `WAITING_KEY` state. Accept will treat it as a normal character. Promote will transition to `READ_QUOTED_{KEY/VALUE}` state and start from next character. The default value is `INVALID`

**Returned values**

- The extracted key-value pairs in a Map(String, String).

**Examples**

Query:

**Simple case**

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

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

Query id: f9e0ca6f-3178-4ee2-aa2c-a5517abb9cee

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

**Single quote as quoting character**

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

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

Query id: 0e22bf6b-9844-414a-99dc-32bf647abd5e

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

unexpected\_quoting\_character\_strategy examples:

unexpected\_quoting\_character\_strategy=invalid

```
SELECT extractKeyValuePairs('name"abc:5', ':', ' ,;', '\"', 'INVALID') as kv;
```

```
┌─kv────────────────┐
│ {'abc':'5'}  │
└───────────────────┘
```

```
SELECT extractKeyValuePairs('name"abc":5', ':', ' ,;', '\"', 'INVALID') as kv;
```

```
┌─kv──┐
│ {}  │
└─────┘
```

unexpected\_quoting\_character\_strategy=accept

```
SELECT extractKeyValuePairs('name"abc:5', ':', ' ,;', '\"', 'ACCEPT') as kv;
```

```
┌─kv────────────────┐
│ {'name"abc':'5'}  │
└───────────────────┘
```

```
SELECT extractKeyValuePairs('name"abc":5', ':', ' ,;', '\"', 'ACCEPT') as kv;
```

```
┌─kv─────────────────┐
│ {'name"abc"':'5'}  │
└────────────────────┘
```

unexpected\_quoting\_character\_strategy=promote

```
SELECT extractKeyValuePairs('name"abc:5', ':', ' ,;', '\"', 'PROMOTE') as kv;
```

```
┌─kv──┐
│ {}  │
└─────┘
```

```
SELECT extractKeyValuePairs('name"abc":5', ':', ' ,;', '\"', 'PROMOTE') as kv;
```

```
┌─kv───────────┐
│ {'abc':'5'}  │
└──────────────┘
```

**Escape sequences without escape sequences support**

```
arthur :) select extractKeyValuePairs('age:a\\x0A\\n\\0') as kv

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

Query id: e9fd26ee-b41f-4a11-b17f-25af6fd5d356

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

**Syntax**

```
extractKeyValuePairs(input)
```

**Aliases**: `str_to_map`, `mapFromString`

**Arguments**

- None.

**Returned value**

**Examples**

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

Introduced in: v23.4.0

Same as `extractKeyValuePairs` but with escaping support.

Escape sequences supported: `\x`, `\N`, `\a`, `\b`, `\e`, `\f`, `\n`, `\r`, `\t`, `\v` and `\0`.
Non standard escape sequences are returned as it is (including the backslash) unless they are one of the following:
`\\`, `'`, `"`, `backtick`, `/`, `=` or ASCII control characters (`c <= 31`).

This function will satisfy the use case where pre-escaping and post-escaping are not suitable. For instance, consider the following
input string: `a: "aaaa\"bbb"`. The expected output is: `a: aaaa\"bbbb`.

- Pre-escaping: Pre-escaping it will output: `a: "aaaa"bbb"` and `extractKeyValuePairs` will then output: `a: aaaa`
- Post-escaping: `extractKeyValuePairs` will output `a: aaaa\` and post-escaping will keep it as it is.

Leading escape sequences will be skipped in keys and will be considered invalid for values.

**Escape sequences with escape sequence support turned on**

```
arthur :) select extractKeyValuePairsWithEscaping('age:a\\x0A\\n\\0') as kv

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

Query id: 44c114f0-5658-4c75-ab87-4574de3a1645

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

**Syntax**

```
extractKeyValuePairsWithEscaping(input)
```

**Arguments**

- None.

**Returned value**

**Examples**

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

Introduced in: v21.1.0

Creates a value of type `Map(key, value)` from key-value pairs.

**Syntax**

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

**Arguments**

- `key_n` — The keys of the map entries. [`Any`](/docs/sql-reference/data-types)
- `value_n` — The values of the map entries. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns a map containing key:value pairs. [`Map(Any, Any)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

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

```
{'key1':0,'key2':0}
{'key1':1,'key2':2}
{'key1':2,'key2':4}
```

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

Introduced in: v20.7.0

Collect all the keys and sum corresponding values.

**Syntax**

```
mapAdd(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 a map or returns a 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 mapAdd(map(1, 1), map(1, 1))
```

```
{1:2}
```

**With tuple**

```
SELECT mapAdd(([toUInt8(1), 2], [1, 1]), ([toUInt8(1), 2], [1, 1]))
```

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

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

Introduced in: v23.4.0

Tests whether a condition holds for all key-value pairs in a map.
`mapAll` is a higher-order function.
You can pass a lambda function to it as the first argument.