Functions related to a collection of items of the same data type stored at adjacent memory locations.
Creates an array from the list of given parameters.
array(expression, …)
Parameter
Type
Description
Required
expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
An element inside the array.
Required
The data type of the returned value is array.
data record()| fieldsAdd array(2, 3, 7, 7, 1)
Query result:
| array(2, 3, 7, 7, 1) |
|---|
[2, 3, 7, 7, 1] |
Returns the average of an array. Values that are not numeric are ignored. Returns 0 if there is no matching element.
arrayAvg(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is double or long.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayAvg(a)
Query result:
| a | arrayAvg(a) |
|---|---|
[2, 3, 7, 7, 1] | 4 |
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.
arrayConcat(array, …)
Parameter
Type
Description
Required
array
array expression
Array expression that should be combined with others.
Required
The data type of the returned value is array.
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] |
Returns the cumulative sum, also known as the running total, of the elements of the input array.
arrayCumulativeSum(array)
Parameter
Type
Description
Required
array
array
The input array.
Required
The data type of the returned value is array.
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 arrayCumulativeSum(a)
Query result:
a
arrayCumulativeSum(a)
2, 3, 7, 7, 1
[2, 5, 12, 19, 20]
2, null, 7, null, 1
[2, 2, 9, 9, 10]
2, 3, 7, 7, 1
null
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.
arrayDelta(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is array.
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] |
Calculates the element-wise difference between consecutive elements in an array. For each element at index i > 0, the result is: result[i] = array[i] - array[i - 1]. The first element of the returned array is null. The function supports array elements of numeric type (long, double), timestamp or duration.
Unlike arrayDelta, arrayDiff uses the immediate previous element rather than the previous non-null element and preserves negative differences.
arrayDiff(array)
Parameter
Type
Description
Required
array
array
The array expression to get the element-wise difference from.
Required
The data type of the returned value is array.
data record(a = array(2, 3, 7, 7, 1)),record(a = array(5, null, 2, 4, -1)),record(a = array(now(), now()+1h, now())),record(a = array(2, 3, 1h, now(), 1h, 2h))| fieldsAdd arrayDiff(a)
Query result:
| a | arrayDiff(a) |
|---|---|
[2, 3, 7, 7, 1] | [null, 1, 4, 0, -6] |
[5, null, 2, 4, -1] | [null, null, null, 2, -5] |
[now(), now()+1h, now(),] | [null, 1h, -1h] |
[2, 3, 1h, now(), 1h, 2h] | [null, 1, null, now()-1h, null, 1h] |
Returns the array without duplicates. It sorts numbers in ascending order and strings in lexicographic order.
arrayDistinct(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is array.
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] |
Extracts a single element with the given index from an array. The index can be defined as any dynamic expression of data type long. When the index is a literal, you can alternatively use the bracket notation: array[...].
arrayElement(expression, index)
Parameter
Type
Description
Required
expression
array
The array from which to extract an element.
Required
index
long
The index of the element to extract. Negative numbers are counted from the end. Use -1 for the last element of the array.
Required
The data type of the returned value matches the data type of the element at the specified index.
data record(a = array(0, 1, 2, 3, 4, 5, 6), i = 2),record(a = array("foo", "bar"), i = -1)| fieldsAdd a[2],arrayElement(a, 2),arrayElement(a, i),arrayElement(a, (arraySize(a)-1) / 2)
Query result:
| a | i | a[2] | arrayElement(a, 2) | arrayElement(a, i) | arrayElement(a, (arraySize(a)-1) / 2) |
|---|---|---|---|---|---|
[0, 1, 2, 3, 4, 5, 6] | 2 | 2 | 2 | 2 | 3 |
["foo", "bar"] | -1 | null | null | "bar" | "foo" |
Returns the first non-null element of an array.
arrayFirst(arrayName)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value matches the data type of the first element of the input array.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayFirst(a)
Query result:
| a | arrayFirst(a) |
|---|---|
[2, 3, 7, 7, 1] | 2 |
Returns a flattened array.
arrayFlatten(array)
Parameter
Type
Description
Required
array
array
The array that should be flattened.
Required
The data type of the returned value is array.
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)
[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]
Returns the position of the first member in the array, which is equal to the given value.
arrayIndexOf(array, value)
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
The data type of the returned value is long.
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 |
Returns the last non-null element of an array.
arrayLast(array)
Parameter
Type
Description
Required
array
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
Required
The data type of the returned value matches the data type of the last element of the input array.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayLast(a)
Query result:
| a | arrayLast(a) |
|---|---|
[2, 3, 7, 7, 1] | 1 |
Returns position of the last member in the array, which is equal to the given value.
arrayLastIndexOf(array, value)
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
The data type of the returned value is long.
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 |
Returns the maximum (biggest) number of an array. Values that are not numeric are ignored. Returns NULL if there is no matching element.
arrayMax(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value matches the data type of the last element of the input array.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayMax(a)
Query result:
| a | arrayMax(a) |
|---|---|
[2, 3, 7, 7, 1] | 7 |
Returns the median of the members of an array. Returns null for arrays with mixed data type. Quantile calculations use an exponential histogram representation suitable for large data sets with high dynamic ranges, producing small relative errors. Results might differ slightly from those obtained through less calculation-efficient methods.
arrayMedian(expression)
Parameter
Type
Description
Required
expression
array
The array from which to compute the median.
Required
The data type of the returned value can be boolean, double, duration, or timestamp.
fetch bizevents | filter matchesValue(event.category, {"/v1/trade/buy", "/v1/trade/sell"}) | summarize amounts = collectArray(amount), by: event.category | fieldsAdd arrayMedian(amounts)
Returns the minimum (smallest) number of an array. Values that are not numeric are ignored. Returns NULL if there is no matching element.
arrayMin(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value matches the data type of the minimum (smallest) element of the input array.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arrayMin(a)
Query result:
| a | arrayMin(a) |
|---|---|
[2, 3, 7, 7, 1] | 1 |
Replaces each element of the input array with the average of current and previous elements within the window.
arrayMovingAvg(array, window)
Parameter
Type
Description
Required
array
array
Required
window
double, long
The maximum number of elements to look back at. Maximum 60.
Required
The data type of the returned value is array.
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 |
Replaces each element of the input array with the maximum of current and previous elements within the window.
arrayMovingMax(array, window)
Parameter
Type
Description
Required
array
array
Required
window
double, long
The maximum number of elements to look back at. Maximum 60.
Required
The data type of the returned value is array.
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 |
Replaces each element of the input array with the minimum of current and previous elements within the window.
arrayMovingMin(array, windowSize)
Parameter
Type
Description
Required
array
array
Required
window
double, long
The maximum number of elements to look back at. Maximum 60.
Required
The data type of the returned value is array.
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 |
Replaces each element of the input array with the sum of current and previous elements within the window.
arrayMovingSum(array, windowSize)
Parameter
Type
Description
Required
array
array
Required
window
double, long
The maximum number of elements to look back at. Maximum 60.
Required
The data type of the returned value is array.
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 |
Calculates a given percentile of an array. Quantile calculations use an exponential histogram representation suitable for large data sets with high dynamic ranges, producing small relative errors. Results might differ slightly from those obtained through less calculation-efficient methods.
arrayPercentile(expression, percentile)
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
The data type of the returned value can be boolean, double, duration, or timestamp.
fetch bizevents | filter matchesValue(event.category, {"/v1/trade/buy", "/v1/trade/sell"}) | summarize amounts = collectArray(amount), by: event.category | fieldsAdd arrayPercentile(amounts, 90)
Returns the array where NULL elements are removed.
arrayRemoveNulls(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is array.
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] |
Returns the array with elements in reversed order.
arrayReverse(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is array.
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] |
Returns the size of an array.
arraySize(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is long.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arraySize(a)
Query result:
| a | arraySize(a) |
|---|---|
[2, 3, 7, 7, 1] | 5 |
Extracts a slice from the input array using a from index (inclusive) and a to index (exclusive). Indexes that are >= 0 are counted from the start of the array, and indexes < 0 are counted from the end of the array. The index 0 represents the first array element and index -1 represents the last array element. Positive indexes beyond the array length are clamped to the array end, and negative indexes beyond the start are clamped to the array start. If from >= to, the function returns an empty array.
arraySlice(array [, from] [, to])
Parameter
Type
Description
Required
array
array
The array expression to get the slice from.
Required
from
long
Index of the first element to include in the slice (inclusive). Default: 0.
Optional
to
long
Index of the last element to include in the slice (exclusive). Default: array length.
Optional
The data type of the returned value is array.
data record(a = array(0, 1, 2, 3, 4))| fieldsAdd arraySlice(a),arraySlice(a, from: 2),arraySlice(a, from: -2),arraySlice(a, to: -1),arraySlice(a, from: -100, to: 100),arraySlice(a, from: -1, to: 1),arraySlice(a, from: 100, to: 101)
Query result:
a
arraySlice(a)
arraySlice(a, from: 2)
arraySlice(a, from: -2)
arraySlice(a, to: -1)
arraySlice(a, from: -100, to: 100)
arraySlice(a, from: -1, to: 1)
arraySlice(a, from: 100, to: 101)
[0,1,2,3,4]
[0,1,2,3,4]
[2,3,4]
[3,4]
[0,1,2,3]
[0,1,2,3,4]
[]
[]
Returns the array with elements sorted in ascending order by default. If you need to control the order, use the direction parameter.
arraySort(array, direction)
Parameter
Type
Description
Required
array
array
Required
direction
string
A sort order. Possible values: ascending, descending. Default: ascending.
Optional
The data type of the returned value is array.
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] |
Returns the sum of an array. Values that are not numeric are ignored. Returns 0 if there is no matching element.
arraySum(array)
Parameter
Type
Description
Required
array
array
Required
The data type of the returned value is double or long.
data record(a = array(2, 3, 7, 7, 1))| fieldsAdd arraySum(a)
Query result:
| a | arraySum(a) |
|---|---|
[2, 3, 7, 7, 1] | 20 |
Converts an array into a string. You can specify the optional delimiter parameter to add a delimiter between elements in the output string.
arrayToString(expression [, delimiter])
Parameter
Type
Description
Required
expression
array
The array to convert to a string.
Required
delimiter
string
The character(s) to insert between array elements. Default: "" (none).
Optional
The data type of the returned value is string.
data record(a = array(1, 2, 3)),record(a = array("D", "Q", "L"))| fieldsAdd arrayToString(a), arrayToString(a, delimiter:", ")
Query result:
a
arrayToString(a)
arrayToString(a, delimiter:", ")
[1, 2, 3]
"123"
"1, 2, 3"
["D", "Q", "L"]
"DQL"
"D, Q, L"