All Products
Search
Document Center

Simple Log Service:Map functions and operators

Last Updated:Jan 29, 2024

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.

Important 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

Supported in SQL

Supported in SPL

Subscript operator

[x]

Returns 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 from 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(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_filter(x, lambda_expression)

Filters elements in a map based on a lambda expression.

×

map_keys function

map_keys(x)

Returns an array that consists of all keys in a map.

×

map_values function

map_values(x)

Returns an array that consists of all values in a map.

×

multimap_agg function

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 resultscardinality

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 resultselement_at

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 resultshistogram

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 resultshistogram_u

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 resultsmap

  • Example 2: Return an empty map.

    • Query statement

      *| SELECT map()
    • Query and analysis resultsmap

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 resultsmap_agg

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 resultsmap_concat

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 resultsmap_filter

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 resultsmap_keys

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 resultsmap_values

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 resultsmultimap_agg