Operations on arrays
An array type can be created by applying the ARRAY
suffix to
another type. For example INT ARRAY
is an array of integers.
Array indexes start from 1. Array sizes are limited to 2^31 elements.
Array elements may be nullable types, e.g., INT ARRAY NULL
.
Multidimensional arrays are possible, e.g. VARCHAR ARRAY ARRAY
is a two-dimensional array.
In CREATE TABLE
and CREATE TYPE
declarations there is no way to
specify the nullability of the elements of an ARRAY
. The compiler
will always assume that array elements are nullable:
CREATE TABLE T(a INT ARRAY);
Table T
will have a single column a
whose values are nullable
arrays; the array elements will be nullable INT values.
Array literals
Array literals have the syntax ARRAY[
expr [,
expr]*]
. An example
creating a nested array is: ARRAY[ARRAY[1, 2], ARRAY[3, 4]]
.
The UNNEST
SQL Operator
The UNNEST
operator takes an ARRAY
and returns a table with a
row for each element in the ARRAY
: UNNEST(ARRAY) [WITH ORDINALITY]
. If the input is an array with 5 elements, the output
is a table with 5 rows, each row holding one element of the array.
The additional keyword WITH ORDINALITY
creates an output table
with two columns, where the second column is the index of the element
within the array, with numbering starting at 1. If the array contains
duplicated values, the resulting table will be a multiset.
The UNNEST
operator can be used in self-joins as follows:
CREATE TABLE data(CITIES VARCHAR ARRAY, COUNTRY VARCHAR);
CREATE VIEW V AS SELECT city, country
FROM data, UNNEST(cities) AS t (city);
The previous query is a shortcut for a CROSS-JOIN query:
CREATE VIEW V AS SELECT city, data.country
FROM data CROSS JOIN UNNEST(data.cities) AS city;
UNNEST
applied to a NULL
value returns an empty array.
Note that applying UNNEST
to an ARRAY
of structure-typed objects
will produce a collection whose columns are the fields of the
structure, instead of a collection with a single structure-typed
column.
Predefined functions on array values
Function | Description | Example |
---|---|---|
array[ index] | where index is an expression that evaluates to an integer, and array is an expression that evaluates to an array. Returns the element at the specified position. If the index is out of bounds, the result is NULL . | ARRAY[2,3][2] => 3 |
array[SAFE_OFFSET( index)] | where index is an expression that evaluates to an integer, and array is an expression that evaluates to an array. Returns the element at the specified position, with first element at index 0. If the index is out of bounds, the result is NULL . | ARRAY[2,3][2] => NULL |
ARRAY( [ exp [, exp]* ]) | Array constructor function. Can have zero arguments. | ARRAY() => [] , ARRAY(NULL, 2) => [NULL, 2] |
ARRAY_APPEND( array , element ) | Appends an element to the end of the array and returns the result. If the array is NULL , the function will return NULL . If the element is NULL , the NULL element will be added to the end of the array. | ARRAY_APPEND(ARRAY [1, 2], 3) => [1, 2, 3] |
ARRAY_COMPACT( array ) | Returns NULL if array is NULL . Returns a new array by removing all the NULL values from the array. | ARRAY_COMPACT(ARRAY [1, 2, 3, NULL]) => [1, 2, 3] |
ARRAY_CONTAINS( array , element ) | Returns NULL if element is NULL . The right argument must have the same type as array elements, otherwise result is undefined. Returns true if the array contains the element. | ARRAY_CONTAINS(ARRAY [1, 2, 3], 2) => true |
ARRAY_DISTINCT( array ) | Returns NULL if array is NULL . Returns a new array removing duplicate values from the array and keeping the order of elements. | ARRAY_DISTINCT(ARRAY [1, 1, 2, 2]) => [1, 2] |
ARRAY_JOIN | Another name for ARRAY_TO_STRING | |
ARRAY_LENGTH( array ) | Another name for CARDINALITY | ARRAY_LENGTH( ARRAY [1, 2, 3]) => 3 |
ARRAY_MAX( array ) | Returns the maximum value in the array | ARRAY_MAX(ARRAY [9, 1, 2, 4, 8, null]) => 9 |
ARRAY_MIN( array ) | Returns the minimum value in the array. Ignores null values. | ARRAY_MIN(ARRAY [9, 1, 2, 5, 8, null]) => 1 |
ARRAYS_OVERLAP( array1 , array2 ) | Returns NULL if array1 or array2 is NULL . Errors if the two arrays are of different types. Returns true if the two arrays have at least one element in common. Returns NULL if they do not have elements in common but any of them has a NULL value. Returns false otherwise. | ARRAYS_OVERLAP(ARRAY [1, 2, 3], ARRAY [3, 4, 5]) => true |
ARRAY_POSITION( array , element ) | Returns NULL if array or element is NULL . The right argument must have the same type as array elements, otherwise result is undefined. Returns the (1-based) index of the first element of the array as a long. Returns 0 if element doesn't exist in array. | ARRAY_POSITION(ARRAY [1, 3, 4, 6], 4) => 3 |
ARRAY_PREPEND( array , element ) | Prepends an element to the start of the array and returns the result. If the array is NULL , the function will return NULL . If the element is NULL , the NULL element will be added to the start of the array. | ARRAY_PREPEND(ARRAY [2, 3], 1) => [1, 2, 3] |
ARRAY_REMOVE( array , element ) | Returns NULL if array or element is NULL . The right argument must have the same type as array elements, otherwise result is undefined. Returns a new array removing all elements that are equal to element from the given array. | ARRAY_REMOVE(ARRAY [2, 2, 6, 8], 2) => [6, 8] |
ARRAY_REVERSE( array ) | Returns NULL if array is NULL . Returns array with the elements in reverse order. | ARRAY_REVERSE(ARRAY [2, 2, 6, 8]) => [8, 6, 2, 2] |
ARRAY_REPEAT( element , count ) | Returns the array containing element count times. If count is negative, an empty array is returned. If count is NULL , NULL is returned. If element is NULL , an array containing count number of NULL s is returned. | ARRAY_REPEAT(3, 4) => [3, 3, 3, 3] |
ARRAY_SIZE( array ) | Another name for CARDINALITY | ARRAY_SIZE( ARRAY [1, 2, 3]) => 3 |
ARRAY_TO_STRING( array , separator [, null_string ]) | Concatenates the values of the string array array, separated by the separator string. If null_string is given and is not NULL , then NULL array entries are represented by that string; otherwise, they are omitted. | ARRAY_TO_STRING(ARRAY[1, 2, 3, NULL, 5], ',', '*') => 1,2,3,*,5 |
CARDINALITY( array ) | Returns the size of the array expression (number of elements). | CARDINALITY(ARRAY[2,3]) => 2 |
ELEMENT( array ) | Returns the single element of an array of any type. If the array has zero elements, returns NULL . If the array has more than one element, it causes a runtime exception. | ELEMENT(ARRAY[2]) => 2 |
SORT_ARRAY( array , [ ascendingOrder ]) | Returns a new array, sorted in ascending or descending order according to the natural ordering of the array elements. The default order is ascending if ascendingOrder is not specified. Null elements are considered to be the smallest. | SORT_ARRAY(ARRAY [4, 7, 1, null]) => [null, 1, 4, 7] |