Array Functions and Operators
Function | Syntax | Purpose |
|---|---|---|
Constructor | TYPE[](e1, e2, …, en) | Construct an array of SQL type TYPE giving the elements. NULL is also supported as an element. |
Constructor (2) | array[e1, e2, …, en] | PostgreSQL®-compliant constructor. The type of the array is deduced from the elements. |
array concatenation | array_cat(array, array) | Concatenate 2 arrays into a new one |
array length | array_length(array) | Return the number of elements of a given array |
array softmax | softmax(array) | Returns the softmax of the array. The array must be 1-dimensional and contain float or double values. The system transfers NULL-valued indices as 0.0. |
array cross entropy loss | cross_entropy_loss(array, array) | Returns the cross entropy loss of two arrays. Both arrays must be 1-dimensional and contain float or double values. NULL values do not contribute to the sum. |
array log loss | log_loss(array, array) | Returns the log loss of two arrays. Both arrays must be 1-dimensional and contain float or double values. NULL values do not contribute to the sum. |
array logits loss | logits_loss(array, array) | Returns the logits loss of two arrays. Both arrays must be 1-dimensional and contain float or double values. NULL values do not contribute to the sum. |
array hinge loss | hinge_loss(array, array) | Returns the hinge loss of two arrays. Both arrays must be 1-dimensional and contain float or double values. NULL values do not contribute to the sum. |
array sum | array_sum(array) | Returns the sum of the array. The array must be 1-dimensional and contain numeric values. NULL values do not contribute to the sum. |
array min/max | array_min(array), array_max(array) | Returns the corresponding minimum or maximum of the array. This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL. |
array argmin/argmax | array_argmin(array), array_argmax(array) | Returns the corresponding argmin or argmax of the array as a BIGINT index. This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL. |
array casting | cast_to_array(array, format) | Casts the elements of the array to another type, as given by the format string. The format is: 'ARRAY(INT)' for a int array, 'ARRAY(CHAR)' for char array, and so on. Currently only numeric casts are supported. |
scalar position | array_position(array, scalar, pos = 1 ) | Returns the position of the first matching scalar in the array. pos indicates the position to start searching, if not specified it defaults to 1. |
array elements positions | array_positions(array, array ) | Same as array_position(). But this looks for all elements stored in the right array, and return their respective positions in the left array. |
array append | array_append(array, value) | Add value to the back of an array |
array prepend | array_prepend(value, array) | Add value to the front of an array |
array remove | array_remove(array, value) | remove value from the array |
array replace | array_replace(array, old_value, new_value) | replace value by another in an array |
convert array to string | char(array) | Converts array to its string representation. |
convert array to string | array_to_string(array, delimiter [, nullstr]) | Converts array to a list of elements seperated by 'delimiter'. Nested arrays are flattened. NULL values are either ignored, or replaced by nullstr if provided. |
convert string to array | string_to_array(str, format) | Converts the string representation of an array (e.g 'int[1,2,NULL]') into an array. The format string is the same as in cast_to_array. |
Function | SQL statement | Result |
|---|---|---|
type constructor | int[](1,2) | int array with values 1 and 2 |
constructor with no type | array[1,2] | bigint array with values 1 and 2 |
array_cat | array_cat(array[2, 3], array[4, 5]) | array[2, 3, 4, 5] |
array_length | array_length(array[2, 3]) | 2 |
cast_to_array | cast_to_array(array[2.5, 3.4], 'ARRAY(INT)') | int[](2,3) |
array_position | array_position(array[1, 2, 1], 1) | 1 |
| array_position(array[1, 2, 3, 4, 1], 1, 3) | 5 |
array_positions | array_positions(array[1, 2, 3, 4, 1], 1) | int[](1,5) |
array_append | array_append(array[1,2], 3) | array[1,2,3] |
array_prepend | array_prepend(0, array[1,2]) | array[0,1,2] |
array_remove | array_remove(array[1,2], 1) | array[2] |
array_replace | array_replace(array[1,2,4],4,3) | array[1,2,3] |
softmax | softmax(float[](2.0, 2.0, 2.0, NULL, 2.0)) | float[0.25, 0.25, 0.25, 0.0, 0.25] |
array_sum | array_sum(int[](1, 2, 3)) | 6 |
array_min | array_min(int[](1, -2, 3)) | -2 |
array_argmax | array_argmax(int[](1, 3, 2)) | 1 |
char(<array value>) | char(array[1,2]) | 'bigint[1,2]' |
array_to_string | array_to_string(int[1,2,3,NULL,5], ',', '*') | '1,2,3,*,5' |
string_to_array | string_to_array('int[1,2]', 'ARRAY(INT)') | int[](1,2) |
Operator | Syntax | Purpose |
|---|---|---|
contains scalar | array @> scalar | Tell whether the array contains the scalar |
contains elements | array @> array | Tell whether the left array contains all elements from right array |
scalar is contained by | scalar <@ array | Tell whether the array contains the scalar |
array is contained by | array <@ array | Tell whether the right array contains all elements from left |
overlap | array && array | Tell whether any of the elements between the arrays are common |
access operator | array[element_index] | Returns the element stored in the given index position. Index is 1 based. Access out of bounds returns a NULL. |
slice operator | array[left_index:right_index] | Returns the subarray ranging from left_index to right_index inclusive. Index is 1 based. |
About the Array Slice Operator
- Each index is 1 based.
- If the array or either index value is NULL, the result of slicing is NULL.
- If the user excludes the left index, it’s equivalent to using 1. If the user excludes the right index, it’s equivalent to len(array). If they exclude both, it returns the full array.
- Values below zero for the left index will be converted to 1 and values beyond the array length for the right index will be converted to len(array). arrayVal[-1:4] is equivalent to arrayVal[1:4], and int[](1, 2, 3)[1:6] is equivalent to int[](1, 2, 3)[1:3].
- Ranges completely out of array bounds will return an empty array. array[1, 2][4:5] = array[]
- Sequential slices will slice each dimension of multidimensional arrays. array[array[1, 2, 3], array[4, 5, 6]][1:1][2:3] = array[array[2,3]]
- More than N sequential slice of an N-dimensional array, ex 3 sequential slices on a 2D array, will return an empty array. array[1, 2, 3][:][:] = []
- When slicing multidimensional arrays, slicing cannot be combined with the access operator. Any access operator [n] will be converted to [:n]. arrayVal[4][1:6] = arrayVal[:4][1:6]
- Slicing an array of tuples such as tuple<<int,int>>[tuple<<int,int>>(1,2),tuple<<int,int>>(3,4)][1:1][2:3] will slice [1:1] on the array and [2:3] on the tuple elements within the sliced array, returning the value tuple<<int>>[tuple<<int>>(2)].
Operator | SQL Predicate | Result |
|---|---|---|
Contains | array[1, 4, 3] @> array[3, 1] | True |
| array[1, 4, 3] @> array[3, 1, 3] | True |
| array[3, 1, 3] @> 1 | True |
Is contained by | array[2, 5] <@ array[2, 5, 3, 7, 9] | True |
| array[2, 2, 5] <@ array[2, 5, 3, 7, 9] | True |
| 1 <@ array[2, 5, 1] | True |
Overlap | array[1 ,2, 3] && array[1 ,6, 4] | True |
Multidimensional arrays are supported. As seen in these examples, each dimension, or length, at each level does not have to be the same. Null values are allowed at each dimension of an array.
Multidimensional Examples
Function | SQL statement | Result |
|---|---|---|
type constructor 2d | int[][](int[](1, NULL), array[1]) | 2d int array with values [1, NULL] and [1] |
constructor with no type 2d | array[int[](1), array[int(2)]] | 2d int array with values [1] and [2] |
2d array with null values | array[array[1, NULL], NULL] | 2d bigint array with values [1, NULL] and NULL |
2d array with different lengths | array[array[1], array[1,2], array[1,2,3]] | 2d bigint array with values [1], [1,2], and [1,2,3] |