Array Functions

Operate on array values using the array functions supported by the Query service.

Function	Return Type	Input Arguments	Description
`all_match(arr[T], function(T, boolean))`	Boolean	arr[T]: Array function(): A function that takes an array and a boolean as arguments	Returns whether all elements of an array match the given predicate function. If all the elements match the predicate function, this function returns TRUE. If one or more elements don’t match, this function returns FALSE.
`any_match(arr[T], function(T, boolean))`	Boolean	arr[T]: Array function(): A function that takes an array and a boolean as arguments	Returns whether any elements of an array match the given predicate function. If one or more elements match the predicate function, this function returns TRUE. If no elements match the predicate, this function returns FALSE.
`array_distinct(arr[])`	Array	arr[]: Array	Returns the distinct values in the input array.
`array_except(arr1[], arr2[])`	Array	arr1[], arr2[], ...: Arrays	Returns an array of elements in arr1[] but not in arr2[]. This function eliminates any items that are duplicated in the two arrays.
`array_intersect(arr1[], arr2[])`	Array	arr1[], arr2[]: Arrays	Returns an array of elements common to arr1[] and arr2[]. This function finds items that are duplicated in the two arrays.
`array_join(arr[], delimiter, null_replacement)`	Varchar	arr[]: Array delimiter: null_replacement: String (optional)	Concatenates the elements of the array and the delimiter specified in the input arguments. Null values in the array are replaced with an optional null_replacement character.
`array_max(arr[])`	Data type of the elements in the input array	arr[]: Array	Returns the maximum value in arr[].
`array_min(arr[])`	Data type of the elements in the input array	arr[]: Array	Returns the minimum value in arr[].
`array_position(arr[], element)`	Bigint	arr[]: Array element: Any data type	Returns the position of the first occurrence of element in arr[]. If the element isn’t found, it returns zero.
`array_remove(arr[], element)`	Array	arr[]: Array element: Any data type	Removes the specified element from the array.
`array_sort(arr[])`	Array	arr[]: Array	Returns an array with sorted elements. Nulls are placed at the end.
`array_union(arr1[], arr2[])`	Array	arr1[], arr2[]: Arrays	Returns the union of arr1[] and arr2[] without duplicates.
`arrays_overlap(arr1[], arr2[])`	Boolean	arr1[], arr2[]: Arrays	Returns TRUE if arrays arr1[] and arr2[] have any non-null elements in common, or FALSE otherwise.
`cardinality(arr[])`	Bigint	arr[]: Array	Returns the size of the input array.
`combinations(arr[])`	An array of arrays	arr[]: Array	Returns all the possible n-element subgroups of the input array.
`concat(arr1[], arr2[], ...)`	Array	arr1[], arr2[], ...: Arrays	Returns a concatenated array of the input arrays.
`contains(arr[], element)`	Boolean	arr[]: Array element: Any data type	Returns TRUE if the array arr[] contains the element, or FALSE otherwise.
`element_at(arr[], index)`	Data type of the array elements	arr[]: Array index: Positive integer	Returns the element from the array arr[] at the index position.
`filter(arr[T], function(Tboolean))`	Array	arr[]: Array function(): A predicate function	Returns an array of those elements from array arr[] for which function() returns TRUE. For example: `SELECT filter(ARRAY[5, -6, NULL, 7], x -> x > 0)` returns `[5, 7]`
`flatten(arr[])`	Array	arr[]: Array of arrays	Returns a flattened array (array[T]) by concatenating the contained arrays.
`ngrams(arr[])`	Array	arr[]: Array	Returns n-grams for the array arr[].
`repeat(element, count)`	Array	element: Any data type count: Integer	Repeats element for count times.
`reverse(arr[])`	Array	arr[]: Array	Returns an array that reverses the order of the elements in the input array.
`sequence(start, stop)`	Integer	start: Integer stop: Integers	Generates a sequence of integers from start to stop. If start is less than or equal to stop, it increments by positive 1. Otherwise, it decrements by negative 1.
`shuffle(arr[])`	Array	arr[]: Array	Returns a random permutation of the input array.
`slice(arr[], index, length)`	Array	arr[]: Array index: Positive integer length: Positive integer	Returns an array using elements taken from the input array starting from index index with a length of length.
`transform(arr[T], function(T, X))`	Array	arr[]: Array function():	Returns an array that is the result of applying function() to each element of the input array. For example: `SELECT transform(ARRAY[8, 0], x -> x + 1);` returns `[9, 1]`
`zip(arr1[], arr2[], ...)`	Array	arr1[], arr2[], ...: Arrays	Returns an array formed by merging the given arrays element-wise into a single array. If the arguments have an uneven length, missing values are replaced with null.
`zip_with(arr1[], arr2[], function(x, y))`	Array	arr1[], arr2[]: function():	Merges the two input arrays element-wise into a single array by using function(). If one array is shorter than the other, nulls are appended at the end to match the length of the longer array before applying function().