Skip to main content

Array Functions

SQL Array Functions and Usage.

FunctionDescriptionExampleResult
GET(array, index)Returns an element from the array by index (1-based)GET([1, 2], 2)2
LENGTH(array)Returns the length of the arrayLENGTH([1, 2])2
RANGE(start, end)Returns an array collected by [start, end)RANGE(1, 3)[1, 2]
ARRAY_CONCAT(array1, array2)Concats two arraysARRAY_CONCAT([1, 2], [3, 4][1,2,3,4]
ARRAY_CONTAINS(array, item)Checks if the array contains a specific elementARRAY_CONTAINS([1, 2], 1)1
ARRAY_INDEXOF(array, item)Returns the index(1-based) of an element if the array contains the elementARRAY_INDEXOF([1, 2, 9], 9)3
ARRAY_SLICE(array, start[, end])Extracts a slice from the array by index (1-based)ARRAY_SLICE([1, 21, 32, 4], 2, 3)[21,32]
ARRAY_SORT(array)Sorts elements in the array in ascending orderARRAY_SORT([1, 4, 3, 2])[1,2,3,4]
ARRAY_AGGREGATE(array, name)Aggregates elements in the array with an aggregate function (sum, count, avg, min, max, any, ...)ARRAY_AGGREGATE([1, 2, 3, 4], 'SUM')10
ARRAY_UNIQUE(array)Counts unique elements in the array (except NULL)ARRAY_UNIQUE([1, 2, 3, 3, 4])4
ARRAY_DISTINCT(array)Removes all duplicates and NULLs from the array without preserving the original orderARRAY_DISTINCT([1, 2, 2, 4])[1,2,4]
ARRAY_PREPEND(item, array)Prepends an element to the arrayARRAY_PREPEND(1, [3, 4])[1,3,4]
ARRAY_APPEND(array, item)Appends an element to the arrayARRAY_APPEND([3, 4], 5)[3,4,5]
ARRAY_REMOVE_FIRST(array)Removes the first element from the arrayARRAY_REMOVE_FIRST([1, 2, 3])[2,3]
ARRAY_REMOVE_LAST(array)Removes the last element from the arrayARRAY_REMOVE_LAST([1, 2, 3])[1,2]
UNNEST(array)Unnests the array and returns the set of elementsUNNEST([1, 2])1
2
(2 rows)
note

ARRAY_SORT(array) can accept two optional parameters, order and nullposition, which can be specified through the syntax ARRAY_SORT(array, order, nullposition).

  • order specifies the sorting order as either ascending (ASC) or descending (DESC). Defaults to ASC.
  • nullposition determines the position of NULL values in the sorting result, at the beginning (NULLS FIRST) or at the end (NULLS LAST) of the sorting output. Defaults to NULLS FIRST.
note

ARRAY_AGGREGATE(array, name) supports the following aggregation functions, avg, count, max, min, sum, any, stddev_samp, stddev_pop, stddev, std, median, approx_count_distinct, kurtosis, skewness.

ARRAY_AGGREGATE(array, name) function also support rewrite as ARRAY_<name>(array). Following is a list of existing rewrites, array_avg, array_count, array_max, array_min, array_sum, array_any, array_stddev_samp, array_stddev_pop, array_stddev, array_std, array_median, array_approx_count_distinct, array_kurtosis, array_skewness.

note

UNNEST(array) can also be used as a table function.