Map functions in ClickHouse provide powerful tools for working with key-value pair data structures.

​
ClickHouse function reference

​
map

Creates a value of type Map(key, value) from key-value pairs. Syntax: 
map(key1, value1[, key2, value2, ...])
Arguments:
  • key_n (Any type supported as key type of Map): The keys of the map entries.
  • value_n (Any type supported as value type of Map): The values of the map entries.
Returns:
  • A map containing key:value pairs. Type: Map(key, value).
Example: 
SELECT
  map('salsa', 'mild', 'guacamole', 'spicy') AS taco_toppings;
Result: 
| taco_toppings                                |
|----------------------------------------------|
| {'salsa':'mild','guacamole':'spicy'}         |

​
mapFromArrays

Creates a map from an array or map of keys and an array or map of values. Syntax: 
mapFromArrays(keys, values)
Alias:
  • MAP_FROM_ARRAYS
Arguments:
  • keys (Array or Map): Array or map of keys to create the map from.
  • values (Array or Map): Array or map of values to create the map from.
Returns:
  • A map with keys and values constructed from the key array and value array/map.
Example: 
SELECT
  mapFromArrays(['beef', 'chicken', 'fish'], ['carne', 'pollo', 'pescado']) AS taco_menu;
Result: 
| taco_menu                                                    |
|--------------------------------------------------------------|
| {'beef':'carne','chicken':'pollo','fish':'pescado'}          |

​
extractKeyValuePairs

Converts a string of key-value pairs to a Map(String, String). Parsing is tolerant towards noise (e.g. log files). Syntax: 
extractKeyValuePairs(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]])
Alias:
  • str_to_map
  • mapFromString
Arguments:
  • data (String or FixedString): String to extract key-value pairs from.
  • key_value_delimiter (String or FixedString, optional): Single character delimiting keys and values. Defaults to :.
  • pair_delimiters (String or FixedString, optional): Set of character delimiting pairs. Defaults to , and ;.
  • quoting_character (String or FixedString, optional): Single character used as quoting character. Defaults to ".
Returns:
  • A map of key-value pairs. Type: Map(String, String)
Example: 
SELECT
  extractKeyValuePairs('filling:beef, sauce:salsa, extras:guacamole;cheese') AS taco_order;
Result: 
| taco_order                                                           |
|----------------------------------------------------------------------|
| {'filling':'beef','sauce':'salsa','extras':'guacamole;cheese'}       |

​
mapFromArrays

Creates a map from an array of keys and an array of values. Syntax: 
mapFromArrays(keys, values)
Alias:
  • MAP_FROM_ARRAYS(keys, values)
Arguments:
  • keys (Array): Array of keys to create the map from.
  • values (Array): Array of values to create the map from.
Returns: A map with keys and values constructed from the key array and value array. Example: 
SELECT
  mapFromArrays(['salsa', 'guacamole', 'sour_cream'], [1, 2, 3]) AS taco_toppings_map;
Result: 
| taco_toppings_map                               |
|-------------------------------------------------|
| {'salsa':1,'guacamole':2,'sour_cream':3}        |
mapFromArrays also accepts arguments of type Map. These are cast to array of tuples during execution. 
SELECT
  mapFromArrays([1, 2, 3], map('mild', 1, 'medium', 2, 'hot', 3)) AS spice_level_map;
Result: 
| spice_level_map                                 |
|-------------------------------------------------|
| {1:('mild',1),2:('medium',2),3:('hot',3)}       |
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.
This function is a convenient alternative to syntax CAST([...], 'Map(key_type, value_type)'). For example, instead of writing:
  • CAST((['salsa', 'guacamole'], [4, 5]), 'Map(String, UInt32)'), or
  • CAST([('salsa',4), ('guacamole',5)], 'Map(String, UInt32)')
you can write mapFromArrays(['salsa', 'guacamole'], [4, 5]).

​
extractKeyValuePairs

Converts a string of key-value pairs to a Map(String, String). This function is useful for parsing log files or other text-based data formats that contain key-value pairs. Syntax 
extractKeyValuePairs(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]])
Aliases:
  • str_to_map
  • mapFromString
Arguments
  • data (String or FixedString): String to extract key-value pairs from.
  • key_value_delimiter (String or FixedString, optional): Single character delimiting keys and values. Default: :.
  • pair_delimiters (String or FixedString, optional): Set of characters delimiting pairs. Default: , and ;.
  • quoting_character (String or FixedString, optional): Single character used as quoting character. Default: ".
Returns:
  • A map of key-value pairs. Type: Map(String, String)
Examples Basic usage: 
SELECT
  extractKeyValuePairs('salsa:mild, guacamole:spicy; chips:crispy') AS taco_toppings
Result: 
| taco_toppings                                                 |
|---------------------------------------------------------------|
| {'salsa':'mild','guacamole':'spicy','chips':'crispy'}         |
Using a custom quote character: 
SELECT
  extractKeyValuePairs('salsa:\'mild\';\'guacamole\':spicy;chips:crispy,extra_salsa:yes', ':', ';,', '\'') AS taco_order
Result: 
| taco_order                                                                            |
|---------------------------------------------------------------------------------------|
| {'salsa':'mild','guacamole':'spicy','chips':'crispy','extra_salsa':'yes'}             |
Handling escape sequences: 
SELECT
  extractKeyValuePairs('spice_level:a\\x0A\\n\\0') AS taco_spice
Result: 
| taco_spice                         |
|------------------------------------|
| {'spice_level':'a\\x0A\\n\\0'}     |
Restoring a map from a serialized string: 
SELECT
  map('Carne Asada', '2', 'Al Pastor', '3') AS original_order,
  toString(original_order) AS serialized_order,
  extractKeyValuePairs(serialized_order, ':', ',', '\'') AS restored_order
Result: 
| Row 1:                                                   |
|----------------------------------------------------------|
| original_order:  {'Carne Asada':'2','Al Pastor':'3'}     |
| serialized_order: {'Carne Asada':'2','Al Pastor':'3'}    |
| restored_order:   {'Carne Asada':'2','Al Pastor':'3'}    |
This function is particularly useful when working with log files or other text-based data formats that contain key-value pairs, such as taco order details or ingredient lists.

​
extractKeyValuePairsWithEscaping

Converts a string of key-value pairs to a Map(String, String) with support for escape sequences. Syntax: 
extractKeyValuePairsWithEscaping(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]])
Arguments:
  • data (String or FixedString): String to extract key-value pairs from.
  • key_value_delimiter (String or FixedString, optional): Single character delimiting keys and values. Default: :.
  • pair_delimiters (String or FixedString, optional): Set of characters delimiting pairs. Default: , and ;.
  • quoting_character (String or FixedString, optional): Single character used as quoting character. Default: ".
Returns:
  • A map of key-value pairs. (Map(String, String))
Supported escape sequences: \x, \N, \a, \b, \e, \f, \n, \r, \t, \v, \0, \\, \', \", `, /, =, and ASCII control characters (c <= 31). Non-standard escape sequences are returned as-is (including the backslash).
  • Leading escape sequences are skipped in keys and considered invalid for values.
  • This function is useful when pre-escaping and post-escaping are not suitable.
Example: 
SELECT
  extractKeyValuePairsWithEscaping('salsa:spicy\\ntomato,guacamole:creamy\\navocado') AS taco_toppings;
Result: 
| taco_toppings                                                       |
|---------------------------------------------------------------------|
| {'salsa':'spicy\ntomato','guacamole':'creamy\navocado'}             |
In this example, the function correctly handles the escaped newline characters in the topping descriptions, preserving them in the resulting map.

​
mapAdd

Collects all the keys and sums corresponding values in maps or tuples of arrays. Syntax 
mapAdd(arg1, arg2 [, ...])
Arguments
  • arg1, arg2, … (Map or Tuple): Maps or tuples of two arrays, where:
    • The first array contains keys
    • The second array contains values for each key
All key arrays should have the same type. All value arrays should contain items that can be promoted to one of these types:
  • Int64
  • UInt64
  • Float64
The common promoted type is used for the result array. Returns
  • Depending on the arguments, returns either:
    • A map
    • A tuple where:
      • The first array contains sorted keys
      • The second array contains corresponding values
Examples Using Map type: 
SELECT
  mapAdd(map(1, 1), map(1, 1));
Result: 
| mapAdd(map(1, 1), map(1, 1))  |
|-------------------------------|
| {1:2}                         |
Using a tuple of arrays: 
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))  |
In this example:
  • Keys [1, 2] are combined
  • Corresponding values [1, 1] and [1, 1] are summed to [2, 2]
  • The result type is a tuple of arrays, where values are promoted to UInt64
This function is useful for aggregating map data, such as combining ingredient quantities across multiple taco recipes or summing up sales data for different taco varieties.

​
mapSubtract

Subtracts corresponding values for matching keys in maps. Syntax: 
mapSubtract(map1, map2[, ...])
Arguments:
  • map1, map2, … (Map): Maps to subtract. All maps should have the same key and value types. Value types must be numeric and can be promoted to a common type (Int64, UInt64, or Float64).
Returns: A new map containing:
  • All keys from the input maps
  • Values calculated by subtracting corresponding values from subsequent maps from the first map’s values
  • Keys present only in the first map retain their original values
  • Keys not present in the first map but present in subsequent maps will have negative values in the result
Example: 
SELECT
  mapSubtract(
    map('carnitas', 5, 'al pastor', 3, 'barbacoa', 2),
    map('carnitas', 2, 'al pastor', 1, 'carne asada', 4)
  ) AS remaining_tacos;
Result: 
| remaining_tacos                                                   |
|-------------------------------------------------------------------|
| {'carnitas':3,'al pastor':2,'barbacoa':2,'carne asada':-4}        |
In this example:
  • ‘carnitas’ and ‘al pastor’ values are subtracted
  • ‘barbacoa’ keeps its original value as it’s only in the first map
  • ‘carne asada’ appears with a negative value as it’s only in the second map
When subtracting maps with different keys, the result will contain all unique keys from all input maps.

​
mapPopulateSeries

Fills missing key-value pairs in a map with integer keys, creating a series from the smallest to the largest key (or specified maximum) with a step size of 1. Syntax: 
mapPopulateSeries(map[, max])
mapPopulateSeries(keys, values[, max])
Arguments:
  • map (Map): A map with integer keys.
  • keys (Array(Int)): Array of keys.
  • values (Array(Int)): Array of values.
  • max (Int8, Int16, Int32, Int64, Int128, Int256, optional): Optional maximum key value.
Returns:
  • A Map or a Tuple of two Arrays: keys in sorted order, and corresponding values.
Example: 
SELECT
  mapPopulateSeries(map(1, 10, 5, 20), 6) AS populated_map;
Result: 
| populated_map                       |
|-------------------------------------|
| {1:10,2:0,3:0,4:0,5:20,6:0}         |
In this example:
  • Keys 2, 3, and 4 are added to fill the series.
  • The value 0 is used as the default for missing keys.
  • Key 6 is added because the max argument is set to 6.
Example with 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)) |
In this taco-themed example: 
SELECT
  mapPopulateSeries(map(1, 'Carne Asada', 3, 'Al Pastor'), 5) AS taco_menu;
Result: 
| taco_menu                                                  |
|------------------------------------------------------------|
| {1:'Carne Asada',2:'',3:'Al Pastor',4:'',5:''}             |
This creates a complete taco menu with placeholders for missing taco varieties.
If keys repeat in the input, only the first value (in order of appearance) is associated with the key in the result.
The provided documentation does not contain any “Note:” or “Warning:” sections, so I will return the original markdown unchanged:

​
mapContains

Checks if a given key is present in a map. Syntax 
mapContains(map, key)
Arguments
  • map (Map): A map.
  • key: The key to search for. The type must match the key type of the map.
Returns
  • 1 if the map contains the key, 0 if not. Type: UInt8.
Example Query: 
SELECT
    mapContains(map('filling', 'carnitas', 'salsa', 'verde'), 'filling') AS order1,
    mapContains(map('tortilla', 'corn', 'cheese', 'queso fresco'), 'filling') AS order2;
Result: 
| order1 | order2 |
|--------|--------|
| 1      | 0      |
In this example, we check if the ‘filling’ key exists in each taco order. The first order contains ‘filling’, so it returns 1, while the second order doesn’t, so it returns 0.

​
mapKeys

Returns an array containing all the keys from a given map. Syntax: 
mapKeys(map)
Arguments:
  • map (Map): A map.
Returns:
  • An array containing all keys from the map. (Array).
Example: 
SELECT
    mapKeys(map('name', 'Carne Asada', 'spice_level', 'medium')) AS keys1,
    mapKeys(map('filling', 'beans', 'tortilla', 'corn')) AS keys2;
Result: 
| keys1                     | keys2                   |
|---------------------------|-------------------------|
| ['name','spice_level']    | ['filling','tortilla']  |
The mapKeys function is useful for extracting and working with the keys of a map separately from their values. This can be particularly helpful when you need to perform operations or comparisons based on the keys alone.

​
mapValues

Returns the values of a given map. Syntax: 
mapValues(map)
Arguments:
  • map (Map): Map.
Returns:
  • Array containing all the values from the map. Array.
Example: 
SELECT
    mapValues(map('name', 'Carne Asada', 'price', '$8.99')) AS values1,
    mapValues(map('filling', 'Carnitas', 'salsa', 'Roja')) AS values2;
Result: 
| values1                   | values2                |
|---------------------------|------------------------|
| ['Carne Asada','$8.99']   | ['Carnitas','Roja']    |
The mapValues function is particularly useful when you need to extract and work with just the values from a map, such as for aggregations or further processing.

​
mapContainsKeyLike

Checks if a map contains a key that matches a given pattern. Syntax: 
mapContainsKeyLike(map, pattern)
Arguments:
  • map (Map): A map.
  • pattern (String): A string pattern to match against the keys.
Returns:
  • 1 if the map contains a key matching the specified pattern, 0 otherwise. Type: UInt8.
Example: 
SELECT
  mapContainsKeyLike(map('carne_asada', 'beef', 'pollo', 'chicken', 'al_pastor', 'pork'), 'p%') AS row1,
  mapContainsKeyLike(map('guacamole', 'avocado', 'pico_de_gallo', 'tomato'), 'p%') AS row2;
Result: 
| row1 | row2 |
|------|------|
| 1    | 1    |
In this example, mapContainsKeyLike checks if each items map contains a key starting with ‘p’. Both rows return 1 because they contain keys matching the pattern (‘pollo’ and ‘pico_de_gallo’ respectively). This function is useful for searching maps with string keys when you need to perform pattern matching on the keys.

​
mapExtractKeyLike

Extracts key-value pairs from a map where the keys match a given pattern. Syntax: 
mapExtractKeyLike(map, pattern)
Arguments:
  • map (Map(String, T)): A map with string keys.
  • pattern (String): A string pattern to match against the keys. Uses the LIKE operator for matching.
Returns: A new map containing only the key-value pairs where the key matches the given pattern. If no keys match, an empty map is returned. Example: 
SELECT
  mapExtractKeyLike(
    map(
      'salsa_verde', 'spicy',
      'guacamole', 'creamy',
      'salsa_roja', 'tangy'
    ),
    'salsa%'
  ) AS taco_sauces;
Result: 
| taco_sauces                                         |
|-----------------------------------------------------|
| {'salsa_verde':'spicy','salsa_roja':'tangy'}        |
In this example, mapExtractKeyLike returns a new map containing only the key-value pairs where the key starts with ‘salsa’, matching the pattern ‘salsa%’. This function is useful for filtering maps based on key patterns, which can be particularly handy when working with structured data like menu items, ingredient lists, or categorized information in taco-related applications.

​
mapApply

Applies a function to each element of a map. Syntax: 
mapApply(func, map)
Arguments:
  • func (Function): Lambda function to apply to each key-value pair.
  • map (Map): Map to process.
Returns:
  • A new map obtained by applying func(key, value) to each element of the original map.
Example: 
SELECT
  mapApply((k, v) -> (k, v * 10), _map) AS result
FROM (
  SELECT
    map('salsa', number, 'guacamole', number * 2) AS _map
  FROM numbers(3)
)
Result: 
| result                           |
|----------------------------------|
| {'salsa':0,'guacamole':0}        |
| {'salsa':10,'guacamole':20}      |
| {'salsa':20,'guacamole':40}      |
In this example, we create a map with taco toppings as keys and their quantities as values. The mapApply function then multiplies each value by 10, simulating an increase in taco topping quantities.
The lambda function (k, v) -> (k, v * 10) keeps the key unchanged and multiplies the value by 10 for each element in the map.

​
mapFilter

Filters a map by applying a function to each map element. Syntax: 
mapFilter(func, map)
Arguments:
  • func (Function): Lambda function to apply to each key-value pair.
  • map (Map): Map to filter.
Returns:
  • A new map containing only the elements for which func(key, value) returns a non-zero value.
Example: 
SELECT
  mapFilter((k, v) -> v % 2 = 0, map('salsa', 1, 'guacamole', 2, 'sour_cream', 3, 'cheese', 4)) AS even_toppings;
Result: 
| even_toppings                    |
|----------------------------------|
| {'guacamole':2,'cheese':4}       |
In this example, mapFilter returns a new map containing only the toppings with even quantities.
The mapFilter function is particularly useful when you need to selectively include key-value pairs from a map based on a condition. It’s often used in data processing pipelines to clean or transform map data.

​
mapUpdate

Updates values in one map with values from another map for corresponding keys. Syntax: 
mapUpdate(map1, map2)
Arguments:
  • map1 (Map): The base map to be updated.
  • map2 (Map): The map containing updates.
Returns:
  • Returns map1 with values updated for keys that exist in map2. (Map).
Example: 
SELECT
  mapUpdate(
    map('salsa', 'mild', 'guacamole', 'chunky'),
    map('salsa', 'hot', 'queso', 'smooth')
  ) AS updated_toppings;
Result: 
| updated_toppings                                        |
|---------------------------------------------------------|
| {'salsa':'hot','guacamole':'chunky','queso':'smooth'}   |
In this example:
  • The ‘salsa’ value is updated from ‘mild’ to ‘hot’.
  • The ‘guacamole’ key-value pair remains unchanged.
  • The ‘queso’ key-value pair is added from the second map.
This function is useful for merging or updating map data, such as combining different taco topping preferences or updating inventory information.

​
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 the [] operator. Syntax: 
mapConcat(maps)
Arguments:
  • maps (Map): Arbitrarily many Map type arguments.
Returns: A map with concatenated maps passed as arguments. Example: 
SELECT
  mapConcat(map('salsa', 1, 'guacamole', 3), map('queso', 2)) AS taco_toppings;
Result: 
| taco_toppings                             |
|-------------------------------------------|
| {'salsa':1,'guacamole':3,'queso':2}       |
When concatenating maps with duplicate keys, only the first value is accessible: 
SELECT
  mapConcat(map('salsa', 'mild', 'queso', 'white'), map('salsa', 'hot')) AS taco_toppings,
  taco_toppings['salsa'] AS salsa_type;
Result: 
| taco_toppings                                        | salsa_type |
|------------------------------------------------------|------------|
| {'salsa':'mild','queso':'white','salsa':'hot'}       | mild       |
In this example, even though ‘salsa’ appears twice in the concatenated map, accessing taco_toppings['salsa'] returns the first value ‘mild’.
The mapConcat function is useful for combining multiple maps, such as merging different topping preferences for a taco order. However, be cautious when dealing with duplicate keys, as only the first occurrence will be accessible using the standard map access syntax.

​
mapExists

Checks if a given key exists in a map. Syntax: 
mapExists(map, key)
Arguments:
  • map (Map): A map.
  • key: The key to search for. Type must match the key type of the map.
Returns:
  • 1 if the map contains the key, 0 if not. (UInt8).
Example: 
SELECT
    mapExists(map('filling', 'carnitas', 'salsa', 'verde'), 'filling') AS order1,
    mapExists(map('tortilla', 'corn', 'cheese', 'queso fresco'), 'filling') AS order2;
Result: 
| order1 | order2 |
|--------|--------|
| 1      | 0      |
In this example, we check if the ‘filling’ key exists in each taco order. The first order contains ‘filling’, so it returns 1, while the second order doesn’t, so it returns 0.
The mapExists function is case-sensitive for string keys.

​
mapAll

Returns 1 if the specified function returns a non-zero value for all key-value pairs in the map. Otherwise, it returns 0. Syntax: 
mapAll([func,] map)
Arguments:
  • func (Function, optional): Lambda function to apply to each key-value pair. If not specified, the function checks if all values are non-zero.
  • map (Map): The input Map.
Returns:
  • 1 if the condition is true for all elements, 0 otherwise. (UInt8)
Example: 
SELECT
  mapAll((k, v) -> (v > 0), map('salsa', 3, 'guacamole', 2, 'sour_cream', 1)) AS all_positive;
Result: 
| all_positive |
|--------------|
| 1            |
In this example, mapAll checks if all taco toppings have a positive quantity. Since all values are greater than 0, it returns 1. 
SELECT
  mapAll((k, v) -> (v > 2), map('salsa', 3, 'guacamole', 2, 'sour_cream', 1)) AS all_above_two;
Result: 
| all_above_two |
|---------------|
| 0             |
Here, mapAll checks if all toppings have a quantity greater than 2. Since ‘guacamole’ and ‘sour_cream’ have quantities of 2 and 1 respectively, it returns 0.
mapAll is a higher-order function. You can pass a lambda function to it as the first argument to define custom conditions for each key-value pair.

​
mapSort

Sorts the elements of a map in ascending order. Syntax: 
mapSort([func,] map)
Arguments:
  • func (optional): Lambda function to determine the sorting order. If not specified, sorting is based on the map keys.
  • map (Map): The map to sort.
Returns:
  • A new sorted map. (Map).
Examples: Sorting by keys (default): 
SELECT
  mapSort(map('salsa', 2, 'guacamole', 1, 'sour_cream', 3)) AS sorted_toppings;
Result: 
| sorted_toppings                                 |
|-------------------------------------------------|
| {'guacamole':1,'salsa':2,'sour_cream':3}        |
Sorting by values using a lambda function: 
SELECT
  mapSort((k, v) -> v, map('salsa', 2, 'guacamole', 1, 'sour_cream', 3)) AS sorted_by_popularity;
Result: 
| sorted_by_popularity                            |
|-------------------------------------------------|
| {'guacamole':1,'salsa':2,'sour_cream':3}        |
In this example, taco toppings are sorted first by their keys (alphabetically), and then by their popularity (value).
This function is similar to arraySort but operates on maps instead of arrays.

​
mapPartialSort

Partially sorts the elements of a map in ascending order. Syntax 
mapPartialSort([func,] limit, map)
Arguments
  • func — Optional lambda function to determine the sorting order. If not specified, sorting is based on map keys.
  • limit (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, or Int64): Number of elements to sort. Elements in range [1..limit] are sorted.
  • map (Map): Map to sort.
Returns
  • A partially sorted map. (Map).
Example Let’s sort a map of taco ingredients by their quantity: 
SELECT
  mapPartialSort((k, v) -> v, 2, map('salsa', 3, 'cheese', 1, 'lettuce', 2)) AS sorted_toppings;
Result: 
| sorted_toppings                         |
|-----------------------------------------|
| {'cheese':1,'lettuce':2,'salsa':3}      |
In this example:
  • The map is sorted based on the values (quantities of ingredients).
  • Only the first 2 elements are guaranteed to be in the correct order.
  • ‘cheese’ (1) and ‘lettuce’ (2) are sorted, while ‘salsa’ (3) remains in its original position.
This function is useful when you need to partially sort a large map, which can be more efficient than sorting the entire map.

​
mapReverseSort

Sorts the elements of a map in descending order. If a function is specified, the sorting order is determined by the result of that function applied to the keys and values of the map. Syntax: 
mapReverseSort([func,] map)
Arguments:
  • func (optional): A lambda function to apply to the keys and values of the map.
  • map (Map): The map to sort.
Returns:
  • A new map with elements sorted in descending order.
Example: 
SELECT
  mapReverseSort(map('salsa', 2, 'guacamole', 1, 'sour_cream', 3)) AS taco_toppings;
Result: 
| taco_toppings                                     |
|---------------------------------------------------|
| {'sour_cream':3,'salsa':2,'guacamole':1}          |
Using a lambda function to sort by value: 
SELECT
  mapReverseSort((k, v) -> v, map('salsa', 2, 'guacamole', 1, 'sour_cream', 3)) AS taco_toppings;
Result: 
| taco_toppings                                     |
|---------------------------------------------------|
| {'sour_cream':3,'salsa':2,'guacamole':1}          |
In this example, the taco toppings are sorted in descending order based on their popularity (represented by the numeric values).
This function is similar to arrayReverseSort, but operates on maps instead of arrays.

​
mapPartialReverseSort

Sorts the elements of a map in descending order with an additional limit argument allowing partial sorting. If a function is specified, the sorting order is determined by the result of that function applied to the keys and values of the map. Syntax: 
mapPartialReverseSort([func,] limit, map)
Arguments:
  • func (Function, optional): Function to apply to the keys and values of the map. Lambda function.
  • limit (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64): Elements in range [1..limit] are sorted.
  • map (Map): Map to sort.
Returns:
  • Partially sorted map. (Map).
Example: 
SELECT
  mapPartialReverseSort((k, v) -> v, 2, map('salsa', 3, 'guacamole', 1, 'sour_cream', 2));
Result: 
| mapPartialReverseSort(lambda(tuple(k, v), v), 2, map('salsa', 3, 'guacamole', 1, 'sour_cream', 2)) |
|----------------------------------------------------------------------------------------------------|
| {'salsa':3,'sour_cream':2,'guacamole':1}                                                           |
In this example, the function sorts the taco toppings map based on their popularity (value). The limit of 2 ensures that only the top 2 elements are sorted in descending order, while the least popular topping remains at the end.