map #

Use any quantity of key/value pairs to construct a Map Column
Syntax

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

Param

• key1, key2: keys in the Map Column
• value1, value2: values in the Map Column

Return value

• Constructed Map Column

Example

SELECT map(1, 2, 3, 4), map('k1', 'v1', 'k2', 'v2');

Query results

{1:2,3:4}    {'k1':'v1','k2':'v2'}

arrayElement #

Search the data of a specific key in the Map. Rows not containing this key will be filled with the Default, equivalent to map[key]
Syntax

arrayElement(map, key)

Param

• map: Map Column to be searched
• key: name of the key to be searched

Return value

• value corresponding to the key name

Example

WITH map('k1', 1) AS m 
SELECT arrayElement(m, 'k1'), m['k1'], m['k2'];

Query results

1    1    0

Caution
When using arrayElement, the entire Map Column is read first and then filtered, so for implicit Map Columns, search performance is lower. For implicit Map Columns, it is recommended to use the mapElement (or {}) method to search for keys.

mapElement #

Search the data of a specific key in the Map. Rows not containing this key will be filled with NULL, equivalent to map{key}
Syntax

mapElement(map, key)

Param

• map: Map Column to be searched
• key: name of the key to be searched

Return value

• value corresponding to the key name

Example

WITH map('k1', 1) AS m 
SELECT mapElement(m, 'k1'), m{'k1'}, m{'k2'};

Query results

1    1    NULL

Caution
For implicit Map Columns, using this method only needs to read the corresponding implicit Column, which is more efficient compared to arrayElement;

mapKeys #

Obtain the keys of each row in the Map Data and return them as an Array
Syntax

mapKeys(map)

Param

• map: Map Column to be searched

Return value

• Array of keys contained in each row

Example

WITH map('k1', 'v1', 'k2', 'v2') AS m 
SELECT mapKeys(m);

Query results

['k1','k2']

Caution
For implicit Map Columns, this method will first read the entire Map Column, then extract the keys and return them. The efficiency is lower. It is recommended to use the getMapKeys method below.

mapValues #

Obtain the values of each row in the Map Data and return them as an Array
Syntax

mapValues(map)

Param

• map: Map Column to be searched

Return value

• Array of values contained in each row

Example

WITH map('k1', 'v1', 'k2', 'v2') AS m 
SELECT mapValues(m);

Query results

['v1','v2']

getMapKeys #

Obtain the list of keys contained in the Map Column (in the specified partition), with each key appearing only once in the result
Syntax

getMapKeys('database', 'table', 'map' [, 'partition_regex'])

Param

• database: database name, must be written as a string constant in quotes
• table: table name, must be written as a string constant in quotes
• map: Map Column name, must be written as a string constant in quotes
• partition_regex: regular expression for the partition

Return value

• Array of keys contained in the Map Column (in the specified partition)

Example

INSERT INTO some_db.map_table (m) VALUES {'k1': 'v1', 'k2': 'v2'}; 
SELECT getMapKeys('some_db', 'map_table', 'm');

Query results

['k1','k2']

Caution
This function finds keys by reading the metadata of each part, so it is only applicable to implicit Map Columns.

mapContains #

Return whether each row of the Map Column contains the target key
Syntax

mapContains(map, key)

Param

• map: Map Column
• key: name of the key to be searched

Return value

• Whether each row contains the specified key, 1 means contains, 0 means not included, type: UInt8

Example

WITH map('k1', 'v1', 'k2', 'v2') AS m 
SELECT mapContains(m, 'k1'), mapContains(m, 'k3');

Query results

1    0

str_to_map #

Use the specified delimiter to parse a String into a Map(String, String) type.
Syntax

str_to_map(pattern, pair_delimiter, key_value_delimiter)

Param

• pattern: The String to be parsed
• key_value_delimiter: The delimiter between key and value
• pair_delimiter: The delimiter between key-value pairs

Return value

• The constructed Map Column

Example

SELECT str_to_map('k1=v1, k2=v2', ',', '=');

Query results

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

mapFromArrays #

Use two Arrays to construct a Map Column, where the first Array is the key and the second Array is the value.
Syntax

mapFromArrays(keys, values)

Param

• keys: List used as key
• values: List used as value, must have the same length as keys in each row

Return value

• The constructed Map Column

Example

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

Query results

{'a':1,'b':2,'c':3}

mapAdd #

Add the values of corresponding keys in multiple Map Columns
Syntax

mapAdd(map1, map2 [, ...])

Param

• map1, map2: Map Columns to be added

Return value

• Map Column after summation

Example

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

Query results

{1:3,2:2}

mapSubtract #

Subtract the values of corresponding keys in multiple Map Columns (the first value minus all subsequent values)
Syntax

mapAdd(map1, map2 [, ...])

Param

• map1: Map Column as the minuend
• map2: Map Column as the subtrahend

Return value

• Map Column after subtraction

Example

SELECT mapSubtract(map(1, 2)::Map(Int16,Int16), map(1, 1)::Map(Int16,Int16), map(2, -2)::Map(Int16,Int16));

Query results

{1:1,2:2}

mapPopulateSeries #

The Map key type must be Integer. Fill the Map with all key values from the minimum key to the maximum key, and fill the value with the default value of the corresponding type. The maximum key can also be specified by the second parameter.
This function can also accept two Arrays, the first as key and the second as value.
Syntax

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

Param

• map: Map Column to be filled, key type must be Integer
• keys: List of keys to be filled, type: Array(Integer)
• values: List of values to be filled, type: Array
• max: The maximum key value to be filled

Return value

• If the first parameter is a Map, returns the filled Map Column
• If the first two parameters are keys and values, returns the filled Array, placed in a Tuple. Type: Tuple(Array, Array)

Example

SELECT mapPopulateSeries(map(2, 10, 5, 20)), mapPopulateSeries(map(2, 10, 5, 20), 3); 
SELECT mapPopulateSeries([1, 5], [5, 20], 6);

Query results

{2:10,3:0,4:0,5:20}    {2:10,3:0} 
([1,2,3,4,5,6],[5,0,0,0,20,0])

mapContainsKeyLike #

Returns whether each row of the Map Column contains a key that meets the specified pattern
Syntax

mapContainsKeyLike(map, pattern)

Param

• map: Map Column to be queried, key must be of type String or FixedString
• pattern: The specified key pattern, syntax is the same as the like clause

Return value

• Whether each row contains a key that meets the specified pattern, 1 means contains, 0 means not included, type: UInt8

Example

WITH map('k1', 'v1', 'k2', 'v2') AS m 
SELECT mapContainsKeyLike(m, 'k%'), mapContainsKeyLike(m, 'm%');

Query results

1    0

mapExtractKeyLike #

Filter the keys and their corresponding values in the Map Column that meet the specified pattern
Syntax

mapExtractKeyLike(map, pattern)

Param

• map: Map Column to be queried, key must be of type String or FixedString
• pattern: The specified key pattern, syntax is the same as the like clause

Return value

• The filtered Map Column

Example

WITH map('k1', 'v1', 'k2', 'v2') AS m 
SELECT mapExtractKeyLike(m, 'k%'), mapExtractKeyLike(m, 'm%');

Query results

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

mapApply #

Run the specified lambda expression on each key-value pair in the Map.
Syntax

mapApply(lambda, map)

Param

• lambda: Lambda expression with key-value pair as parameter
• map: Map Column

Return value

• A new Map column composed of key-value pairs returned by the lambda

Example

WITH map(1, 1, 2, 2) AS m 
SELECT mapApply((k, v) -> (toString(k), v * 2), m);

Query results

{'1':2,'2':4}

mapFilter #

Use the provided condition to filter key-value pairs in the Map.
Syntax

mapFilter(lambda, map)

Param

• lambda: A lambda expression that takes a key-value pair as parameters and returns a UInt8 or Nullable(UInt8) type. 0 and NULL will be filtered; values greater than 0 will be retained.
• map: The Map column to be filtered

Return value

• The filtered Map column

Example

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

Query results

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

mapUpdate #

Copy map1, use the value in map2 to update the copied Map column, and return it. For keys that exist in both map1 and map2, the corresponding value in map2 will be retained.
Syntax

mapUpdate(map1, map2)

Param

• map1: The Map column to be updated (copied)
• map2: The Map column used to update map1

Return value

• The Map column after updating

Example

WITH map('k1', 1, 'k2', 1) AS m1, map('k1', 2, 'k3', 2) AS m2 
SELECT mapUpdate(m1, m2), mapUpdate(m2, m1);

Query results

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