This topic describes the syntax of array functions and provides examples on how to use these functions.

Operator or function Description Example
Subscript operator ([]) Obtains a certain element in an array. N/A
array_distinct Removes duplicate values from an array. N/A
array_intersect(x, y) Obtains the intersection of arrays x and y. N/A
array_union(x, y) → array Obtains the union of arrays x and y. N/A
array_except(x, y) → array Obtains the subtraction of arrays x and y. N/A
array_join(x, delimiter, null_replacement) → varchar Concatenates the elements of the specified array by using the delimiter and an optional string to replace null elements.
Note When you use the array_join function, the maximum length of the result is 1 KB. Excess length is truncated.
N/A
array_max(x) → x Returns the maximum value of the input array. N/A
array_min(x) → x Returns the minimum value of the input array. N/A
array_position(x, element) → bigint Returns the position of the first occurrence of the specified element in array x (or 0 if not found). N/A
array_remove(x, element) → array Removes all occurrences of a specified element from array x. N/A
array_sort(x) → array Sorts and returns array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array. N/A
cardinality(x) → bigint Returns the cardinality (size) of array x. N/A
concat(array1, array2, ..., arrayN) → array Concatenates the arrays array1, array2, ..., and arrayN. N/A
contains(x, element) → boolean Returns true if array x contains the specified element. N/A
filter(array, function) → array For more information about this function (a Lambda function), see filter in Lambda functions. N/A
flatten(x) → array Flattens an array(array(T)) to an array(T) by concatenating the contained arrays. N/A
reduce(array, initialState, inputFunction, outputFunction) → x For more information about this function, see reduce in Lambda functions. N/A
reverse(x) → array Returns an array that has the reversed order of array x. N/A
sequence(start, stop) → array Generates a sequence of items from start to stop, increasing by 1. N/A
sequence(start, stop, step) → array Generates a sequence of items from start to stop, increasing by step. N/A
sequence(start, stop, step) → array Generates a sequence of timestamps from start to stop, increasing by step. The type of step can be either INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH. N/A
shuffle(x) → array Generates a random permutation of array x. N/A
slice(x, start, length) → array Returns a subset of array x starting from the start value with the specified length. N/A
transform(array, function) → array For more information about this function, see transform() in Lambda functions. N/A
zip(array1, array2[, ...]) → array Merges the specified arrays. The M-th element of the N-th argument will be the N-th field of the M-th output element. SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
zip_with(array1, array2, function) → array For more information, see zip_with in Lambda functions. N/A
array_agg (key) An aggregate function that returns an array from values in the key column. * | select array_agg(key)
array_transpose(array[array[x,y,z], array[a,b,c]]) Returns a new matrix by changing rows to columns and the columns to rows. N/A