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.

Comparison Operations on Arrays

Comparison operations (=, <>, !=, >, <, >=, <=) in arrays occur lexicographically, from left to right. The elements are compared one by one until a difference is found. Arrays can be compared even if they are different in size.

Examples:

• [45] > [22]
• [23, 56, 16] > [23, NULL]

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_CONCAT( array, [ array ]* )	Returns NULL if any argument is NULL. Concatenates a list of arrays.	ARRAY_CONCAT(ARRAY [1, 2, 3], ARRAY[4]) => [1, 2, 3, 4]
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. 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_EXCEPT( left, right )	Returns NULL if any argument is NULL. Returns an array with all elements in the left array that do not appear in the right array, removing duplicates.	ARRAY_EXCEPT(ARRAY [1, 2, 3], ARRAY [3,1,4]) => [2]
ARRAY_INSERT( array, pos, element )	Returns NULL if array or pos are NULL. Insert element in array at specified position, padding with NULLs if necessary. If pos is negative, it is considered from the end of the array. Produces error when pos is zero or very large	ARRAY_INSERT(ARRAY [1, 2, 3], 3, 4) => [1,2,4,3]
ARRAY_INTERSECT( left, right )	Returns NULL if any argument is NULL. Returns an array with all elements that appear in common in both the left array and right arrays, with no duplicates.	ARRAY_INTERSECT(ARRAY [1, 2, 3], ARRAY [3,1,4]) => [1,3]
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.	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. 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. 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 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
ARRAY_UNION( left, right )	Returns NULL if any argument is NULL. Returns an array with all elements that either in the left array or the right arrays, with no duplicates.	ARRAY_UNION(ARRAY [1, 2, 3], ARRAY [3,1,4]) => [1,2,3,4]
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]