Skip to content

array.md

Latest commit

 

History

History
130 lines (104 loc) · 16.6 KB

array.md

File metadata and controls

130 lines (104 loc) · 16.6 KB
Raw

Array Operations

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 values can 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;

CROSS APPLY is another spelling for the same query:

CREATE VIEW v AS SELECT city, data.country
FROM data CROSS apply UNNEST(data.cities) AS city;

UNNEST applied to a NULL value returns an empty table. As a consequence, the queries above drop the rows of data where cities is NULL or empty. The join forms that instead keep such rows, LEFT JOIN UNNEST(...) ON TRUE and OUTER APPLY UNNEST(...), are not yet supported; see unsupported operations. To keep the rows where the array is NULL, substitute a one-element array in the UNNEST argument:

-- A row with a NULL cities array produces one row with a NULL city
CREATE VIEW v AS SELECT city, country
FROM data, UNNEST(COALESCE(cities, ARRAY[NULL])) AS t (city);

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.

A NULL array element is smaller than any other element, whatever the NULLS FIRST or NULLS LAST clause of an enclosing ORDER BY says.

On arrays, = is equivalent to IS NOT DISTINCT FROM and <> is equivalent to IS DISTINCT FROM: two NULL elements compare as equal, so ARRAY[1, NULL] = ARRAY[1, NULL] is true. See comparing complex values.

Examples:

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

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][SAFE_OFFSET(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 ) 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. The result is sorted in ascending order. ARRAY_EXCEPT(ARRAY [1, 2, 3], ARRAY [3,1,4]) => [2]
ARRAY_EXISTS( array, predicate ) predicate is a lambda expression with an argument that has the array element type, returning a Boolean. ARRAY_EXISTS returns true when the predicate returns true for at least one element. If the array is empty, returns false. If the result is not true and the predicate evaluates to NULL for any element, it returns NULL. ARRAY_EXISTS(ARRAY[1, -2, 3], x -> x > 0) => true
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. The result is sorted in ascending order. 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. Ignores null values. 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 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 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 elements of array, converted to strings, 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 are either in the left array or the right arrays, with no duplicates. The result is sorted in ascending order. 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 (ascendingOrder is true) or descending order (ascendingOrder is false) 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]
TRANSFORM( array, function ) function is a lambda expression with an argument that has the array element type. TRANSFORM returns a new array constructed by applying the function to every element of array. If array is NULL, it returns NULL TRANSFORM(ARRAY[1, 2, 3], x -> x % 2) => [1, 0, 1]