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 |