Array functions
Timestream for LiveAnalytics supports the following array functions.
| Function | Output data type | Description |
|---|---|---|
|
array_distinct(x) |
array |
Remove duplicate values from the array x. Example result: |
|
array_intersect(x, y) |
array |
Returns an array of the elements in the intersection of x and y, without duplicates. Example result: |
|
array_union(x, y) |
array |
Returns an array of the elements in the union of x and y, without duplicates. Example result: |
|
array_except(x, y) |
array |
Returns an array of elements in x but not in y, without duplicates. Example result: |
|
array_join(x, delimiter, null_replacement) |
varchar |
Concatenates the elements of the given array using the delimiter and an optional string to replace nulls. Example result: |
|
array_max(x) |
same as array elements |
Returns the maximum value of input array. Example result: |
|
array_min(x) |
same as array elements |
Returns the minimum value of input array. Example result: |
|
array_position(x, element) |
bigint |
Returns the position of the first occurrence of the element in array x (or 0 if not found). Example result: |
|
array_remove(x, element) |
array |
Remove all elements that equal element from array x. Example result: |
|
array_sort(x) |
array |
Sorts and returns the array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array. Example result: |
|
arrays_overlap(x, y) |
boolean |
Tests if arrays x and y have any non-null elements in common. Returns null if there are no non-null elements in common but either array contains null. Example result: |
|
cardinality(x) |
bigint |
Returns the size of the array x. Example result: |
|
concat(array1, array2, ..., arrayN) |
array |
Concatenates the arrays array1, array2, ..., arrayN. Example result: |
|
element_at(array(E), index) |
E |
Returns element of array at given index. If index < 0, element_at accesses elements from the last to the first. Example result: |
|
repeat(element, count) |
array |
Repeat element for count times. Example result: |
|
reverse(x) |
array |
Returns an array which has the reversed order of array x. Example result: |
|
sequence(start, stop) |
array(bigint) |
Generate a sequence of integers from start to stop, incrementing by 1 if start is less than or equal to stop, otherwise -1. Example result: |
|
sequence(start, stop, step) |
array(bigint) |
Generate a sequence of integers from start to stop, incrementing by step. Example result: |
|
sequence(start, stop) |
array(timestamp) |
Generate a sequence of timestamps from start date to stop date, incrementing by 1 day. Example result: |
|
sequence(start, stop, step) |
array(timestamp) |
Generate a sequence of timestamps from start to stop, incrementing by step. The data type of step is interval. Example result: |
|
shuffle(x) |
array |
Generate a random permutation of the given array x. Example result: |
|
slice(x, start, length) |
array |
Subsets array x starting from index start (or starting from the end if start is negative) with a length of length. Example result: |
|
zip(array1, array2[, ...]) |
array(row) |
Merges the given arrays, element-wise, into a single array of rows. If the arguments have an uneven length, missing values are filled with NULL. Example result: |
