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:

SELECT city, country
FROM data, UNNEST(cities) AS t (city)

where we assume that the data table has a schema defined as CREATE TABLE data AS (CITIES VARCHAR ARRAY, COUNTRY VARCHAR).

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_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_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 NULLs 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]