This topic describes the syntax of map functions and operators. This topic also provides examples on how to use the functions and operators.
The following table describes the map functions and operators that are supported by Simple Log Service.
Function | Syntax | Description | Supported in SQL | Supported in SPL |
[x] | Returns the value of a key from a map. | √ | × | |
cardinality(x) | Returns the size of a map. | √ | × | |
element_at(x, key) | Returns the value of a key from a map. | √ | × | |
histogram(x) | Groups query and analysis results and returns data in the JSON format. | √ | × | |
histogram_u(x) | Groups query and analysis results and returns data in multiple rows and multiple columns. | √ | × | |
map() | Returns an empty map. | √ | × | |
map(x, y) | Returns a map that is created by using two arrays. | √ | × | |
map_agg(x, y) | Returns a map that is created by using x and y. x is the key in the map. y is the value of the key in the map. If y has multiple values, a random value is extracted as the value of the key. | √ | × | |
map_concat(x, y...) | Returns the union of multiple maps. | √ | × | |
map_filter(x, lambda_expression) | Filters elements in a map based on a lambda expression. | √ | × | |
map_keys(x) | Returns an array that consists of all keys in a map. | √ | × | |
map_values(x) | Returns an array that consists of all values in a map. | √ | × | |
multimap_agg(x, y) | Returns a multimap that is created by using x and y. x is a key in the multimap. y is the value of the key in the multimap. The value is of the array type. If y has multiple values, all the values are extracted as the values of the key. | √ | × |
Subscript operator
The subscript operator returns the value of a key from a map.
Syntax
[x]
Parameters
Parameter | Description |
x | The value of this parameter is of the varchar type. |
Return value type
An arbitrary data type.
Examples
In a log that is transformed by a data transformation job, the value of the etl_context field is of the map type. You can use the subscript operator to obtain the value of the project key from the value of the etl_context field.
Sample fields
etl_context: { project:"datalab-148****6461-cn-chengdu" logstore:"internal-etl-log" consumer_group:"etl-83****4d1965" consumer:"etl-b2d40ed****c8d6-291294" shard_id:"0" }
Query statement
* | SELECT try_cast(json_parse(etl_context) AS map(varchar, varchar))['project']
Query and analysis results
cardinality function
The cardinality function returns the size of a map.
Syntax
cardinality(x)
Parameters
Parameter | Description |
x | The value of this parameter is of the map type. |
Return value type
The bigint type.
Examples
Use the histogram function to obtain the number of requests for each request method. Then, use the cardinality function to obtain the number of request methods.
Query statement
* | SELECT histogram(request_method) AS request_method, cardinality(histogram(request_method)) AS "kinds"
Query and analysis results
element_at function
The element_at function returns the value of a key from a map.
Syntax
element_at(x, key)
Parameters
Parameter | Description |
x | The value of this parameter is of the map type. |
key | The value of this parameter is a key in a map. |
Return value type
An arbitrary data type.
Examples
Use the histogram function to obtain the number of requests for each request method. Then, use the element_at function to obtain the value of the DELETE field.
Query statement
* | SELECT histogram(request_method) AS request_method, element_at(histogram(request_method),'DELETE') AS "count"
Query and analysis results
histogram function
The histogram function groups query and analysis results and returns data in the JSON format. This function is equivalent to * | SELECT count(*) GROUP BY x
.
Syntax
histogram(x)
Parameters
Parameter | Description |
x | The value of this parameter is of an arbitrary data type. |
Return value type
The map type.
Examples
Use the histogram function to obtain the number of requests for each request method.
Query statement
* | SELECT histogram(request_method) AS request_method
Query and analysis results
histogram_u function
The histogram_u function groups query and analysis results and returns data in multiple rows and multiple columns.
Syntax
histogram_u(x)
Parameters
Parameter | Description |
x | The value of this parameter is of an arbitrary data type. |
Return value type
The bigint type.
Examples
Use the histogram_u function to obtain the number of requests for each request method, and then display the number on a column chart.
Query statement
*|SELECT histogram_u(request_method) as request_method
Query and analysis results
map function
The map function returns an empty map or returns a map that is created by using two arrays.
Syntax
The following syntax of the map function returns an empty map:
map()
The following syntax of the map function returns a map that is created by using two arrays:
map(x,y)
Parameters
Parameter | Description |
x | The value of this parameter is of the array type. |
y | The value of this parameter is of the array type. |
Return value type
The map type.
Examples
Example 1: The class field specifies classes. The number field specifies the number of students in the classes. The values of the two fields are of the array type. Use the map function to create a map based on the values of the two fields. In the returned result, each class is mapped to the number of students in the class.
Sample fields
class:["class01","class02","class03","class04","class05"] number:[49,50,45,47,50]
Query statement
* | SELECT map(try_cast(json_parse(class) AS array(varchar)) ,try_cast(json_parse(number) AS array(bigint)))
Query and analysis results
Example 2: Return an empty map.
Query statement
*| SELECT map()
Query and analysis results
map_agg function
The map_agg function returns a map that is created by using x and y. x is a key in the map. y is the value of the key in the map. If y has multiple values, a random value is extracted as the value of the key.
Syntax
map_agg(x, y)
Parameters
Parameter | Description |
x | The value of this parameter is of an arbitrary data type. |
y | The value of this parameter is of an arbitrary data type. |
Return value type
The map type.
Examples
Extract the values of the request_method and request_time fields, and then use the extracted values to create a map. The value of the request_method field is a key in the map. The value of the request_time field is the value of the key in the map.
Sample fields
request_method:POST request_time:80
Query statement
* | SELECT map_agg(request_method,request_time)
Query and analysis results
map_concat function
The map_concat function returns the union of multiple maps.
Syntax
map_concat(x, y)
Parameters
Parameter | Description |
x | The value of this parameter is of the map type. |
y | The parameter can be of the map data type. |
Return value type
The map type.
Examples
In a log that is transformed by a data transformation job, the values of the etl_context and progress fields are of the map type. You can use the map_concat function to obtain the union of the field values.
Sample fields
etl_context: { project:"datalab-148****6461-cn-chengdu" logstore:"internal-etl-log" consumer_group:"etl-83****4d1965" consumer:"etl-b2d40ed****c8d6-291294" shard_id:"0" } progress: { accept:3 dropped:0 delivered:3 failed:0 }
Query statement
* | SELECT map_concat( cast ( json_parse(etl_context) AS map(varchar, varchar) ), cast (json_parse(progress) AS map(varchar, varchar)) )
Query and analysis results
map_filter function
The map_filter function filters elements in a map based on a lambda expression.
Syntax
map_filter(x, lambda_expression)
Parameters
Parameter | Description |
x | The value of this parameter is of the map type. |
lambda_expression_expression | The lambda expression. For more information, see Lambda expressions. |
Return value type
The map type.
Examples
Create a map that does not contain null values from two arrays by using the lambda expression (k, v) -> v is not null
.
Query statement
* | SELECT map_filter(map(array[10, 20, 30], array['a', NULL, 'c']), (k, v) -> v is not null)
Query and analysis results
map_keys function
The map_keys function returns an array that consists of all keys in a map.
Syntax
map_keys(x)
Parameters
Parameter | Description |
x | The value of this parameter is of the map type. |
Return value type
The array type.
Examples
In a log that is transformed by a data transformation job, the value of the etl_context field is of the map type. You can use the map_keys function to obtain all keys from the value of the etl_context field.
Sample fields
etl_context: { project:"datalab-148****6461-cn-chengdu" logstore:"internal-etl-log" consumer_group:"etl-83****4d1965" consumer:"etl-b2d40ed****c8d6-291294" shard_id:"0" }
Query statement
* | SELECT map_keys(try_cast(json_parse(etl_context) AS map(varchar, varchar)))
Query and analysis results
map_values function
The map_values function returns an array that consists of all values in a map.
Syntax
map_values(x)
Parameters
Parameter | Description |
x | The value of this parameter is of the map type. |
Return value type
The array type.
Examples
In a log that is transformed by a data transformation job, the value of the etl_context field is of the map type. You can use the map_values function to obtain the values of all keys from the value of the etl_context field.
Sample fields
etl_context: { project:"datalab-148****6461-cn-chengdu" logstore:"internal-etl-log" consumer_group:"etl-83****4d1965" consumer:"etl-b2d40ed****c8d6-291294" shard_id:"0" }
Query statement
* | SELECT map_values(try_cast(json_parse(etl_context) AS map(varchar, varchar)))
Query and analysis results
multimap_agg function
The multimap_agg function returns a multimap that is created by using x and y. x is a key in the multimap. y is the value of the key in the multimap. The value is of the array type. If y has multiple values, all the values are extracted as the values of the key.
Syntax
multimap_agg(x, y)
Parameters
Parameter | Description |
x | The value of this parameter is of an arbitrary data type. |
y | The value of this parameter is of an arbitrary data type. |
Return value type
The map type.
Examples
Extract all values of the request_method and request_time fields, and then use the extracted values to create a multimap. The value of the request_method field is a key in the multimap. The value of the request_time field is the value of the key in the multimap. The value of the key is of the array type.
Sample fields
request_method:POST request_time:80
Query statement
* | SELECT multimap_agg(request_method,request_time)
Query and analysis results