Map Functions

map

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 type supported as key type of Map.
value_n — The values of the map entries. Any type supported as value type of Map.

Returned value

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

Examples

Query:

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

Result:

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

mapFromArrays

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)'). For example, instead of writing

CAST((['aa', 'bb'], [4, 5]), 'Map(String, UInt32)'), or
CAST([('aa',4), ('bb',5)], 'Map(String, UInt32)')

you can write mapFromArrays(['aa', 'bb'], [4, 5]).

Syntax

mapFromArrays(keys, values)

Alias: MAP_FROM_ARRAYS(keys, values)

Arguments

keys — Array or map of keys to create the map from Array or Map. If keys is an array, we accept Array(Nullable(T)) or Array(LowCardinality(Nullable(T))) as its type as long as it doesn't contain NULL value.
values - Array or map of values to create the map from Array or Map.

Returned value

A map with keys and values constructed from the key array and value array/map.

Example

Query:

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

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.

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

Result:

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

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

Result:

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

extractKeyValuePairs

Converts a string of key-value pairs to a Map(String, String). Parsing is tolerant towards noise (e.g. log files). Key-value pairs in the input string consist of a key, followed by a key-value delimiter, and a value. Key value pairs are separated by a pair delimiter. Keys and values can be quoted.

Syntax

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

Alias:

str_to_map
mapFromString

Arguments

data - String to extract key-value pairs from. String or FixedString.
key_value_delimiter - Single character delimiting keys and values. Defaults to :. String or FixedString.
pair_delimiters - Set of character delimiting pairs. Defaults to , , and ;. String or FixedString.
quoting_character - Single character used as quoting character. Defaults to ". String or 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.

Returned values

A of key-value pairs. Type: Map(String, String)

Examples

Query

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

Result:

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

With a single quote ' as quoting character:

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

Result:

┌─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:

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

Result:

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

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

SELECT
    map('John', '33', 'Paula', '31') AS m,
    toString(m) AS map_serialized,
    extractKeyValuePairs(map_serialized, ':', ',', '\'') AS map_restored
FORMAT Vertical;

Result:

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

extractKeyValuePairsWithEscaping

Same as extractKeyValuePairs but supports escaping.

Supported escape sequences: \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.

Examples

Escape sequences with escape sequence support turned on:

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

Result:

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

mapAdd

Collect all the keys and sum corresponding values.

Syntax

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

Arguments

Arguments are maps or tuples of two arrays, where items in the first array represent keys, and the second array contains values for the each key. All key arrays should have same type, and all value arrays should contain items which are promoted to the one type (Int64, UInt64 or Float64). The common promoted type is used as a type for the result array.

Returned value

Depending on the arguments returns one map or tuple, where the first array contains the sorted keys and the second array contains values.

Example

Query with Map type:

SELECT mapAdd(map(1,1), map(1,1));

Result:

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

Query with a tuple:

SELECT mapAdd(([toUInt8(1), 2], [1, 1]), ([toUInt8(1), 2], [1, 1])) AS res, toTypeName(res) AS type;

Result:

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

mapSubtract

Collect all the keys and subtract corresponding values.

Syntax

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

Arguments

Arguments are maps or tuples of two arrays, where items in the first array represent keys, and the second array contains values for the each key. All key arrays should have same type, and all value arrays should contain items which are promote to the one type (Int64, UInt64 or Float64). The common promoted type is used as a type for the result array.

Returned value

Depending on the arguments returns one map or tuple, where the first array contains the sorted keys and the second array contains values.

Example

Query with Map type:

SELECT mapSubtract(map(1,1), map(1,1));

Result:

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

Query with a tuple map:

SELECT mapSubtract(([toUInt8(1), 2], [toInt32(1), 1]), ([toUInt8(1), 2], [toInt32(2), 1])) AS res, toTypeName(res) AS type;

Result:

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

mapPopulateSeries

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 the keys form a series from the smallest to the largest key (or max argument if it 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])

For array arguments the number of elements in keys and values must be the same for each row.

Arguments

Arguments are Maps or two Arrays, where the first and second array contains keys and values for the each key.

Mapped arrays:

map — Map with integer keys. Map.

keys — Array of keys. Array(Int).
values — Array of values. Array(Int).
max — Maximum key value. Optional. Int8, Int16, Int32, Int64, Int128, Int256.

Returned value

Depending on the arguments a Map or a Tuple of two Arrays: keys in sorted order, and values the corresponding keys.

Example

Query with Map type:

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

Result:

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

Query with mapped arrays:

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

Result:

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

mapKeys

Returns the keys of a given map.

This function can be optimized by enabling setting optimize_functions_to_subcolumns. With enabled setting, the function only reads the keys subcolumn instead the whole map. The query SELECT mapKeys(m) FROM table is transformed to SELECT m.keys FROM table.

Syntax

mapKeys(map)

Arguments

map — Map. Map.

Returned value

Array containing all keys from the map. Array.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

INSERT INTO tab VALUES ({'name':'eleven','age':'11'}), ({'number':'twelve','position':'6.0'});

SELECT mapKeys(a) FROM tab;

Result:

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

mapContains

Returns if a given key is contained in a given map.

Syntax

mapContains(map, key)

Alias: mapContainsKey(map, key)

Arguments

map — Map. Map.
key — Key. Type must match the key type of map.

Returned value

1 if map contains key, 0 if not. UInt8.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

INSERT INTO tab VALUES ({'name':'eleven','age':'11'}), ({'number':'twelve','position':'6.0'});

SELECT mapContains(a, 'name') FROM tab;

Result:

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

mapContainsKeyLike

Syntax

mapContainsKeyLike(map, pattern)

Arguments

map — Map. Map.
pattern - String pattern to match.

Returned value

1 if map contains key like specified pattern, 0 if not.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

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

SELECT mapContainsKeyLike(a, 'a%') FROM tab;

Result:

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

mapExtractKeyLike

Give a map with string keys and a LIKE pattern, this function returns a map with elements where the key matches the pattern.

Syntax

mapExtractKeyLike(map, pattern)

Arguments

map — Map. Map.
pattern - String pattern to match.

Returned value

A map containing elements the key matching the specified pattern. If no elements match the pattern, an empty map is returned.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

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

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

Result:

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

mapValues

Returns the values of a given map.

This function can be optimized by enabling setting optimize_functions_to_subcolumns. With enabled setting, the function only reads the values subcolumn instead the whole map. The query SELECT mapValues(m) FROM table is transformed to SELECT m.values FROM table.

Syntax

mapValues(map)

Arguments

map — Map. Map.

Returned value

Array containing all the values from map. Array.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

INSERT INTO tab VALUES ({'name':'eleven','age':'11'}), ({'number':'twelve','position':'6.0'});

SELECT mapValues(a) FROM tab;

Result:

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

mapContainsValue

Returns if a given key is contained in a given map.

Syntax

mapContainsValue(map, value)

Alias: mapContainsValue(map, value)

Arguments

map — Map. Map.
value — Value. Type must match the value type of map.

Returned value

1 if map contains value, 0 if not. UInt8.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

INSERT INTO tab VALUES ({'name':'eleven','age':'11'}), ({'number':'twelve','position':'6.0'});

SELECT mapContainsValue(a, '11') FROM tab;

Result:

┌─mapContainsValue(a, '11')─┐
│                         1 │
│                         0 │
└───────────────────────────┘

mapContainsValueLike

Syntax

mapContainsValueLike(map, pattern)

Arguments

map — Map. Map.
pattern - String pattern to match.

Returned value

1 if map contains value like specified pattern, 0 if not.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

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

SELECT mapContainsValueLike(a, 'a%') FROM tab;

Result:

┌─mapContainsV⋯ke(a, 'a%')─┐
│                        1 │
│                        0 │
└──────────────────────────┘

mapExtractValueLike

Give 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. Map.
pattern - String pattern to match.

Returned value

A map containing elements the value matching the specified pattern. If no elements match the pattern, an empty map is returned.

Example

Query:

CREATE TABLE tab (a Map(String, String)) ENGINE = Memory;

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

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

Result:

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

mapApply

Applies a function to each element of a map.

Syntax

mapApply(func, map)

Arguments

func — Lambda function.
map — Map.

Returned value

Returns a map obtained from the original map by application of func(map1[i], ..., mapN[i]) for each element.

Example

Query:

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

Result:

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

mapFilter

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

Syntax

mapFilter(func, map)

Arguments

func - Lambda function.
map — Map.

Returned value

Returns a map containing only the elements in map for which func(map1[i], ..., mapN[i]) returns something other than 0.

Example

Query:

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

Result:

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

mapUpdate

Syntax

mapUpdate(map1, map2)

Arguments

map1 Map.
map2 Map.

Returned value

Returns a map1 with values updated of values for the corresponding keys in map2.

Example

Query:

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

Result:

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

mapConcat

Concatenates multiple maps based on the equality of their keys. If elements with the same key exist in more than one input map, all elements are added to the result map, but only the first one is accessible via operator []

Syntax

mapConcat(maps)

Arguments

maps – Arbitrarily many Maps.

Returned value

Returns a map with concatenated maps passed as arguments.

Examples

Query:

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

Result:

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

Query:

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

Result:

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

mapExists([func,], map)

Returns 1 if at least one key-value pair in map exists for which func(key, value) returns something other than 0. Otherwise, it returns 0.

Note

mapExists is a higher-order function. You can pass a lambda function to it as the first argument.

Example

Query:

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

Result:

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

mapAll([func,] map)

Returns 1 if func(key, value) returns something other than 0 for all key-value pairs in map. Otherwise, it returns 0.

Note

Note that the mapAll is a higher-order function. You can pass a lambda function to it as the first argument.

Example

Query:

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

Result:

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

mapSort([func,], map)

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.

Examples

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

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

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

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

For more details see the reference for arraySort function.

mapPartialSort

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 function to apply to the keys and values of the map. Lambda function.
limit – Elements in range [1..limit] are sorted. (U)Int.
map – Map to sort. Map.

Returned value

Partially sorted map. Map.

Example

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

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

mapReverseSort([func,], map)

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.

Examples

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

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

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

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

For more details see function arrayReverseSort.

mapPartialReverseSort

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 function to apply to the keys and values of the map. Lambda function.
limit – Elements in range [1..limit] are sorted. (U)Int.
map – Map to sort. Map.

Returned value

Partially sorted map. Map.

Example

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

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

extractKeyValuePairs

Introduced in: v

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 or FixedString.
key_value_delimiter - Character to be used as delimiter between the key and the value. Defaults to :. String or FixedString.
pair_delimiters - Set of character to be used as delimiters between pairs. Defaults to \space, , and ;. String or FixedString.
quoting_character - Character to be used as quoting character. Defaults to ". String or 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

Aliases: str_to_map, mapFromString

Arguments

None.

Returned value

Examples

extractKeyValuePairsWithEscaping

Introduced in: v

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

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

Arguments

None.

Returned value

Examples

map

Introduced in: v21.1

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
value_n — The values of the map entries. Any

Returned value

Returns a map containing key:value pairs. Map(Any, Any)

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

Introduced in: v20.7

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) or Tuple(Array(T), Array(T))

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) or Tuple(Array(T), Array(T))

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

Introduced in: v23.4

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.

Syntax

mapAll([func,] map)

Arguments

func — Lambda function. Lambda function
map — Map to check. Map(K, V)

Returned value

Returns 1 if all key-value pairs satisfy the condition, 0 otherwise. UInt8

Examples

Usage example

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

mapApply

Introduced in: v22.3

Applies a function to each element of a map.

Syntax

mapApply(func, map)

Arguments

func — Lambda function. Lambda function
map — Map to apply function to. Map(K, V)

Returned value

Returns a new map obtained from the original map by application of func for each element. Map(K, V)

Examples

Usage example

SELECT mapApply((k, v) -> (k, v * 2), map('k1', 1, 'k2', 2))

{'k1':2,'k2':4}

mapConcat

Introduced in: v23.4

Syntax

mapConcat(maps)

Arguments

maps — Arbitrarily many maps. Map

Returned value

Returns a map with concatenated maps passed as arguments. Map

Examples

Usage example

SELECT mapConcat(map('k1', 'v1'), map('k2', 'v2'))

{'k1':'v1','k2':'v2'}

mapContainsKey

Introduced in: v21.2

Determines if a key is contained in a map.

Syntax

mapContains(map, key)

Aliases: mapContains

Arguments

map — Map to search in. Map(K, V)
key — Key to search for. Type must match the key type of the map. Any

Returned value

Returns 1 if map contains key, 0 if not. UInt8

Examples

Usage example

SELECT mapContainsKey(map('k1', 'v1', 'k2', 'v2'), 'k1')

mapContainsKeyLike

Introduced in: v23.4

Checks whether map contains key LIKE specified pattern.

Syntax

mapContainsKeyLike(map, pattern)

Arguments

map — Map to search in. Map(K, V)
pattern — Pattern to match keys against. const String

Returned value

Returns 1 if map contains a key matching pattern, 0 otherwise. UInt8

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 mapContainsKeyLike(a, 'a%') FROM tab;

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

mapContainsValue

Introduced in: v25.6

Determines if a value is contained in a map.

Syntax

mapContainsValue(map, value)

Arguments

map — Map to search in. Map(K, V)
value — Value to search for. Type must match the value type of map. Any

Returned value

Returns 1 if the map contains the value, 0 if not. UInt8

Examples

Usage example

SELECT mapContainsValue(map('k1', 'v1', 'k2', 'v2'), 'v1')

mapContainsValueLike

Introduced in: v25.5

Checks whether a map contains a value LIKE the specified pattern.

Syntax

mapContainsValueLike(map, pattern)

Arguments

map — Map to search in. Map(K, V)
pattern — Pattern to match values against. const String

Returned value

Returns 1 if map contains a value matching pattern, 0 otherwise. UInt8

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 mapContainsValueLike(a, 'a%') FROM tab;

┌─mapContainsV⋯ke(a, 'a%')─┐
│                        1 │
│                        0 │
└──────────────────────────┘

mapExists

Introduced in: v23.4

Tests whether a condition holds for at least one key-value pair in a map. mapExists is a higher-order function. You can pass a lambda function to it as the first argument.

Syntax

mapExists([func,] map)

Arguments

func — Optional. Lambda function. Lambda function
map — Map to check. Map(K, V)

Returned value

Returns 1 if at least one key-value pair satisfies the condition, 0 otherwise. UInt8

Examples

Usage example

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

mapExtractKeyLike

Introduced in: v23.4

Give a map with string keys and a LIKE pattern, this function returns a map with elements where the key matches the pattern.

Syntax

mapExtractKeyLike(map, pattern)

Arguments

map — Map to extract from. Map(K, V)
pattern — Pattern to match keys against. const String

Returned value

Returns a map containing elements the key matching the specified pattern. If no elements match the pattern, an empty map is returned. Map(K, V)

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 mapExtractKeyLike(a, 'a%') FROM tab;

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

mapExtractValueLike

Introduced in: v25.5

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)
pattern — Pattern to match values against. const 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)

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

Introduced in: v22.3

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

Syntax

mapFilter(func, map)

Arguments

func — Lambda function. Lambda function
map — Map to filter. Map(K, V)

Returned value

Returns a map containing only the elements in the map for which func returns something other than 0. Map(K, V)

Examples

Usage example

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

{'k2':2}

mapFromArrays

Introduced in: v23.3

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 or Map
values — Array or map of values to create the map from. Array or Map

Returned value

Returns a map with keys and values constructed from the key array and value array/map. 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

Introduced in: v21.2

Returns the keys of a given map. This function can be optimized by enabling setting 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)

Returned value

Returns array containing all keys from the map. Array(T)

Examples

Usage example

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

['k1','k2']

mapPartialReverseSort

Introduced in: v23.4

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
limit — Elements in the range [1..limit] are sorted. (U)Int*
map — Map to sort. Map(K, V)

Returned value

Returns a partially sorted map in descending order. Map(K, V)

Examples

Usage example

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

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

mapPartialSort

Introduced in: v23.4

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
limit — Elements in the range [1..limit] are sorted. (U)Int*
map — Map to sort. Map(K, V)

Returned value

Returns a partially sorted map. Map(K, V)

Examples

Usage example

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

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

mapPopulateSeries

Introduced in: v20.10

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)
keys — Array of keys. Array(T)
values — Array of values. Array(T)
max — Optional. Maximum key value. Int8 or Int16 or Int32 or Int64 or Int128 or Int256

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) or Tuple(Array(UInt*), Array(Any))

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

Introduced in: v23.4

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
map — Map to sort. Map(K, V)

Returned value

Returns a map sorted in descending order. Map(K, V)

Examples

Usage example

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

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

mapSort

Introduced in: v23.4

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
map — Map to sort. Map(K, V)

Returned value

Returns a map sorted in ascending order. Map(K, V)

Examples

Usage example

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

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

mapSubtract

Introduced in: v20.7

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) or Tuple(Array(T), Array(T))

Returned value

Returns one map or tuple, where the first array contains the sorted keys and the second array contains values. Map(K, V) or Tuple(Array(T), Array(T))

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

Introduced in: v22.3

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)
map2 — The map to use for updating. Map(K, V)

Returned value

Returns map1 with values updated from values for the corresponding keys in map2. Map(K, V)

Examples

Basic usage

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

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

mapValues

Introduced in: v21.2

Returns the values of a given map. This function can be optimized by enabling setting 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)

Returned value

Returns an array containing all the values from the map. Array(T)

Examples

Usage example

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

['v1','v2']

map​

mapFromArrays​

extractKeyValuePairs​

extractKeyValuePairsWithEscaping​

mapAdd​

mapSubtract​

mapPopulateSeries​

mapKeys​

mapContains​

mapContainsKeyLike​

mapExtractKeyLike​

mapValues​

mapContainsValue​

mapContainsValueLike​

mapExtractValueLike​

mapApply​

mapFilter​

mapUpdate​

mapConcat​

mapExists([func,], map)​

mapAll([func,] map)​

mapSort([func,], map)​

mapPartialSort​

mapReverseSort([func,], map)​

mapPartialReverseSort​

extractKeyValuePairs​

extractKeyValuePairsWithEscaping​

map​

mapAdd​

mapAll​

mapApply​

mapConcat​

mapContainsKey​

mapContainsKeyLike​

mapContainsValue​

mapContainsValueLike​

mapExists​

mapExtractKeyLike​

mapExtractValueLike​

mapFilter​

mapFromArrays​

mapKeys​

mapPartialReverseSort​

mapPartialSort​

mapPopulateSeries​

mapReverseSort​

mapSort​

mapSubtract​

mapUpdate​

mapValues​

map

mapFromArrays

extractKeyValuePairs

extractKeyValuePairsWithEscaping

mapAdd

mapSubtract

mapPopulateSeries

mapKeys

mapContains

mapContainsKeyLike

mapExtractKeyLike

mapValues

mapContainsValue

mapContainsValueLike

mapExtractValueLike

mapApply

mapFilter

mapUpdate

mapConcat

mapExists([func,], map)

mapAll([func,] map)

mapSort([func,], map)

mapPartialSort

mapReverseSort([func,], map)

mapPartialReverseSort

extractKeyValuePairs

extractKeyValuePairsWithEscaping

map

mapAdd

mapAll

mapApply

mapConcat

mapContainsKey

mapContainsKeyLike

mapContainsValue

mapContainsValueLike

mapExists

mapExtractKeyLike

mapExtractValueLike

mapFilter

mapFromArrays

mapKeys

mapPartialReverseSort

mapPartialSort

mapPopulateSeries

mapReverseSort

mapSort

mapSubtract

mapUpdate

mapValues