View source for Relational Data - Functions - Tuple Map Functions

== map ==

Creates a value of type [[Relational_Data_-_Data_Types_-_Map|Map(key, value)]] from key-value pairs.

'''Syntax'''

<syntaxhighlight lang="sql">map(key1, value1[, key2, value2, ...])</syntaxhighlight>
'''Arguments'''

* <code>key_n</code> — The keys of the map entries. Any type supported as key type of [[Relational_Data_-_Data_Types_-_Map|Map]].
* <code>value_n</code> — The values of the map entries. Any type supported as value type of [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

* A map containing <code>key:value</code> pairs. [[Relational_Data_-_Data_Types_-_Map|Map(key, value)]].

'''Examples'''

Query:

<syntaxhighlight lang="sql">SELECT map('key1', number, 'key2', number * 2) FROM numbers(3);</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─map('key1', number, 'key2', multiply(number, 2))─┐
│ {'key1':0,'key2':0}                              │
│ {'key1':1,'key2':2}                              │
│ {'key1':2,'key2':4}                              │
└──────────────────────────────────────────────────┘</syntaxhighlight>
<span id="mapfromarrays"></span>
== 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 <code>CAST([...], 'Map(key_type, value_type)')</code>. For example, instead of writing - <code>CAST((['aa', 'bb'], [4, 5]), 'Map(String, UInt32)')</code>, or - <code>CAST([('aa',4), ('bb',5)], 'Map(String, UInt32)')</code>

you can write <code>mapFromArrays(['aa', 'bb'], [4, 5])</code>.

'''Syntax'''

<syntaxhighlight lang="sql">mapFromArrays(keys, values)</syntaxhighlight>
Alias: <code>MAP_FROM_ARRAYS(keys, values)</code>

'''Arguments'''

* <code>keys</code> — Array or map of keys to create the map from [[Relational_Data_-_Data_Types_-_Array|Array]] or [[Relational_Data_-_Data_Types_-_Map|Map]]. If <code>keys</code> is an array, we accept <code>Array(Nullable(T))</code> or <code>Array(LowCardinality(Nullable(T)))</code> as its type as long as it doesn’t contain NULL value.
* <code>values</code> - Array or map of values to create the map from [[Relational_Data_-_Data_Types_-_Array|Array]] or [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

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

'''Example'''

Query:

<syntaxhighlight lang="sql">select mapFromArrays(['a', 'b', 'c'], [1, 2, 3])</syntaxhighlight>
Result:

<pre>┌─mapFromArrays(['a', 'b', 'c'], [1, 2, 3])─┐
│ {'a':1,'b':2,'c':3}                       │
└───────────────────────────────────────────┘</pre>
<code>mapFromArrays</code> also accepts arguments of type [[Relational_Data_-_Data_Types_-_Map|Map]]. These are casted to array of tuples during execution.

<syntaxhighlight lang="sql">SELECT mapFromArrays([1, 2, 3], map('a', 1, 'b', 2, 'c', 3))</syntaxhighlight>
Result:

<pre>┌─mapFromArrays([1, 2, 3], map('a', 1, 'b', 2, 'c', 3))─┐
│ {1:('a',1),2:('b',2),3:('c',3)}                       │
└───────────────────────────────────────────────────────┘</pre>
<syntaxhighlight lang="sql">SELECT mapFromArrays(map('a', 1, 'b', 2, 'c', 3), [1, 2, 3])</syntaxhighlight>
Result:

<pre>┌─mapFromArrays(map('a', 1, 'b', 2, 'c', 3), [1, 2, 3])─┐
│ {('a',1):1,('b',2):2,('c',3):3}                       │
└───────────────────────────────────────────────────────┘</pre>
<span id="extractkeyvaluepairs"></span>
== extractKeyValuePairs ==

Converts a string of key-value pairs to a [[Relational_Data_-_Data_Types_-_Map|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'''

<syntaxhighlight lang="sql">extractKeyValuePairs(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]])</syntaxhighlight>
Alias: - <code>str_to_map</code> - <code>mapFromString</code>

'''Arguments'''

* <code>data</code> - String to extract key-value pairs from. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].
* <code>key_value_delimiter</code> - Single character delimiting keys and values. Defaults to <code>:</code>. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].
* <code>pair_delimiters</code> - Set of character delimiting pairs. Defaults to <code></code>, <code>,</code> and <code>;</code>. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].
* <code>quoting_character</code> - Single character used as quoting character. Defaults to <code>&quot;</code>. [[Relational_Data_-_Data_Types_-_String|String]] or [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].

'''Returned values'''

* A of key-value pairs. Type: [[Relational_Data_-_Data_Types_-_Map|Map(String, String)]]

'''Examples'''

Query

<syntaxhighlight lang="sql">SELECT extractKeyValuePairs('name:neymar, age:31 team:psg,nationality:brazil') as kv</syntaxhighlight>
Result:

<pre class="result:">┌─kv──────────────────────────────────────────────────────────────────────┐
│ {'name':'neymar','age':'31','team':'psg','nationality':'brazil'}        │
└─────────────────────────────────────────────────────────────────────────┘</pre>
With a single quote <code>'</code> as quoting character:

<syntaxhighlight lang="sql">SELECT extractKeyValuePairs('name:\'neymar\';\'age\':31;team:psg;nationality:brazil,last_key:last_value', ':', ';,', '\'') as kv</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─kv───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {'name':'neymar','age':'31','team':'psg','nationality':'brazil','last_key':'last_value'}                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘</syntaxhighlight>
Escape sequences without escape sequences support:

<syntaxhighlight lang="sql">SELECT extractKeyValuePairs('age:a\\x0A\\n\\0') AS kv</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─kv─────────────────────┐
│ {'age':'a\\x0A\\n\\0'} │
└────────────────────────┘</syntaxhighlight>
To restore a map string key-value pairs serialized with <code>toString</code>:

<syntaxhighlight lang="sql">SELECT
    map('John', '33', 'Paula', '31') AS m,
    toString(m) as map_serialized,
    extractKeyValuePairs(map_serialized, ':', ',', '\'') AS map_restored
FORMAT Vertical;</syntaxhighlight>
Result:

<pre>Row 1:
──────
m:              {'John':'33','Paula':'31'}
map_serialized: {'John':'33','Paula':'31'}
map_restored:   {'John':'33','Paula':'31'}</pre>
<span id="extractkeyvaluepairswithescaping"></span>
== extractKeyValuePairsWithEscaping ==

Same as <code>extractKeyValuePairs</code> but supports escaping.

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

This function will satisfy the use case where pre-escaping and post-escaping are not suitable. For instance, consider the following input string: <code>a: &quot;aaaa\&quot;bbb&quot;</code>. The expected output is: <code>a: aaaa\&quot;bbbb</code>. - Pre-escaping: Pre-escaping it will output: <code>a: &quot;aaaa&quot;bbb&quot;</code> and <code>extractKeyValuePairs</code> will then output: <code>a: aaaa</code> - Post-escaping: <code>extractKeyValuePairs</code> will output <code>a: aaaa\</code> 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:

<syntaxhighlight lang="sql">SELECT extractKeyValuePairsWithEscaping('age:a\\x0A\\n\\0') AS kv</syntaxhighlight>
Result:

<pre class="result">┌─kv────────────────┐
│ {'age':'a\n\n\0'} │
└───────────────────┘</pre>
<span id="mapadd"></span>
== mapAdd ==

Collect all the keys and sum corresponding values.

'''Syntax'''

<syntaxhighlight lang="sql">mapAdd(arg1, arg2 [, ...])</syntaxhighlight>
'''Arguments'''

Arguments are [[Relational_Data_-_Data_Types_-_Map|maps]] or [[Relational_Data_-_Data_Types_-_Tuple#tuplet1-t2|tuples]] of two [[Relational_Data_-_Data_Types_-_Array#data-type-array|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 ([[Relational_Data_-_Data_Types_-_Int_Uint#int-ranges|Int64]], [[Relational_Data_-_Data_Types_-_Int_Uint#uint-ranges|UInt64]] or [[Relational_Data_-_Data_Types_-_Float#float32-float64|Float64]]). The common promoted type is used as a type for the result array.

'''Returned value'''

* Depending on the arguments returns one [[Relational_Data_-_Data_Types_-_Map|map]] or [[Relational_Data_-_Data_Types_-_Tuple#tuplet1-t2|tuple]], where the first array contains the sorted keys and the second array contains values.

'''Example'''

Query with <code>Map</code> type:

<syntaxhighlight lang="sql">SELECT mapAdd(map(1,1), map(1,1));</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapAdd(map(1, 1), map(1, 1))─┐
│ {1:2}                        │
└──────────────────────────────┘</syntaxhighlight>
Query with a tuple:

<syntaxhighlight lang="sql">SELECT mapAdd(([toUInt8(1), 2], [1, 1]), ([toUInt8(1), 2], [1, 1])) as res, toTypeName(res) as type;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─res───────────┬─type───────────────────────────────┐
│ ([1,2],[2,2]) │ Tuple(Array(UInt8), Array(UInt64)) │
└───────────────┴────────────────────────────────────┘</syntaxhighlight>
<span id="mapsubtract"></span>
== mapSubtract ==

Collect all the keys and subtract corresponding values.

'''Syntax'''

<syntaxhighlight lang="sql">mapSubtract(Tuple(Array, Array), Tuple(Array, Array) [, ...])</syntaxhighlight>
'''Arguments'''

Arguments are [[Relational_Data_-_Data_Types_-_Map|maps]] or [[Relational_Data_-_Data_Types_-_Tuple#tuplet1-t2|tuples]] of two [[Relational_Data_-_Data_Types_-_Array#data-type-array|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 ([[Relational_Data_-_Data_Types_-_Int_Uint#int-ranges|Int64]], [[Relational_Data_-_Data_Types_-_Int_Uint#uint-ranges|UInt64]] or [[Relational_Data_-_Data_Types_-_Float#float32-float64|Float64]]). The common promoted type is used as a type for the result array.

'''Returned value'''

* Depending on the arguments returns one [[Relational_Data_-_Data_Types_-_Map|map]] or [[Relational_Data_-_Data_Types_-_Tuple#tuplet1-t2|tuple]], where the first array contains the sorted keys and the second array contains values.

'''Example'''

Query with <code>Map</code> type:

<syntaxhighlight lang="sql">SELECT mapSubtract(map(1,1), map(1,1));</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapSubtract(map(1, 1), map(1, 1))─┐
│ {1:0}                             │
└───────────────────────────────────┘</syntaxhighlight>
Query with a tuple map:

<syntaxhighlight lang="sql">SELECT mapSubtract(([toUInt8(1), 2], [toInt32(1), 1]), ([toUInt8(1), 2], [toInt32(2), 1])) as res, toTypeName(res) as type;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─res────────────┬─type──────────────────────────────┐
│ ([1,2],[-1,0]) │ Tuple(Array(UInt8), Array(Int64)) │
└────────────────┴───────────────────────────────────┘</syntaxhighlight>
<span id="mappopulateseries"></span>
== 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 <code>max</code> 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'''

<syntaxhighlight lang="sql">mapPopulateSeries(map[, max])
mapPopulateSeries(keys, values[, max])</syntaxhighlight>
For array arguments the number of elements in <code>keys</code> and <code>values</code> must be the same for each row.

'''Arguments'''

Arguments are [[Relational_Data_-_Data_Types_-_Map|Maps]] or two [[Relational_Data_-_Data_Types_-_Array#data-type-array|Arrays]], where the first and second array contains keys and values for the each key.

Mapped arrays:

* <code>map</code> — Map with integer keys. [[Relational_Data_-_Data_Types_-_Map|Map]].

or

* <code>keys</code> — Array of keys. [[Relational_Data_-_Data_Types_-_Array#data-type-array|Array]]([[Relational_Data_-_Data_Types_-_Int_Uint#uint-ranges|Int]]).
* <code>values</code> — Array of values. [[Relational_Data_-_Data_Types_-_Array#data-type-array|Array]]([[Relational_Data_-_Data_Types_-_Int_Uint#uint-ranges|Int]]).
* <code>max</code> — Maximum key value. Optional. [[Relational_Data_-_Data_Types_-_Int_Uint#int-ranges|Int8, Int16, Int32, Int64, Int128, Int256]].

'''Returned value'''

* Depending on the arguments a [[Relational_Data_-_Data_Types_-_Map|Map]] or a [[Relational_Data_-_Data_Types_-_Tuple#tuplet1-t2|Tuple]] of two [[Relational_Data_-_Data_Types_-_Array#data-type-array|Arrays]]: keys in sorted order, and values the corresponding keys.

'''Example'''

Query with <code>Map</code> type:

<syntaxhighlight lang="sql">SELECT mapPopulateSeries(map(1, 10, 5, 20), 6);</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapPopulateSeries(map(1, 10, 5, 20), 6)─┐
│ {1:10,2:0,3:0,4:0,5:20,6:0}             │
└─────────────────────────────────────────┘</syntaxhighlight>
Query with mapped arrays:

<syntaxhighlight lang="sql">SELECT mapPopulateSeries([1,2,4], [11,22,44], 5) AS res, toTypeName(res) AS type;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─res──────────────────────────┬─type──────────────────────────────┐
│ ([1,2,3,4,5],[11,22,0,44,0]) │ Tuple(Array(UInt8), Array(UInt8)) │
└──────────────────────────────┴───────────────────────────────────┘</syntaxhighlight>
<span id="mapcontains"></span>
== mapContains ==

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

'''Syntax'''

<syntaxhighlight lang="sql">mapContains(map, key)</syntaxhighlight>
'''Arguments'''

* <code>map</code> — Map. [[Relational_Data_-_Data_Types_-_Map|Map]].
* <code>key</code> — Key. Type must match the key type of <code>map</code>.

'''Returned value'''

* <code>1</code> if <code>map</code> contains <code>key</code>, <code>0</code> if not. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt8]].

'''Example'''

Query:

<syntaxhighlight lang="sql">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;
</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapContains(a, 'name')─┐
│                      1 │
│                      0 │
└────────────────────────┘</syntaxhighlight>
<span id="mapkeys"></span>
== mapKeys ==

Returns the keys of a given map.

This function can be optimized by enabling setting [https://clickhouse.com/docs/en/operations/settings/settings#optimize-functions-to-subcolumns optimize_functions_to_subcolumns]. With enabled setting, the function only reads the [[Relational_Data_-_Data_Types_-_Map#map-subcolumns|keys]] subcolumn instead the whole map. The query <code>SELECT mapKeys(m) FROM table</code> is transformed to <code>SELECT m.keys FROM table</code>.

'''Syntax'''

<syntaxhighlight lang="sql">mapKeys(map)</syntaxhighlight>
'''Arguments'''

* <code>map</code> — Map. [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

* Array containing all keys from the <code>map</code>. [[Relational_Data_-_Data_Types_-_Array|Array]].

'''Example'''

Query:

<syntaxhighlight lang="sql">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;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapKeys(a)────────────┐
│ ['name','age']        │
│ ['number','position'] │
└───────────────────────┘</syntaxhighlight>
<span id="mapvalues"></span>
== mapValues ==

Returns the values of a given map.

This function can be optimized by enabling setting [https://clickhouse.com/docs/en/operations/settings/settings#optimize-functions-to-subcolumns optimize_functions_to_subcolumns]. With enabled setting, the function only reads the [[Relational_Data_-_Data_Types_-_Map#map-subcolumns|values]] subcolumn instead the whole map. The query <code>SELECT mapValues(m) FROM table</code> is transformed to <code>SELECT m.values FROM table</code>.

'''Syntax'''

<syntaxhighlight lang="sql">mapValues(map)</syntaxhighlight>
'''Arguments'''

* <code>map</code> — Map. [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

* Array containing all the values from <code>map</code>. [[Relational_Data_-_Data_Types_-_Array|Array]].

'''Example'''

Query:

<syntaxhighlight lang="sql">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;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapValues(a)─────┐
│ ['eleven','11']  │
│ ['twelve','6.0'] │
└──────────────────┘</syntaxhighlight>
<span id="mapcontainskeylike"></span>
== mapContainsKeyLike ==

'''Syntax'''

<syntaxhighlight lang="sql">mapContainsKeyLike(map, pattern)</syntaxhighlight>
'''Arguments''' - <code>map</code> — Map. [[Relational_Data_-_Data_Types_-_Map|Map]]. - <code>pattern</code> - String pattern to match.

'''Returned value'''

* <code>1</code> if <code>map</code> contains <code>key</code> like specified pattern, <code>0</code> if not.

'''Example'''

Query:

<syntaxhighlight lang="sql">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;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapContainsKeyLike(a, 'a%')─┐
│                           1 │
│                           0 │
└─────────────────────────────┘</syntaxhighlight>
<span id="mapextractkeylike"></span>
== 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'''

<syntaxhighlight lang="sql">mapExtractKeyLike(map, pattern)</syntaxhighlight>
'''Arguments'''

* <code>map</code> — Map. [[Relational_Data_-_Data_Types_-_Map|Map]].
* <code>pattern</code> - 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:

<syntaxhighlight lang="sql">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;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─mapExtractKeyLike(a, 'a%')─┐
│ {'abc':'abc'}              │
│ {}                         │
└────────────────────────────┘</syntaxhighlight>
<span id="mapapply"></span>
== mapApply ==

Applies a function to each element of a map.

'''Syntax'''

<syntaxhighlight lang="sql">mapApply(func, map)</syntaxhighlight>
'''Arguments'''

* <code>func</code> — [[Relational_Data_-_Functions#higher-order-functions---operator-and-lambdaparams-expr-function|Lambda function]].
* <code>map</code> — [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

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

'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT mapApply((k, v) -> (k, v * 10), _map) AS r
FROM
(
    SELECT map('key1', number, 'key2', number * 2) AS _map
    FROM numbers(3)
)</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─r─────────────────────┐
│ {'key1':0,'key2':0}   │
│ {'key1':10,'key2':20} │
│ {'key1':20,'key2':40} │
└───────────────────────┘</syntaxhighlight>
<span id="mapfilter"></span>
== mapFilter ==

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

'''Syntax'''

<syntaxhighlight lang="sql">mapFilter(func, map)</syntaxhighlight>
'''Arguments'''

* <code>func</code> - [[Relational_Data_-_Functions#higher-order-functions---operator-and-lambdaparams-expr-function|Lambda function]].
* <code>map</code> — [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

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

'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT mapFilter((k, v) -> ((v % 2) = 0), _map) AS r
FROM
(
    SELECT map('key1', number, 'key2', number * 2) AS _map
    FROM numbers(3)
)</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─r───────────────────┐
│ {'key1':0,'key2':0} │
│ {'key2':2}          │
│ {'key1':2,'key2':4} │
└─────────────────────┘</syntaxhighlight>
<span id="mapupdate"></span>
== mapUpdate ==

'''Syntax'''

<syntaxhighlight lang="sql">mapUpdate(map1, map2)</syntaxhighlight>
'''Arguments'''

* <code>map1</code> [[Relational_Data_-_Data_Types_-_Map|Map]].
* <code>map2</code> [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

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

'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT mapUpdate(map('key1', 0, 'key3', 0), map('key1', 10, 'key2', 10)) AS map;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─map────────────────────────────┐
│ {'key3':0,'key1':10,'key2':10} │
└────────────────────────────────┘</syntaxhighlight>
<span id="mapconcat"></span>
== 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 <code>[]</code>

'''Syntax'''

<syntaxhighlight lang="sql">mapConcat(maps)</syntaxhighlight>
'''Arguments'''

* <code>maps</code> – Arbitrarily many [[Relational_Data_-_Data_Types_-_Map|Maps]].

'''Returned value'''

* Returns a map with concatenated maps passed as arguments.

'''Examples'''

Query:

<syntaxhighlight lang="sql">SELECT mapConcat(map('key1', 1, 'key3', 3), map('key2', 2)) AS map;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─map──────────────────────────┐
│ {'key1':1,'key3':3,'key2':2} │
└──────────────────────────────┘</syntaxhighlight>
Query:

<syntaxhighlight lang="sql">SELECT mapConcat(map('key1', 1, 'key2', 2), map('key1', 3)) AS map, map['key1'];</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─map──────────────────────────┬─elem─┐
│ {'key1':1,'key2':2,'key1':3} │    1 │
└──────────────────────────────┴──────┘</syntaxhighlight>
<span id="mapexistsfunc-map"></span>
== mapExists([func,], map) ==

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

<div class="note">

<code>mapExists</code> is a [[Relational_Data_-_Functions#higher-order-functions|higher-order function]]. You can pass a lambda function to it as the first argument.


</div>
'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT mapExists((k, v) -> (v = 1), map('k1', 1, 'k2', 2)) AS res</syntaxhighlight>
Result:

<pre>┌─res─┐
│   1 │
└─────┘</pre>
<span id="mapallfunc-map"></span>
== mapAll([func,] map) ==

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

<div class="note">

Note that the <code>mapAll</code> is a [[Relational_Data_-_Functions#higher-order-functions|higher-order function]]. You can pass a lambda function to it as the first argument.


</div>
'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT mapAll((k, v) -> (v = 1), map('k1', 1, 'k2', 2)) AS res</syntaxhighlight>
Result:

<pre>┌─res─┐
│   0 │
└─────┘</pre>
<span id="mapsortfunc-map"></span>
== mapSort([func,], map) ==

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

'''Examples'''

<syntaxhighlight lang="sql">SELECT mapSort(map('key2', 2, 'key3', 1, 'key1', 3)) AS map;</syntaxhighlight>
<syntaxhighlight lang="text">┌─map──────────────────────────┐
│ {'key1':3,'key2':2,'key3':1} │
└──────────────────────────────┘</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT mapSort((k, v) -> v, map('key2', 2, 'key3', 1, 'key1', 3)) AS map;</syntaxhighlight>
<syntaxhighlight lang="text">┌─map──────────────────────────┐
│ {'key3':1,'key2':2,'key1':3} │
└──────────────────────────────┘</syntaxhighlight>
For more details see the [[Relational_Data_-_Functions_-_Array_Functions#array_functions-sort|reference]] for <code>arraySort</code> function.

<span id="mappartialsort"></span>
== mapPartialSort ==

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

'''Syntax'''

<syntaxhighlight lang="sql">mapPartialSort([func,] limit, map)</syntaxhighlight>
'''Arguments'''

* <code>func</code> – Optional function to apply to the keys and values of the map. [[Relational_Data_-_Functions#higher-order-functions---operator-and-lambdaparams-expr-function|Lambda function]].
* <code>limit</code> – Elements in range [1..limit] are sorted. [[Relational_Data_-_Data_Types_-_Int_Uint|(U)Int]].
* <code>map</code> – Map to sort. [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

* Partially sorted map. [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Example'''

<syntaxhighlight lang="sql">SELECT mapPartialSort((k, v) -> v, 2, map('k1', 3, 'k2', 1, 'k3', 2));</syntaxhighlight>
<syntaxhighlight lang="text">┌─mapPartialSort(lambda(tuple(k, v), v), 2, map('k1', 3, 'k2', 1, 'k3', 2))─┐
│ {'k2':1,'k3':2,'k1':3}                                                    │
└───────────────────────────────────────────────────────────────────────────┘</syntaxhighlight>
<span id="mapreversesortfunc-map"></span>
== mapReverseSort([func,], map) ==

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

'''Examples'''

<syntaxhighlight lang="sql">SELECT mapReverseSort(map('key2', 2, 'key3', 1, 'key1', 3)) AS map;</syntaxhighlight>
<syntaxhighlight lang="text">┌─map──────────────────────────┐
│ {'key3':1,'key2':2,'key1':3} │
└──────────────────────────────┘</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT mapReverseSort((k, v) -> v, map('key2', 2, 'key3', 1, 'key1', 3)) AS map;</syntaxhighlight>
<syntaxhighlight lang="text">┌─map──────────────────────────┐
│ {'key1':3,'key2':2,'key3':1} │
└──────────────────────────────┘</syntaxhighlight>
For more details see function [[Relational_Data_-_Functions_-_Array_Functions#array_functions-reverse-sort|arrayReverseSort]].

<span id="mappartialreversesort"></span>
== mapPartialReverseSort ==

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

'''Syntax'''

<syntaxhighlight lang="sql">mapPartialReverseSort([func,] limit, map)</syntaxhighlight>
'''Arguments'''

* <code>func</code> – Optional function to apply to the keys and values of the map. [[Relational_Data_-_Functions#higher-order-functions---operator-and-lambdaparams-expr-function|Lambda function]].
* <code>limit</code> – Elements in range [1..limit] are sorted. [[Relational_Data_-_Data_Types_-_Int_Uint|(U)Int]].
* <code>map</code> – Map to sort. [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Returned value'''

* Partially sorted map. [[Relational_Data_-_Data_Types_-_Map|Map]].

'''Example'''

<syntaxhighlight lang="sql">SELECT mapPartialReverseSort((k, v) -> v, 2, map('k1', 3, 'k2', 1, 'k3', 2));</syntaxhighlight>
<syntaxhighlight lang="text">┌─mapPartialReverseSort(lambda(tuple(k, v), v), 2, map('k1', 3, 'k2', 1, 'k3', 2))─┐
│ {'k1':3,'k3':2,'k2':1}                                                           │
└──────────────────────────────────────────────────────────────────────────────────┘</syntaxhighlight>
[[Category:Relational_Data]]