Tuple map functions¶

The following functions are available for tuple maps.

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, Tinybird accepts 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]]])

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.

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'}                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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

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's (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 aren't 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's.

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.

or

• 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)) │
└──────────────────────────────┴───────────────────────────────────┘

mapContains¶

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

Syntax¶

mapContains(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:

SELECT mapContains(a, 'name')
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['name', 'age'], ['eleven', '11']), (['number', 'position'], ['twelve', '6.0'])) )

Result:

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

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:

SELECT mapKeys(a)
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['name', 'age'], ['eleven', '11']), (['number', 'position'], ['twelve', '6.0'])) )

Result:

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

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:

SELECT mapValues(a)
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['name', 'age'], ['eleven', '11']), (['number', 'position'], ['twelve', '6.0'])) )

Result:

┌─mapValues(a)─────┐
│ ['eleven','11']  │
│ ['twelve','6.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:

SELECT mapContainsKeyLike(a, 'a%')
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['abc', 'def'], ['abc', 'def']), (['hij', 'klm'], ['hij', 'klm'])) )

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:

SELECT mapExtractKeyLike(a, 'a%')
FROM ( select (c1, c2)::Map(String, String) as a
      from values((['abc', 'def'], ['abc', 'def']), (['hij', 'klm'], ['hij', 'klm'])) )

Result:

┌─mapExtractKeyLike(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.

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.

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}                                                           │
└──────────────────────────────────────────────────────────────────────────────────┘