Array functions
Functions related to a collection of items of the same data type stored at adjacent memory locations.
array
Creates an array from the list of given parameters.
-
Syntax
array(expression, …) -
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
An element inside the array.
-
Example
1data record()2| fieldsAdd array(2, 3, 7, 7, 1)Query result
array(2, 3, 7, 7, 1) [2, 3, 7, 7, 1]
arrayAvg
Returns the average of an array. Values that are not numeric are ignored. Returns 0 if there is no matching element.
-
Syntax
arrayAvg(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayAvg(a)Query result
a arrayAvg(a) [2, 3, 7, 7, 1]4
arrayConcat
Concatenates multiple arrays into a single array. This function skips all null values and non-array input parameter values. If no array parameter input is available, it returns null.
-
Syntax
arrayConcat(array, …) -
Parameters
Name Type Mandatory Default Constraints Description array
array expression
Y
Array expression that should be combined with others.
-
Example
1data record(a = array(2, 3, 7, 7, 1), b = array("hello", "world"), c = array(null, 13))2| fieldsAdd arrayConcat(a, b, c)Query result
a b c arrayConcat(a, b, c) [2, 3, 7, 7, 1][hello, world][NULL, 13][2, 3, 7, 7, 1, hello, world, NULL, 13]
arrayDistinct
Returns the array without duplicates. It sorts numbers in ascending order and strings in lexicographic order.
-
Syntax
arrayDistinct(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayDistinct(a)Query result
a arrayDistinct(a) [2, 3, 7, 7, 1][1, 2, 3, 7]
arrayFirst
Returns the first element of an array.
-
Syntax
arrayFirst(arrayName) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayFirst(a)Query result
a arrayFirst(a) [2, 3, 7, 7, 1]2
arrayIndexOf
Returns position of the first member in the array, which is equal to the given value.
-
Syntax
arrayIndexOf(array, value) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
The array expression in which the value is searched for.
value
expression
yes
The primitive value to search for in the expression.
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayIndexOf(a, 2), arrayIndexOf(a, 7), arrayIndexOf(a, 11)Query result
a arrayIndexOf(a, 2) arrayIndexOf(a, 7) arrayIndexOf(a, 11) [2, 3, 7, 7, 1]02-1
arrayLast
Returns the last element of an array.
-
Syntax
arrayLast(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayLast(a)Query result
a arrayLast(a) [2, 3, 7, 7, 1]1
arrayLastIndexOf
Returns position of the last member in the array, which is equal to the given value.
-
Syntax
arrayLastIndexOf(array, value) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
The array expression in which the value is searched for.
value
expression
yes
The primitive value to search for in the expression.
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayLastIndexOf(a, 2), arrayLastIndexOf(a, 7), arrayLastIndexOf(a, 11)Query result
a arrayLastIndexOf(a, 2) arrayLastIndexOf(a, 7) arrayLastIndexOf(a, 11) [2, 3, 7, 7, 1]03-1
arrayMax
Returns the maximum (biggest) number of an array. Values that are not numeric are ignored. Returns NULL if there is no matching element.
-
Syntax
arrayMax(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayMax(a)Query result
a arrayMax(a) [2, 3, 7, 7, 1]7
arrayMin
Returns the minimum (smallest) number of an array. Values that are not numeric are ignored. Returns NULL if there is no matching element.
-
Syntax
arrayMin(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayMin(a)Query result
a arrayMin(a) [2, 3, 7, 7, 1]1
arrayPercentile
Calculates a given percentile of an array.
-
Syntax
arrayPercentile(expression, percentile) -
Parameters
Name Type Mandatory Default Constraints Description expression
array
yes
The array from which to compute a percentile.
percentile
double, long
yes
The percentile to compute, between 0 and 100.
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayPercentile(a, 50), arrayPercentile(a, 99)Query result
a arrayPercentile(a, 50) arrayPercentile(a, 99) [2, 3, 7, 7, 1]37
arrayRemoveNulls
Returns the array where NULL elements are removed.
-
Syntax
arrayRemoveNulls(array) -
Parameters
| Name | Type | Mandatory | Default | Constraints | Description |
|---|---|---|---|---|---|
array | array | yes |
-
Example
1data record(a = array(2, 3, null, 7, 7, 1))2| fieldsAdd arrayRemoveNulls(a)Query result
a arrayRemoveNulls(a) [2, 3, NULL, 7, 7, 1][2, 3, 7, 7, 1]
arrayReverse
Returns the array with elements in reversed order.
-
Syntax
arrayReverse(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arrayReverse(a)Query result
a arrayReverse(a) [2, 3, 7, 7, 1][1, 7, 7, 3, 2]
arraySize
Returns the size of an array.
-
Syntax
arraySize(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arraySize(a)Query result
a arraySize(a) [2, 3, 7, 7, 1]5
arraySort
Returns the array with elements sorted in ascending order by default. If you need to control the order, use the direction parameter.
-
Syntax
arraySort(array, direction) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
direction
string
no
ascending
possible values: ascending, descending
a sort order
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arraySort(a), arraySort(a, direction: "descending")Query result
a arraySort(a) arraySort(a, direction:"descending") [2, 3, 7, 7, 1][1, 2, 3, 7, 7][7, 7, 3, 2, 1]
arraySum
Returns the sum of an array. Values that are not numeric are ignored. Returns 0 if there is no matching element.
-
Syntax
arraySum(array) -
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
1data record(a = array(2, 3, 7, 7, 1))2| fieldsAdd arraySum(a)Query result
a arraySum(a) [2, 3, 7, 7, 1]20