Simple Log Service:Map functions and operators

Subscript operator

The subscript operator returns the value of a key from a map.

Syntax

[x]

Parameters

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

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

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

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

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

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

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

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

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

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

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

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