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, …)
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)
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, …)
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)
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:
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)
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)
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)
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)
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)
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)
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:
Returns the position of the first member in the array, which is equal to the given value.
arrayIndexOf(array, value)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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])
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:
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)
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)
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])
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: