All Products
Search
Document Center

Simple Log Service:Array functions and operators

Last Updated:Dec 26, 2023

This topic describes the syntax of array functions and operators. This topic also provides examples on how to use the functions and operators.

The following table describes the array 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

Subscript operator

[x]

Returns the element whose index is x from an array. This operator is equivalent to the element_at function.

array_agg function

array_agg(x)

Returns an array that is created from all values in the x column.

array_distinct function

array_distinct(x)

Removes duplicate elements from an array.

array_except function

array_except(x, y)

Returns the difference of two arrays.

array_intersect function

array_intersect(x, y)

Returns the intersection of two arrays.

array_join function

array_join(x, delimiter)

Concatenates the elements of an array into a string by using a specified delimiter. If the array contains a null element, the null element is ignored.

Important

The array_join function can return a maximum of 1 KB of data. If the size of the returned data exceeds 1 KB, the excess data is truncated.

array_join(x, delimiter, null_replacement)

Concatenates the elements of an array into a string by using a specified delimiter. If the array contains a null element, the null element is replaced by the value of the null_replacement parameter.

Important

The array_join function can return a maximum of 1 KB of data. If the size of the returned data exceeds 1 KB, the excess data is truncated.

array_max function

array_max(x)

Returns the maximum value in an array.

array_min function

array_min(x)

Returns the minimum value in an array.

array_position function

array_position(x, element)

Returns the index of a specified element in an array. The index starts from 1. If the specified element does not exist, the function returns 0.

array_remove function

array_remove(x, element)

Removes a specified element from an array.

array_sort function

array_sort(x)

Sorts the elements in an array in ascending order. If the array contains a null element, the null element is placed at the end.

array_transpose function

array_transpose(x)

Transposes a matrix and returns a new two-dimensional array that is created from the elements in the matrix. The elements are located by using the same indexes.

array_union function

array_union(x, y)

Returns the union of two arrays.

cardinality function

cardinality(x)

Returns the number of elements in an array.

concat function

concat(x, y…)

Concatenates multiple arrays into one array.

contains function

contains(x, element)

Checks whether an array contains a specified element. If the array contains the specified element, the function returns true.

element_at function

element_at(x, y)

Returns the element whose index is y from an array.

filter function

filter(x, lambda_expression)

Filters elements in an array based on a lambda expression and returns elements that match the lambda expression.

flatten function

flatten(x)

Transforms a two-dimensional array into a one-dimensional array.

reduce function

reduce(x, lambda_expression)

Returns the sum of the elements in an array based on a lambda expression.

reverse function

reverse(x)

Reverses the elements in an array.

sequence function

sequence(x, y)

Returns an array of elements within a specified range. The elements are consecutive and incremental. The default incremental step is 1.

sequence(x, y, step)

Returns an array of elements within a specified range. The elements are consecutive and incremental. The incremental step is a custom value.

shuffle function

shuffle(x)

Shuffles the elements in an array.

slice function

slice(x, start, length)

Returns a subset of an array.

transform function

transform(x, lambda_expression)

Transforms each element in an array by using a lambda expression.

zip function

zip(x, y...)

Merges multiple arrays into a two-dimensional array. Elements that have the same index in the input arrays form a new array in the two-dimensional array.

zip_with function

zip_with(x, y, lambda_expression)

Merges two arrays into a single array by using a lambda expression.

Subscript operator

The subscript operator is used to return the element whose index is x from an array. This operator is equivalent to the element_at function.

Syntax

[x]

Parameters

Parameter

Description

x

The index of an element in an array. The index starts from 1. The value of this parameter is of the bigint type.

Return value type

The data type of the specified element.

Examples

Obtain the first element from the value of the number field.

  • Sample field

    number:[49,50,45,47,50]
  • Query statement

    * | SELECT cast(json_parse(number) as array(bigint)) [1]
  • Query and analysis resultSubscript operator

array_agg function

The array_agg function is used to return an array that is created from all values in the x column.

Syntax

array_agg (x)

Parameters

Parameter

Description

x

The value of this parameter is of an arbitrary data type.

Return value type

The array type.

Examples

Obtain an array that is created from all values in the status column.

  • Query statement

    * | SELECT array_agg(status) AS array
  • Query and analysis resultarray_agg

array_distinct function

The array_distinct function is used to remove duplicate elements from an array.

Syntax

array_distinct(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Return value type

The array type.

Examples

Remove duplicate elements from the value of the number field.

  • Sample field

    number:[49,50,45,47,50]
  • Query statement

    *| SELECT array_distinct(cast(json_parse(number) as array(bigint)))
  • Query and analysis resultarray_distinct

array_except function

The array_except function is used to return the difference of two arrays.

Syntax

array_except(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 array type.

Examples

Obtain the difference of the [1,2,3,4,5] and [1,3,5,7] arrays.

  • Query statement

    * | SELECT array_except(array[1,2,3,4,5],array[1,3,5,7])
  • Query and analysis resultarray_except

array_intersect function

The array_intersect function is used to return the intersection of two arrays.

Syntax

array_intersect(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 array type.

Examples

Obtain the intersection of the [1,2,3,4,5] and [1,3,5,7] arrays.

  • Query statement

    * | SELECT array_intersect(array[1,2,3,4,5],array[1,3,5,7])
  • Query and analysis resultarray_intersect

array_join function

The array_join function is used to concatenate the elements of an array into a string by using a specified delimiter.

Syntax

  • The following syntax of the array_join function is used to concatenate the elements of an array into a string by using a specified delimiter. If the array contains a null element, the null element is ignored.

    array_join(x, delimiter)
  • The following syntax of the array_join function is used to concatenate the elements of an array into a string by using a specified delimiter. If the array contains a null element, the null element is replaced by the value of the null_replacement parameter.

    array_join(x, delimiter,null_replacement)

Parameters

Parameter

Description

x

The value of this parameter is of an arbitrary array type.

delimiter

The delimiter that is used to connect elements. You can specify a string for this parameter.

null_replacement

The string that is used to replace a null element.

Return value type

The varchar type.

Examples

Concatenate the elements of the [null, 'Log','Service'] array into a string by using space characters and replace the null element with Alicloud.

  • Query statement

    * | SELECT array_join(array[null,'Log','Service'],' ','Alicloud')
  • Query and analysis resultarray_join

array_max function

The array_max function is used to return the maximum value in an array.

Syntax

array_max(x) 

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Important

If an array contains a null element, the function returns null.

Return value type

The data type of elements in the parameter value.

Examples

Obtain the maximum value in an array.

  • Sample field

    number:[49,50,45,47,50]
  • Query statement

    *| SELECT array_max(try_cast(json_parse(number) as array(bigint))) AS max_number
  • Query and analysis resultarray_max

array_min function

The array_min function is used to return the minimum value in an array.

Syntax

array_min(x) 

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Important

If an array contains a null element, the function returns null.

Return value type

The data type of elements in the parameter value.

Examples

Obtain the minimum value in an array.

  • Sample field

    number:[49,50,45,47,50]
  • Query statement

    *| SELECT array_min(try_cast(json_parse(number) as array(bigint))) AS min_number
  • Query and analysis resultarray_min

array_position function

The array_position function is used to return the index of a specified element in an array. The index starts from 1. If the specified element does not exist, the function returns 0.

Syntax

array_position(x, element)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

element

The value of this parameter is the element whose index you want to obtain.

Note

If the element is null, the function returns null.

Return value type

The bigint type.

Examples

Obtain the index of 45 from the [49,45,47] array.

  • Query statement

    * | SELECT array_position(array[49,45,47],45)
  • Query and analysis resultarray_position

array_remove function

The array_remove function is used to remove a specified element from an array.

Syntax

array_remove(x, element)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

element

The value of this parameter is the element that you want to remove.

Note

If the element is null, the function returns null.

Return value type

The array type.

Examples

Remove 45 from the [49,45,47] array.

  • Query statement

    * | SELECT array_remove(array[49,45,47],45)
  • Query and analysis resultarray_remove function

array_sort function

The array_sort function is used to sort the elements in an array in ascending order. If the array contains a null element, the null element is placed at the end.

Syntax

array_sort(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Return value type

The array type.

Examples

Sort the elements in the ['b', 'd', null, 'c', 'a'] array in ascending order.

  • Query statement

    * | SELECT array_sort(array['b','d',null,'c','a'])
  • Query and analysis resultarray_sort

array_transpose function

The array_transpose function is used to transpose a matrix and return a new two-dimensional array that is created from the elements in the matrix. The elements are located by using the same indexes.

Syntax

array_transpose(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array(double) type.

Return value type

The array(double) type.

Examples

Create a two-dimensional array from elements that are located by using the same indexes in a different two-dimensional array. For example, in the [0,1,2,3], [10,19,18,17], and [9,8,7] arrays, 0, 10, and 9 are all located by using the index 1. This way, the new array [0.0,10.0,9.0] is formed.

  • Query statement

    * | SELECT array_transpose(array[array[0,1,2,3],array[10,19,18,17],array[9,8,7]])
  • Query and analysis resultarray_transpose

array_union function

The array_union function is used to return the union of two arrays.

Syntax

array_union(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 array type.

Examples

Obtain the union of the [1,2,3,4,5] and [1,3,5,7] arrays.

  • Query statement

    * | SELECT array_union(array[1,2,3,4,5],array[1,3,5,7])
  • Query and analysis resultarray_union

cardinality function

The cardinality function is used to return the number of elements in an array.

Syntax

cardinality(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Return value type

The bigint type.

Examples

Obtain the number of elements in the value of the number field.

  • Sample field

    number:[49,50,45,47,50]
  • Query statement

    *| SELECT cardinality(cast(json_parse(number) as array(bigint)))
  • Query and analysis resultcardinality

concat function

The concat function is used to concatenate multiple arrays into one array.

Syntax

concat(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 array type.

Examples

Concatenate the ['red','blue'] and ['yellow','green'] arrays into one array.

  • Query statement

    * | SELECT concat(array['red','blue'],array['yellow','green'])
  • Query and analysis resultconcat-array

contains function

The contains function is used to check whether an array contains a specified element. If the array contains the specified element, the function returns true.

Syntax

contains(x, element)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

element

The value of this parameter is the element that you want to check.

Return value type

The Boolean type.

Examples

Check whether the value of the region field contains cn-beijing.

  • Sample field

    region:["cn-hangzhou","cn-shanghai","cn-beijing"]
  • Query statement

    *| SELECT contains(cast(json_parse(region) as array(varchar)),'cn-beijing')
  • Query and analysis resultcontains

element_at function

The element_at function is used to return the element whose index is y from an array.

Syntax

element_at(x, y)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

y

The index of an element in an array. The index starts from 1. The value of this parameter is of the bigint type.

Return value type

An arbitrary data type.

Examples

Obtain the second element from the value of the number field.

  • Sample field

    number:[49,50,45,47,50]
  • Query statement

    * |
    SELECT
      element_at(cast(json_parse(number) AS array(varchar)), 2)
  • Query and analysis result element_at

filter function

The filter function is used to filter elements in an array based on a lambda expression and return elements that match the lambda expression.

Syntax

filter(x, lambda_expression)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

lambda_expression

The lambda expression. For more information, see Lambda expressions.

Return value type

The array type.

Examples

Obtain the elements that are greater than 0 from the [5,-6,null,7] array by using the lambda expression x -> x > 0.

  • Query statement

    * | SELECT filter(array[5,-6,null,7],x -> x > 0)
  • Query and analysis resultfilter

flatten function

The flatten function is used to transform a two-dimensional array into a one-dimensional array.

Syntax

flatten(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Return value type

The array type.

Examples

Transform the two-dimensional array [array[1,2,3,4],array[5,2,2,4] into a one-dimensional array.

  • Query statement

    * | SELECT flatten(array[array[1,2,3,4],array[5,2,2,4]])
  • Query and analysis resultflatten

reduce function

The reduce function is used to return the sum of the elements in an array based on a lambda expression.

Syntax

reduce(x, lambda_expression)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

lambda_expression

The lambda expression. For more information, see Lambda expressions.

Return value type

The bigint type.

Examples

Obtain the sum of the elements in the [5,20,50] array.

  • Query statement

    * | SELECT reduce(array[5,20,50],0,(s, x) -> s + x, s -> s)
  • Query and analysis resultreduce

reverse function

The reverse function is used to reverse the elements in an array.

Syntax

reverse(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Return value type

The array type.

Examples

Reverse the elements in the [1,2,3,4,5] array.

  • Query statement

    * | SELECT reverse(array[1,2,3,4,5])
  • Query and analysis resultreverse

sequence function

The sequence function is used to return an array of elements within a specified range. The elements are consecutive and incremental.

Syntax

  • The following syntax of the sequence function uses the default incremental step. The default step is 1.

    sequence(x, y)
  • The following syntax of the sequence function uses a custom incremental step:

    sequence(x, y, step)

Parameters

Parameter

Description

x

The value of this parameter is of the bigint or timestamp type. UNIX timestamps and date and time expressions are supported.

y

The value of this parameter is of the bigint or timestamp type. UNIX timestamps and date and time expressions are supported.

step

The incremental step.

If the values of the x and y parameters are date and time expressions, the value of the step parameter is in one of the following formats:

  • interval ' n' year to month: The incremental step is n years.

  • interval 'n' day to second: The incremental step is n days.

Return value type

The array type.

Examples

  • Example 1: Obtain the even numbers within the range from 0 to 10.

    • Query statement

      * | SELECT sequence(0,10,2)
    • Query and analysis resultsequence

  • Example 2: Obtain the dates within the range from 2017-10-23 to 2021-08-12 at the incremental step of 1 year.

    • Query statement

      ww* | SELECT  sequence(from_unixtime(1508737026),from_unixtime(1628734085),interval '1' year to month )
    • Query and analysis resultsequence

  • Example 3: Obtain the UNIX timestamps within the range from 1628733298 to 1628734085 at the incremental step of 60 seconds.

    • Query statement

      * | SELECT  sequence(1628733298,1628734085,60)
    • Query and analysis resultsequence

shuffle function

The shuffle function is used to shuffle the elements in an array.

Syntax

shuffle(x)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

Return value type

The array type.

Examples

Shuffle the elements in the [1,2,3,4,5] array.

  • Query statement

    *| SELECT shuffle(array[1,2,3,4,5])
  • Query and analysis resultshuffle

slice function

The slice function is used to return a subset of an array.

Syntax

slice(x, start, length)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

start

The index at which Simple Log Service starts to extract elements.

  • If the value of the start parameter is negative, Simple Log Service starts to extract elements from the end of the array.

  • If the value of the start parameter is a positive number, Simple Log Service starts to extract elements from the beginning of the array.

length

The number of elements that you want to include in the subset.

Return value type

The array type.

Examples

Obtain a subset of the [1,2,4,5,6,7,7] array from the third element with two elements.

  • Query statement

    * | SELECT slice(array[1,2,4,5,6,7,7],3,2)
  • Query and analysis resultslice

transform function

The transform function is used to transform each element in an array by using a lambda expression.

Syntax

transform(x, lambda_expression)

Parameters

Parameter

Description

x

The value of this parameter is of the array type.

lambda_expression

The lambda expression. For more information, see Lambda expressions.

Return value type

The array type.

Examples

Add 1 to each element in the [5,6] array and return a new array.

  • Query statement

    * | SELECT transform(array[5,6],x -> x + 1)
  • Query and analysis resulttransform

zip function

The zip function is used to merge multiple arrays into a two-dimensional array. Elements that have the same index in the input arrays form a new array in the two-dimensional array.

Syntax

zip(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 array type.

Examples

Merge the [1, 2,3], ['1b', null, '3b'], and [1, 2,3] arrays into a two-dimensional array.

  • Query statement

    * | SELECT zip(array[1,2,3], array['1b',null,'3b'],array[1,2,3])
  • Query and analysis resultzip

zip_with function

The zip_with function is used to merge two arrays into a single array by using a lambda expression.

Syntax

zip_with(x, y, lambda_expression)

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.

lambda_expression

The lambda expression. For more information, see Lambda expressions.

Return value type

The array type.

Examples

Use the lambda expression (x, y) -> x + y to add the corresponding elements in the [1, 2] and [3, 4] arrays and return a new array.

  • Query statement

    SELECT zip_with(array[1,2], array[3,4],(x,y) -> x + y)
  • Query and analysis resultzip_with