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
Parameter | Type | Description | Required |
---|---|---|---|
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:
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
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] |
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayDelta(a) |
---|---|
[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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayDistinct(a) |
---|---|
[2, 3, 7, 7, 1] | [1, 2, 3, 7] |
arrayFirst
Returns the first element of an array.
Syntax
arrayFirst(arrayName)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayFirst(a) |
---|---|
[2, 3, 7, 7, 1] | 2 |
arrayFlatten
Returns a flattened array.
Syntax
arrayFlatten(array)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayFlatten(a) |
---|---|
|
|
|
|
[[ | [ |
arrayIndexOf
Returns the position of the first member in the array, which is equal to the given value.
Syntax
arrayIndexOf(array, value)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayIndexOf(a, 2) | arrayIndexOf(a, 7) | arrayIndexOf(a, 11) |
---|---|---|---|
[2, 3, 7, 7, 1] | 0 | 2 | -1 |
arrayLast
Returns the last element of an array.
Syntax
arrayLast(array)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayLastIndexOf(a, 2) | arrayLastIndexOf(a, 7) | arrayLastIndexOf(a, 11) |
---|---|---|---|
[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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMax(a) |
---|---|
[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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMedian(a) |
---|---|
[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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMin(a) |
---|---|
[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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMovingAvg(a, 2) | arrayMovingAvg(a, 6) |
---|---|---|
[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] | null | null |
arrayMovingMax
Replaces each element of the input array with the maximum of current and previous elements within the window.
Syntax
arrayMovingMax(array, window)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMovingMax(a, 2) | arrayMovingMax(a, 6) |
---|---|---|
[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] | null | null |
arrayMovingMin
Replaces each element of the input array with the minimum of current and previous elements within the window.
Syntax
arrayMovingMin(array, windowSize)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMovingMin(a, 2) | arrayMovingMin(a, 6) |
---|---|---|
[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] | null | null |
arrayMovingSum
Replaces each element of the input array with the sum of current and previous elements within the window.
Syntax
arrayMovingSum(array, windowSize)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayMovingSum(a, 2) | arrayMovingSum(a, 6) |
---|---|---|
[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] | null | null |
arrayPercentile
Calculates a given percentile of an array.
Syntax
arrayPercentile(expression, percentile)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayPercentile(a, 50) | arrayPercentile(a, 99) |
---|---|---|
[2, 3, 7, 7, 1] | 3 | 7 |
arrayRemoveNulls
Returns the array where NULL elements are removed.
Syntax
arrayRemoveNulls(array)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arrayReverse(a) |
---|---|
[2, 3, 7, 7, 1] | [1, 7, 7, 3, 2] |
arraySize
Returns the size of an array.
Syntax
arraySize(array)
Parameters
Parameter | Type | Description | Required |
---|---|---|---|
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:
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
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
Parameter | Type | Description | Required |
---|---|---|---|
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:
a | arraySum(a) |
---|---|
[2, 3, 7, 7, 1] | 20 |