Map functions and operators - Log Service - Alibaba Cloud Documentation Center

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 Log Service.

Notice If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.


Function	Syntax	Description
Subscript operator	[`x`]	Is used to retrieve the value of a key from a map.
cardinality function	cardinality(`x`)	Returns the size of a map.
element_at function	element_at(`x`, `key`)	Returns the value of a key in a map.
histogram function	histogram(`x`)	Groups query and analysis results and returns data in the JSON format.
histogram_u function	histogram_u(`x`)	Groups query and analysis results and returns data in multiple rows and multiple columns.
map() function	map()	Returns an empty map.
map function	map(`x`, `y`)	Returns a map that is created by using two arrays.
map_agg function	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 function	map_concat(`x`,`y`...)	Returns the union of multiple maps.
map_filter function	map_filtermap_filter(`x`,`lambda_expression`)	Filters elements in a map based on a lambda expression and returns a new map.
map_keys function	map_keys(`x`)	Returns an array that consists of all the keys of a map.
map_values function	map_values(`x`)	Returns an array that consists of all the values of a map.
multimap_agg function	multimap_agg(`x`,`y`)	Returns a multimap that is created by using `x` and `y`. `x` is the key in the multimap. `y` is the value of the key in the multimap, and 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 is used to retrieve 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 of a data transformation task, the etl_context field has a value of the map type. You can use the subscript operator to retrieve the value of the project key from the value of the etl_context field.

Sample field

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 in 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 numbers in 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.

Syntax

map()

Return value type

The map type.

Examples

Use the map() function to obtain an empty map.

Query statement
```
*| SELECT map()
```
Query and analysis results

map function

The map function returns a map that is created by using two arrays.

Syntax

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

The class field specifies classes. The number field specifies the numbers 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 field

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

map_agg function

The map_agg function 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.

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 request_method is the key in the map. The value of request_time is the value of the key in the map.

Sample field
```
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 value of this parameter is of the map type.

Return value type

The map type.

Examples

In a log of a data transformation task, the etl_context and progress fields have values of the map type. You can use the map_concat function to obtain the union of the values.

Sample field

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 and returns a new map.

Syntax

map_filter(x,lambda_expression)

Parameters


Parameter	Description
`x`	The value of this parameter is of the map type.
`lambda_expression_expression`	The value of this parameter is a lambda expression. For more information, see Lambda functions.

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 the keys of 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 of a data transformation task, the etl_context field has a value of the map type. You can use the map_keys function to obtain all the keys in the value of the etl_context field.

Sample field

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 the values of 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 of a data transformation task, the etl_context field has a value of the map type. You can use the map_values function to obtain all the values of keys in the value of the etl_context field.

Sample field

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 the key in the multimap. y is the value of the key in the multimap, and 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 the values of the request_method and request_time fields and then use the extracted values to create a multimap. The value of request_method is the key in the multimap. The value of request_time is the value of the key in the multimap, and the value of the key is of the array type.

Sample field
```
request_method:POST
request_time:80
```

Query statement

* | SELECT multimap_agg(request_method,request_time)

Query and analysis results