Array functions and operators: syntax and examples - Simple Log Service

Function name	Syntax	Description	SQL	SPL
subscript operator	[x]	Returns the element at index x in an array. This is equivalent to the `element_at` function.	√	×
array_agg function	array_agg(x)	Returns an array of all values in x.	√	×
array_distinct function	array_distinct(x)	Removes duplicate elements from an array.	√	√
array_except function	array_except(x, y)	Returns the difference between two arrays.	√	√
array_intersect function	array_intersect(x, y)	Returns the intersection of two arrays.	√	√
array_join function	array_join(x, delimiter)	Joins the elements of an array into a string, using the specified delimiter. Null elements are ignored. Important The array_join function returns a result of up to 1 KB. Data that exceeds the limit is truncated.	√	√
array_join function	array_join(x, delimiter, null_replacement)	Joins the elements of an array into a string, using the specified delimiter. Null elements are replaced with the null_replacement value. Important The array_join function returns a result of up to 1 KB. Data that exceeds the limit 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 1-based index of a specified element. Returns 0 if the element does not exist.	√	√
array_remove function	array_remove(x, element)	Removes a specified element from an array.	√	√
array_sort function	array_sort(x)	Sorts the elements of an array in ascending order. Null elements are placed at the end.	√	√
array_transpose function	array_transpose(x)	Transposes a two-dimensional array (an array of arrays) by swapping its rows and columns.	√	×
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 a single array.	√	×
contains function	contains(x, element)	Returns `true` if the array contains the specified element, and `false` otherwise.	√	×
element_at function	element_at(x, y)	Returns the element at index y in an array.	√	×
filter function	filter(x, lambda_expression)	Filters an array using a lambda expression, returning only the elements that satisfy the expression.	√	√
flatten function	flatten(x)	Converts a two-dimensional array into a one-dimensional array.	√	×
reduce function	reduce(x, lambda_expression)	Reduces an array to a single value by applying a lambda expression to its elements.	√	√
reverse function	reverse(x)	Reverses the order of elements in an array.	√	√
sequence function	sequence(x, y)	Returns an array of consecutive, increasing values within a specified range. The default step is 1.	√	√
sequence function	sequence(x, y, step)	Returns an array of consecutive, increasing values within a specified range, with a custom step.	√	√
shuffle function	shuffle(x)	Shuffles the elements of 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 using a lambda expression.	√	√
zip function	zip(x, y...)	Merges multiple arrays into a single two-dimensional array. Elements at the same index from the input arrays form a new subarray.	√	√
zip_with function	zip_with(x, y, lambda_expression)	Merges two arrays into a single array by applying a lambda expression to each pair of corresponding elements.	√	×

Subscript operator

The subscript operator returns the element at index x in an array. It is equivalent to the element_at function.

Syntax

[x]

Parameters

Parameter	Description
x	The 1-based index of the element to retrieve from an array. The value must be a `bigint`.

Return value type

The return value has the same data type as the array elements.

Examples

This example returns the first element from the number field.

Sample field
```
number:[49,50,45,47,50]
```

Query statement

* | SELECT cast(json_parse(number) as array(bigint)) [1]

The query returns 49.

Array_agg function

The array_agg function returns an array of all values in x.

Syntax

array_agg (x)

Parameters

Parameter	Description
x	The parameter can be any data type.

Return value type

array

Examples

Returns the values of the status field as an array.

Query statement
```
* | SELECT array_agg(status) AS array
```
Query results: The array field contains an array of HTTP status codes (such as 200, 202, 204, 303, 304, 401, 402, and 501).

Array_distinct function

Removes duplicate elements from an array.

Syntax

array_distinct(x)

Parameters

Parameter	Description
x	Must be an array.

Return value type

array

Examples

Removes duplicate elements from the number field.

Sample field
```
number:[49,50,45,47,50]
```

Query statement

*| SELECT array_distinct(cast(json_parse(number) as array(bigint)))

The query result is [49,50,45,47]. The duplicate element 50 is removed from the original array.

array_except function

The array_except function returns the difference between two arrays.

Syntax

array_except(x, y)

Parameters

Parameter	Description
x	Must be an array.
y	Must be an array.

Return value type

array

Examples

Calculate the difference between the arrays [1,2,3,4,5] and [1,3,5,7].

Query statement

* | SELECT array_except(array[1,2,3,4,5],array[1,3,5,7])

The query returns [2,4].

array_intersect function

Calculates the intersection of two arrays.

Syntax

array_intersect(x, y)

Parameters

Parameter	Description
x	An array.
y	An array.

Return value type

array

Examples

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

Query and analysis statement

* | SELECT array_intersect(array[1,2,3,4,5],array[1,3,5,7])

The query and analysis result is [1,3,5].

array_join function

Concatenates array elements into a string using a specified delimiter.

Syntax

Concatenates array elements into a string using a delimiter. Null elements are ignored.
```
array_join(x, delimiter)
```
Concatenates array elements into a string using a delimiter. It replaces null elements with null_replacement.
```
array_join(x, delimiter,null_replacement)
```

Parameters

Parameter	Description
x	The input array.
delimiter	The string used to separate array elements.
null_replacement	The string used to replace null elements.

Return value type

varchar.

Examples

This example concatenates the elements of the array [null, 'Log', 'Service'] into a string using a space as the delimiter. It replaces the null element with 'Alicloud'.

Query

* | SELECT array_join(array[null,'Log','Service'],' ','Alicloud')

Results: The value of the _col0 column is Alicloud Log Service.

Array_max function

Returns the maximum value in an array.

Syntax

array_max(x)

Parameters

Parameter	Description
x	The input must be an array. Important If the array contains a null element, the function returns null.

Return value type

The returned value has the same data type as the array elements.

Examples

This example finds the maximum value in an array of numbers.

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 results: The max_number value is 50.

Array_min function

Returns the minimum value in an array.

Syntax

array_min(x)

Parameters

Parameter	Description
x	The input array. Important If the array contains a null element, the function returns null.

Return value type

The return value has the same data type as the array elements.

Examples

Find the minimum value in an array of numbers.

Sample field
```
number:[49,50,45,47,50]
```

Query statement

*| SELECT array_min(try_cast(json_parse(number) as array(bigint))) AS min_number

The query returns 45.

Array_position function

The array_position function returns the 1-based index of an element in an array. It returns 0 if the element is not found.

Syntax

array_position(x, element)

Parameters

Parameter	Description
x	The array to search.
element	The element to find in the array. Note If the element to find is null, the function returns null.

Return value type

Returns a bigint value.

Examples

Finds the index of 45 in the array [49,45,47].

Query and analysis statement

* | SELECT array_position(array[49,45,47],45)

The result is 2, indicating that the element 45 is at index 2 in the array.

array_remove function

The array_remove function removes all occurrences of a specified element from an array.

Syntax

array_remove(x, element)

Parameters

Parameter	Description
x	The input array.
element	The element to remove from the array. Note If element is null, the function returns null.

Return value type

Returns an array.

Examples

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

Query and analysis statement

* | SELECT array_remove(array[49,45,47],45)

The result is [49,47].

array_sort function

The array_sort function sorts the elements of an array into ascending order, placing any null elements at the end.

Syntax

array_sort(x)

Parameters

Parameter	Description
x	The array to sort.

Return value type

Returns an array.

Examples

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

Query statement

* | SELECT array_sort(array['b','d',null,'c','a'])

The result is ["a","b","c","d",null]. The elements are sorted in ascending alphabetical order, and the null value is placed at the end.

array_transpose function

The array_transpose function transposes a two-dimensional array (a matrix). It creates a new two-dimensional array by grouping elements that share the same index across the inner arrays.

Syntax

array_transpose(x)

Parameters

Parameter	Description
x	The input is a two-dimensional array of type `array(double)`.

Return value type

array(double)

Examples

The function groups elements at the same index in each inner array to form new inner arrays. For example, from the arrays [0,1,2,3], [10,19,18,17], and [9,8,7], the function takes the first elements (0, 10, and 9) and groups them into the new inner array [0.0,10.0,9.0].

Query and analysis statement

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

Query and analysis results: The value in the _col0 column is [[0.0,10.0,9.0],[1.0,19.0,8.0],[2.0,18.0,7.0],[3.0,17.0]].

array_union function

Returns an array of the elements in the union of two arrays, without duplicates.

Syntax

array_union(x, y)

Parameters

Parameter	Description
x	Must be an array.
y	Must be an array.

Return value type

array

Examples

This example calculates the union of the arrays [1,2,3,4,5] and [1,3,5,7].

Query statement

* | SELECT array_union(array[1,2,3,4,5],array[1,3,5,7])

The query returns [1,2,3,4,5,7].

Cardinality function

Returns the number of elements in an array.

Syntax

cardinality(x)

Parameters

Parameter	Description
x	The input array.

Return value type

Returns a bigint value.

Examples

This example counts the number of elements in the number field.

Sample field
```
number:[49,50,45,47,50]
```

Query statement

*| SELECT cardinality(cast(json_parse(number) as array(bigint)))

The query returns 5, as the array contains 5 elements.

concat function

Concatenates multiple arrays into a single array.

Syntax

concat(x, y…)

Parameters

Parameter	Description
x	Must be an array.
y	Must be an array.

Return value type

array

Examples

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

Query statement

* | SELECT concat(array['red','blue'],array['yellow','green'])

The query returns ["red","blue","yellow","green"].

contains function

Checks if an array contains a specified element. Returns true if found.

Syntax

contains(x, element)

Parameters

Parameter	Description
x	The array to check.
element	The element to search for in the array.

Return value type

boolean

Examples

Checks if 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')

The query returns a column named _col0 with the value true, which indicates that the region array contains cn-beijing.

element_at function

Returns the element at index y in an array.

Syntax

element_at(x, y)

Parameters

Parameter	Description
x	The array from which to retrieve an element.
y	The 1-based bigint index of the element to retrieve from the array.

Return value type

The return value has the same data type as the elements in the array.

Examples

This example retrieves the second element from an array that is stored as a JSON string in the number field.

Sample field
```
number:[49,50,45,47,50]
```

Query and analysis statement

* |
SELECT
  element_at(cast(json_parse(number) AS array(varchar)), 2)

The query returns a column named _col0 with a value of 50.

Filter function

The filter function filters an array based on a lambda expression, returning a new array that contains only the elements for which the expression evaluates to true.

Syntax

filter(x, lambda_expression)

Parameters

Parameter	Description
x	The input array.
lambda_expression	A lambda expression. For more information, see lambda expression.

Return value type

Returns an array.

Examples

Returns the elements that are greater than 0 from the array [5,-6,null,7]. The lambda expression is x -> x > 0.

Query statement

* | SELECT filter(array[5,-6,null,7],x -> x > 0)

The query returns [5,7].

Flatten function

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

Syntax

flatten(x)

Parameters

Parameter	Description
x	The two-dimensional array to flatten. The value must be an array.

Return value type

Returns a one-dimensional array.

Examples

This example flattens a two-dimensional array into a one-dimensional array.

Query statement

* | SELECT flatten(array[array[1,2,3,4],array[5,2,2,4]])

Query and analysis results: The _col0 column contains [1,2,3,4,5,2,2,4].

Reduce function

The reduce function applies a lambda expression to an array, aggregating its elements into a single value.

Syntax

reduce(array, initialState, inputFunction, outputFunction)

Parameters

Parameter	Description
array	The input array of bigint elements.
initialState, inputFunction, outputFunction	Defines the reduction logic, which includes an initial state, an input function that processes each element, and an output function that finalizes the result. For more information, see lambda expression.

Return value type

bigint

Examples

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

Query statement

* | SELECT reduce(array[5, 20, 50], 0, (s, x) -> s + x, s -> s)

Query result: 75

Reverse function

Reverses the order of elements in an array.

Syntax

reverse(x)

Parameters

Parameter	Description
x	The array to reverse.

Return value type

An array with its elements in reverse order.

Examples

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

Query statement
```
* | SELECT reverse(array[1,2,3,4,5])
```
Query results: [5,4,3,2,1].

Sequence function

The sequence function returns an array of consecutive, incrementing values within a specified range.

Syntax

Uses a default step of 1.
```
sequence(x, y)
```
Uses a custom step.
```
sequence(x, y, step)
```

Parameters

Parameter	Description
x	The start value of the sequence. Supported data types are bigint and timestamp. For the timestamp type, you can use a UNIX timestamp or a date and time expression.
y	The end value of the sequence. Supported data types are bigint and timestamp. For the timestamp type, you can use a UNIX timestamp or a date and time expression.
step	The step value. When x and y are date and time expressions, the step parameter must be in one of the following formats: `interval ' n' year to month`: The interval is n years. `interval 'n' day to second`: The interval is n days.

Return value type

array

Examples

Return even numbers from 0 to 10.
- Query statement
```
* | SELECT sequence(0,10,2)
```
- The query returns [0,2,4,6,8,10].

Return the dates from October 23, 2017, to August 12, 2021, at an interval of one year.

Query statement

ww* | SELECT  sequence(from_unixtime(1508737026),from_unixtime(1628734085),interval '1' year to month )

Query result

["2017-10-23 13:37:06.000","2018-10-23 13:37:06.000","2019-10-23 13:37:06.000","2020-10-23 13:37:06.000"]

Return the UNIX timestamps between 1628733298 and 1628734085 at an interval of 60 seconds.
- Query statement
```
* | SELECT  sequence(1628733298,1628734085,60)
```
- In the result, the _col0 field contains the timestamp array [1628733298,1628733358,1628733418,1628733478,1628733538,1628733598,1628733658,1628733718,1628733778,1628733838,1628733898,1628733958,1628734018,1628734078].

Shuffle function

Randomly shuffles the elements of an array.

Syntax

shuffle(x)

Parameters

Parameter	Description
x	Must be an array.

Return value type

Returns an array.

Examples

This example returns a random permutation of the array [1,2,3,4,5].

Query and analysis statement
```
*| SELECT shuffle(array[1,2,3,4,5])
```
Query and analysis results: The query returns a single column, _col0, containing a randomly reordered array. Example values include [3,1,2,4,5], [5,1,2,4,3], and [2,5,3,1,4].

slice function

Returns a subset of an array.

Syntax

slice(x, start, length)

Parameters

Parameter	Description
x	The input array.
start	The 1-based starting index of the slice. If start is negative, the index is counted from the end of the array. If start is positive, the index is counted from the beginning of the array.
length	The number of elements to include in the subset.

Return value type

array

Examples

This example returns a two-element subset of the array [1,2,4,5,6,7,7], starting at the third element.

Query and analysis statement

* | SELECT slice(array[1,2,4,5,6,7,7],3,2)

Query and analysis results: The _col0 column contains [4,5].

Transform function

The transform function applies a lambda expression to each element of an array.

Syntax

transform(x, lambda_expression)

Parameters

Parameter	Description
x	Must be an array.
lambda_expression	The lambda expression to apply. For more information, see lambda expression.

Return value type

Array.

Examples

This example adds 1 to each element in the array [5,6].

query and analysis statement

* | SELECT transform(array[5,6],x -> x + 1)

The query returns [6,7].

zip function

The zip function merges multiple arrays into a two-dimensional array. It groups elements with the same index from the input arrays to form nested arrays.

Syntax

zip(x, y...)

Parameters

Parameter	Description
x	Must be an array.
y	Must be an array.

Return value type

Returns a two-dimensional array.

Examples

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

Query and analysis statement

* | SELECT zip(array[1,2,3], array['1b',null,'3b'],array[1,2,3])

The _col0 column in the query and analysis results returns [[1,"1b",1],[2,null,2],[3,"3b",3]]. The function groups elements from the input arrays by their shared index.

zip_with function

The zip_with function merges two arrays by applying a lambda expression to their corresponding elements.

Syntax

zip_with(x, y, lambda_expression)

Parameters

Parameter	Description
x	The first input array.
y	The second input array.
lambda_expression	A lambda expression that specifies how to combine the elements. For more information, see lambda expression.

Return value type

array

Examples

This example uses the lambda expression (x, y) -> x + y to add the corresponding elements of the arrays [1,2] and [3,4]. The function returns the results in a new array.

Query and analysis statement

SELECT zip_with(array[1,2], array[3,4],(x,y) -> x + y)

Query and analysis results: The value in the _col0 column is [4,6].