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
expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
An element inside the array.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record()| fieldsAdd array(2, 3, 7, 7, 1)
Query result:
[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
array
array
required
Returns
The data type of the returned value is double
or long
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayAvg(a)
Query result:
[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
array
array expression
Array expression that should be combined with others.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1), b = array("hello", "world"), c = array(null, 13))| fieldsAdd arrayConcat(a, b, c)
Query result:
[2, 3, 7, 7, 1]
[hello, world]
[NULL, 13]
[2, 3, 7, 7, 1, hello, world, NULL, 13]
arrayDelta
Returns an array where each element is the difference from the previous non-null element, when positive, otherwise it returns 0. Null elements are skipped. The first element of the returned array is null.
Syntax
arrayDelta(array)
Parameters
array
array
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayDelta(a)
Query result:
[2, 3, 7, 7, 1]
[NULL, 1, 4, 0, 0]
arrayDistinct
Returns the array without duplicates. It sorts numbers in ascending order and strings in lexicographic order.
Syntax
arrayDistinct(array)
Parameters
array
array
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayDistinct(a)
Query result:
[2, 3, 7, 7, 1]
[1, 2, 3, 7]
arrayFirst
Returns the first element of an array.
Syntax
arrayFirst(arrayName)
Parameters
array
array
required
Returns
The data type of the returned value matches the data type of the first element of the input array.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayFirst(a)
Query result:
[2, 3, 7, 7, 1]
2
arrayFlatten
Returns a flattened array.
Syntax
arrayFlatten(array)
Parameters
array
array
The array that should be flattened.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1)),record(a = array(array(2, 3), array(7, 7, 1))),record(a = array(array(2, 3, null), array(record(a = 7), "7", 1)))| fieldsAdd arrayFlatten(a)
Query result:
[2, 3, 7, 7, 1]
[2, 3, 7, 7, 1]
[[2, 3], [7, 7, 1]]
[2, 3, 7, 7, 1]
[[2
, 3
, NULL
], [a:7
, 7
, 1
]]
[2
, 3
, NULL
, a: 7
, 7
, 1
]
arrayIndexOf
Returns the position of the first member in the array, which is equal to the given value.
Syntax
arrayIndexOf(array, value)
Parameters
array
array
The array expression in which the value is searched for.
required
value
expression
The primitive value to search for in the expression.
required
Returns
The data type of the returned value is long
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayIndexOf(a, 2), arrayIndexOf(a, 7), arrayIndexOf(a, 11)
Query result:
[2, 3, 7, 7, 1]
0
2
-1
arrayLast
Returns the last element of an array.
Syntax
arrayLast(array)
Parameters
array
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
required
Returns
The data type of the returned value matches the data type of the last element of the input array.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayLast(a)
Query result:
[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
array
array
The array expression in which the value is searched for.
required
value
expression
The primitive value to search for in the expression.
required
Returns
The data type of the returned value is long
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayLastIndexOf(a, 2), arrayLastIndexOf(a, 7), arrayLastIndexOf(a, 11)
Query result:
[2, 3, 7, 7, 1]
0
3
-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
array
array
required
Returns
The data type of the returned value matches the data type of the last element of the input array.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayMax(a)
Query result:
[2, 3, 7, 7, 1]
7
arrayMedian
Returns the median of the members of an array. Returns null
for arrays with mixed data type.
Syntax
arrayMedian(expression)
Parameters
expression
array
The array from which to compute the median.
required
Returns
The data type of the returned value can be boolean
, double
, duration
, or timestamp
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1)),record(a = array(2m, 3m, 7m, 7m, 1m)),record(a = array(now()-2m, now()-3m, now()-7m, now()-7m, now()-1m)),record(a = array(false, true, false, true))| fieldsAdd arrayMedian(a)
Query result:
[2, 3, 7, 7, 1]
3
[120000000000, 180000000000, 420000000000, 420000000000, 60000000000]
3 min
[2024-01-12T10:58:10.927511615Z, 2024-01-12T10:57:10.927511615Z, 2024-01-12T10:53:10.927511615Z, 2024-01-12T10:53:10.927511615Z, 2024-01-12T10:59:10.927511615Z]
2024-01-12T10:57:10.927Z
[false, true, false, true]
false
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
array
array
required
Returns
The data type of the returned value matches the data type of the minimum (smallest) element of the input array.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayMin(a)
Query result:
[2, 3, 7, 7, 1]
1
arrayMovingAvg
Replaces each element of the input array with the average of current and previous elements within the window.
Syntax
arrayMovingAvg(array, window)
Parameters
array
array
required
window
double, long
The maximum number of elements to look back at. Maximum 60.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1)),record(a = array(2, null, 7, null, 1)),record(a = array(2, "3", 7, 7, 1))| fieldsAdd arrayMovingAvg(a, 2), arrayMovingAvg(a, 6)
Query result:
[2, 3, 7, 7, 1]
[2, 2.5, 5, 7, 4]
[2, 2.5, 4, 4.75, 4]
[2, NULL, 7, NULL, 1]
[2, 2, 7, 7, 1]
[2, 2, 4.5, 4.5, 3.3333333333333335]
[2, 3, 7, 7, 1]
arrayMovingMax
Replaces each element of the input array with the maximum of current and previous elements within the window.
Syntax
arrayMovingMax(array, window)
Parameters
array
array
required
window
double, long
The maximum number of elements to look back at. Maximum 60.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1)),record(a = array(2, null, 7, null, 1)),record(a = array(2, "3", 7, 7, 1))| fieldsAdd arrayMovingMax(a, 2), arrayMovingMax(a, 6)
Query result:
[2, 3, 7, 7, 1]
[2, 3, 7, 7, 7]
[2, 3, 7, 7, 7]
[2, NULL, 7, NULL, 1]
[2, 2, 7, 7, 1]
[2, 2, 7, 7, 7]
[2, 3, 7, 7, 1]
arrayMovingMin
Replaces each element of the input array with the minimum of current and previous elements within the window.
Syntax
arrayMovingMin(array, windowSize)
Parameters
array
array
required
window
double, long
The maximum number of elements to look back at. Maximum 60.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1)),record(a = array(2, null, 7, null, 1)),record(a = array(2, "3", 7, 7, 1))| fieldsAdd arrayMovingMin(a, 2), arrayMovingMin(a, 6)
Query result:
[2, 3, 7, 7, 1]
[2, 2, 3, 7, 1]
[2, 2, 2, 2, 1]
[2, NULL, 7, NULL, 1]
[2, 2, 7, 7, 1]
[2, 2, 2, 2, 1]
[2, 3, 7, 7, 1]
arrayMovingSum
Replaces each element of the input array with the sum of current and previous elements within the window.
Syntax
arrayMovingSum(array, windowSize)
Parameters
array
array
required
window
double, long
The maximum number of elements to look back at. Maximum 60.
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1)),record(a = array(2, null, 7, null, 1)),record(a = array(2, "3", 7, 7, 1))| fieldsAdd arrayMovingSum(a, 2), arrayMovingSum(a, 6)
Query result:
[2, 3, 7, 7, 1]
[2, 5, 10, 14, 8]
[2, 5, 12, 19, 20]
[2, NULL, 7, NULL, 1]
[2, 2, 7, 7, 1]
[2, 2, 9, 9, 10]
[2, 3, 7, 7, 1]
arrayPercentile
Calculates a given percentile of an array.
Syntax
arrayPercentile(expression, percentile)
Parameters
expression
array
The array from which to compute a percentile.
required
percentile
double, long
The percentile to compute, between 0 and 100.
required
Returns
The data type of the returned value can be boolean
, double
, duration
, or timestamp
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayPercentile(a, 50), arrayPercentile(a, 99)
Query result:
[2, 3, 7, 7, 1]
3
7
arrayRemoveNulls
Returns the array where NULL elements are removed.
Syntax
arrayRemoveNulls(array)
Parameters
array
array
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, null, 7, 7, 1))| fieldsAdd arrayRemoveNulls(a)
Query result:
[2, 3, NULL, 7, 7, 1]
[2, 3, 7, 7, 1]
arrayReverse
Returns the array with elements in reversed order.
Syntax
arrayReverse(array)
Parameters
array
array
required
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayReverse(a)
Query result:
[2, 3, 7, 7, 1]
[1, 7, 7, 3, 2]
arraySize
Returns the size of an array.
Syntax
arraySize(array)
Parameters
array
array
required
Returns
The data type of the returned value is long
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arraySize(a)
Query result:
[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
array
array
required
direction
string
A sort order. Possible values: ascending, descending. Default: ascending.
optional
Returns
The data type of the returned value is array
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arraySort(a), arraySort(a, direction: "descending")
Query result:
[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
array
array
required
Returns
The data type of the returned value is double
or long
.
Examples
Example 1
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arraySum(a)
Query result:
[2, 3, 7, 7, 1]
20