tencent cloud

Cloud Log Service

DocumentationCloud Log ServiceOperation GuideSearch and Analysis (Log Topic)Statistical Analysis (SQL)SQL FunctionsMAP function

MAP function

Download
Focus Mode
Font Size
Last updated: 2026-05-20 19:53:19
This article introduces the basic syntax and examples of MAP functions and operators. MAP functions and operators use the MAP type, where keys must be of character type and cannot be null, while values are allowed to be null.
Function Name
Statement
Description
map[key]
Obtain the corresponding value for a given key from the map.
cardinality(x)
Return the cardinality (size) of the map.
element_at(map(k, v), key)
Returns the value corresponding to the given key. If the key does not exist, returns NULL.
map() / map(array(k), array(v))
Returns an empty map or creates a map using the given array of key/value pairs.
map_from_entries(array(row(k, v)))
Create a map from a given array of entries.
multimap_from_entries(array(row(k, v)))
Create a multimap from a given array of entries, where each key can be associated with multiple values.
map_concat(map1, map2, ..., mapN)
Returns the union of all given maps.
map_filter(map(k, v), function(k, v, boolean))
Filter the map to create a new map with entries where the function returns true.
map_keys(x)
Returns all keys in the map.
map_values(x)
Returns all values in the map.
map_zip_with(map(k, v1), map(k, v2), function)
Merges two maps into one and applies a function to the value pairs with the same keys.
transform_keys(map(k1, v), function(k1, v, k2))
Applies a function to each entry and transforms the keys.
transform_values(map(k, v1), function(k, v1, v2))
Applies a function to each entry and transforms the values.

Subscript Operator []

The [] operator is used to retrieve the value corresponding to a given key from a map. If the key does not exist in the map, this operator throws an error. To return NULL when the key is absent, use the element_at function.

Syntax

map[key]

Parameter Description

Parameter
Description
map
The parameter value is of MAP type.
key
Key name to obtain.

Return Value Type

Type corresponding to values in the map.

Example

Example 1: Obtain the value corresponding to the key Bob from name_to_age_map.
Query and analysis statements.
* | SELECT name_to_age_map['Bob'] AS bob_age
Query and analysis results.
22
Example 2: Build a map and access the element with key key2.
Query and analysis statements.
* | SELECT MAP(ARRAY['key1', 'key2', 'key3'], ARRAY['v1', 'v2', 'v3'])['key2']
Query and analysis results.
v2

cardinality Function

The cardinality function returns the cardinality (size) of a map, which is the number of key-value pairs in the map.

Syntax

cardinality(x)

Parameter Description

Parameter
Description
x
The parameter value is of MAP type.

Return Value Type

It returns values of the bigint type.

Example

Calculate the number of key-value pairs in the map.
Query and analysis statements.
* | SELECT cardinality(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]))
Query and analysis results.
3

element_at Function

The element_at function returns the value corresponding to a given key. Unlike the subscript operator [], if the key does not exist in the map, element_at returns NULL instead of throwing an error.

Syntax

element_at(map(k, v), key)

Parameter Description

Parameter
Description
map(k, v)
The parameter value is of MAP type, where k is the key type and v is the value type.
key
Key name to obtain.

Return Value Type

v type (corresponding to the value type in the map). Returns NULL if the key does not exist.

Example

The value of the specified key in the map is obtained. Returns NULL if the key does not exist.
Query and analysis statements.
* | SELECT element_at(MAP(ARRAY['a', 'b'], ARRAY['test1', 'test2']), 'c')
Query and analysis results.
NULL

map function

The map function returns an empty map or creates a map using the given array of key/value pairs. When creating a map using the data type constructor, you need to provide a key array and a value array in the same order. The values must be of the same type, or the values can be cast to a common type.

Syntax

map()
map(array(k), array(v))

Parameter Description

Parameter
Description
array(k)
Key array.
array(v)
Value array, which must be the same length as the key array.

Return Value Type

map(k, v) type.

Example

Example 1: Returns an empty map.
Query and analysis statements.
* | SELECT map()
Query and analysis results.
{}
Example 2: Create a map containing integer values.
Query and analysis statements.
* | SELECT MAP(ARRAY['key1', 'key2', 'key3'], ARRAY[2373, 3463, 45837])
Query and analysis results.
{"key1":2373,"key2":3463,"key3":45837}
Example 3: Creating a map containing character values.
Query and analysis statements.
* | SELECT MAP(ARRAY['key1', 'key2', 'key3'], ARRAY['v1', 'v2', 'v3'])
Query and analysis results.
{"key1":"v1","key2":"v2","key3":"v3"}
Example 4: Create a map using integer and decimal values. The values are automatically converted to the decimal type.
Query and analysis statements.
* | SELECT MAP(ARRAY['key1', 'key2', 'key3'], ARRAY[23, 34.63, 45.837])
Query and analysis results.
{"key1":23,"key2":34.63,"key3":45.837}
Example 5: Creating a map containing NULL values.
Query and analysis statements.
* | SELECT MAP(ARRAY['key1', 'key2', 'key3'], ARRAY['v1', NULL, 'v3'])
Query and analysis results.
{"key1":"v1","key2":null,"key3":"v3"}
Note:
To create a map through aggregation, see the map_agg() and multimap_agg() functions.

map_from_entries function

The map_from_entries function is used to create a map from a given array of entries. Each element in the array is a row containing a key-value pair.

Syntax

map_from_entries(array(row(k, v)))

Parameter Description

Parameter
Description
array(row(k, v))
An array of rows containing key-value pairs, where each element is a tuple in the form of (k, v).

Return Value Type

map(k, v) type.

Example

Create a map from an array of entries.
Query and analysis statements.
* | SELECT map_from_entries(ARRAY[(1, 'x'), (2, 'y')])
Query and analysis results.
{"1":"x","2":"y"}

multimap_from_entries function

The multimap_from_entries function is used to create a multimap from a given array of entries. Unlike map_from_entries, a multimap associates multiple values with each key, and values with the same key are collected into an array.

Syntax

multimap_from_entries(array(row(k, v)))

Parameter Description

Parameter
Description
array(row(k, v))
An array of rows containing key-value pairs, allowing duplicate keys.

Return Value Type

map(k, array(v)) type.

Example

Create a multimap from an array of entries, with key 1 associated with multiple values.
Query and analysis statements.
* | SELECT multimap_from_entries(ARRAY[(1, 'x'), (2, 'y'), (1, 'z')])
Query and analysis results.
{"1":["x","z"],"2":["y"]}

map_concat function

The map_concat function returns the union of all given maps. If a key exists in multiple maps, the value for that key in the resulting map comes from the last map in the argument list that contains the key.

Syntax

map_concat(map1(k, v), map2(k, v), ..., mapN(k, v))

Parameter Description

Parameter
Description
map1, map2, ..., mapN
Multiple MAP-type parameters to be merged must have consistent key and value types.

Return Value Type

map(k, v) type.

Example

Merges two maps, taking the value from the latter for duplicate keys.
Query and analysis statements.
* | SELECT map_concat(MAP(ARRAY['a'], ARRAY[1]), MAP(ARRAY['a', 'b'], ARRAY[2, 3]))
Query and analysis results.
{"a":2,"b":3}

map_filter function

The map_filter function is used to filter out entries that meet specified conditions from a map to construct a new map. It evaluates each key-value pair through a Lambda function and retains only those entries for which the function returns true.

Syntax

map_filter(map(k, v), function(k, v, boolean))

Parameter Description

Parameter
Description
map(k, v)
The parameter value is of MAP type.
function(k, v, boolean)
Lambda function that takes key k and value v as parameters and returns a Boolean value. Syntax: (k, v) -> Boolean expression.

Return Value Type

map(k, v) type.

Example

Example 1: Filter out entries with NULL values.
Query and analysis statements.
* | SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL)
Query and analysis results.
{"10":"a","30":"c"}
Example 2: Retain entries with values greater than 10.
Query and analysis statements.
* | SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10)
Query and analysis results.
{"k1":20,"k3":15}

map_keys function

The map_keys function returns an array of all keys in the map.

Syntax

map_keys(x)

Parameter Description

Parameter
Description
x
The parameter value is of MAP(k, v) type.

Return Value Type

array(k) type.

Example

Obtain all keys in the map.
Query and analysis statements.
* | SELECT map_keys(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]))
Query and analysis results.
["a","b","c"]

map_values function

The map_values function returns an array of all values in the map.

Syntax

map_values(x)

Parameter Description

Parameter
Description
x
The parameter value is of MAP(k, v) type.

Return Value Type

array(v) type.

Example

Obtain all values in the map.
Query and analysis statements.
* | SELECT map_values(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]))
Query and analysis results.
[1, 2, 3]

map_zip_with function

The map_zip_with function merges two maps into one by processing key-value pairs with identical keys through a Lambda function. For keys present in only one map, NULL is passed as the corresponding value from the other map.

Syntax

map_zip_with(map(k, v1), map(k, v2), function(k, v1, v2, v3))

Parameter Description

Parameter
Description
map(k, v1)
The first parameter of MAP type.
map(k, v2)
The second MAP-type parameter must have keys of the same type as the first map.
function(k, v1, v2, v3)
Lambda function that takes key k, value v1 from the first map, and value v2 from the second map as parameters, returning a new value v3. Syntax: (k, v1, v2) -> expression.

Return Value Type

map(k, v3) type.

Example

Example 1: Merges two maps and concatenates corresponding string values.
Query and analysis statements.
* | SELECT map_zip_with(
    MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']),
    MAP(ARRAY[1, 2, 3], ARRAY['d', 'e', 'f']),
    (k, v1, v2) -> concat(v1, v2)
)
Query and analysis results.
{"1":"ad","2":"be","3":"cf"}
Example 2: Merges two maps, calculates the quotient of values, and concatenates key names.
Query and analysis statements.
* | SELECT map_zip_with(
    MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 8, 27]),
    MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]),
    (k, v1, v2) -> k || CAST(v1 / v2 AS VARCHAR)
)
Query and analysis results.
{"a":"a1","b":"b4","c":"c9"}

transform_keys function

The transform_keys function is used to apply a Lambda function to each entry of a map to transform the keys, generating a new map. The values remain unchanged.

Syntax

transform_keys(map(k1, v), function(k1, v, k2))

Parameter Description

Parameter
Description
map(k1, v)
The parameter value is of MAP type, where k1 is the original key type.
function(k1, v, k2)
Lambda function that takes the original key k1 and value v as parameters, returning a new key k2. Syntax: (k, v) -> new key expression.

Return Value Type

map(k2, v) type.

Example

Example 1: Adds 1 to integer keys.
Query and analysis statements.
* | SELECT transform_keys(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']), (k, v) -> k + 1)
Query and analysis results.
{"2":"a","3":"b","4":"c"}
Example 2: Uses the square of the value as the new key.
Query and analysis statements.
* | SELECT transform_keys(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]), (k, v) -> v * v)
Query and analysis results.
{"1":1,"4":2,"9":3}
Example 3: Concatenates the key and value as the new key.
Query and analysis statements.
* | SELECT transform_keys(MAP(ARRAY['a', 'b'], ARRAY[1, 2]), (k, v) -> k || CAST(v AS VARCHAR))
Query and analysis results.
{"a1":1,"b2":2}
Example 4: Uses another map to map keys.
Query and analysis statements.
* | SELECT transform_keys(
    MAP(ARRAY[1, 2], ARRAY[1.0, 1.4]),
    (k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k]
)
Query and analysis results.
{"one":1,"two":1.4}

transform_values function

The transform_values function is used to apply a Lambda function to each entry of a map to transform the values, generating a new map. The keys remain unchanged.

Syntax

transform_values(map(k, v1), function(k, v1, v2))

Parameter Description

Parameter
Description
map(k, v1)
The parameter value is of MAP type, and v1 is of primitive type.
function(k, v1, v2)
Lambda function that takes key k and original value v1 as parameters, returning a new value v2. Syntax: (k, v) -> new value expression.

Return Value Type

map(k, v2) type.

Example

Example 1: Adds the corresponding key to the value.
Query and analysis statements.
* | SELECT transform_values(MAP(ARRAY[1, 2, 3], ARRAY[10, 20, 30]), (k, v) -> v + k)
Query and analysis results.
{"1":11,"2":22,"3":33}
Example 2: Uses the square of the key as the new value.
Query and analysis statements.
* | SELECT transform_values(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']), (k, v) -> k * k)
Query and analysis results.
{"1":1,"2":4,"3":9}
Example 3: Concatenates the key and value as the new value.
Query and analysis statements.
* | SELECT transform_values(MAP(ARRAY['a', 'b'], ARRAY[1, 2]), (k, v) -> k || CAST(v AS VARCHAR))
Query and analysis results.
{"a":"a1","b":"b2"}
Example 4: Use another map to map and concatenate values.
Query and analysis statements.
* | SELECT transform_values(
    MAP(ARRAY[1, 2], ARRAY[1.0, 1.4]),
    (k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k] || '_' || CAST(v AS VARCHAR)
)
Query and analysis results.
{"1":"one_1.0","2":"two_1.4"}

Previous Topic: Lambda FunctionNext Topic: Conditional Expressions

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback