Array Functions

This page covers functions for constructing and manipulating arrays in Rockset.

Refer also to the section on UNNEST for information on querying data inside arrays.

💡

Indexing of arrays in SQL

In standard SQL, all arrays are 1-based, so the first element is at index 1 and the last element is at index LENGTH(array).

ARRAY_CREATE

ARRAY_CREATE(val1, val2, ...) Construct an array from literals.

SELECT ARRAY_CREATE(5, 'hello', null)

SELECT ARRAY_CREATE()

You can also construct arrays directly with brackets [].

SELECT [1,2,3]

ARRAY_POSITION

ARRAY_POSITION(array, val) Return a 1-based index of the first occurrence of val if it is found within array. If val is null, it will look for occurrence of null in the array. If val does not exist within array, it returns 0.

SELECT ARRAY_POSITION(ARRAY_CREATE(5, null, 5), 5)

SELECT ARRAY_POSITION(ARRAY_CREATE(5, null, 5), 'hello')

SELECT ARRAY_POSITION(ARRAY_CREATE(5, 'hello', null), null)

ARRAY_REMOVE

ARRAY_REMOVE(array, val) Returns the array with all occurrences of value removed.

SELECT ARRAY_REMOVE(ARRAY_CREATE(3, 1, 2), 1)

SELECT ARRAY_REMOVE(ARRAY_CREATE('a', 'b', 'c'), 'c')

SELECT ARRAY_REMOVE(ARRAY_CREATE(3, 1.6, -0.83, 1.6), 1.6)

SELECT ARRAY_REMOVE(ARRAY_CREATE(3, 3, 3), 3)

ARRAY_LAST_POSITION

ARRAY_LAST_POSITION(array, val) Return a 1-based index of the last occurrence of val if it is found within array. If val does not exist within array, it returns 0.

SELECT ARRAY_LAST_POSITION(ARRAY_CREATE(5, null, 5), 5)

SELECT ARRAY_LAST_POSITION(ARRAY_CREATE(5, null, 5), 'hello')

SELECT ARRAY_LAST_POSITION(ARRAY_CREATE(5, 'hello', null), null)

LENGTH

LENGTH(obj) Returns number of elements in obj.

SELECT LENGTH(ARRAY_CREATE())

SELECT LENGTH(ARRAY_CREATE(1, 2, 'hello', null))

SELECT LENGTH(null)

CARDINALITY

CARDINALITY(array) Alias of LENGTH.

SLICE

SLICE(array, start[, length]) Returns a subset of array starting from index start (or starting from the end if start is negative) with length length (unless start + length > LENGTH(array)). length is optional and defaults to length of array if unspecified. If ABS(index) > LENGTH(array) it will throw an error.

SELECT SLICE(ARRAY_CREATE(1, 2, 3), 1)

SELECT SLICE(ARRAY_CREATE(1, 2, 3), 1, 2)

SELECT SLICE(ARRAY_CREATE(1, 2, 3), -1)

SELECT SLICE(ARRAY_CREATE(1, 2, 3), 5)

ELEMENT_AT

ELEMENT_AT(array, val) Returns element of array at index val. If val < 0, this function accesses elements from the last to the first.

SELECT ELEMENT_AT(ARRAY_CREATE(4, 5, 6), 2)

SELECT ELEMENT_AT(ARRAY_CREATE(4, 5, 6), -1)

SELECT ELEMENT_AT(ARRAY_CREATE(4, 5, 6), 0)

ARRAY_CONTAINS

ARRAY_CONTAINS(array, element) Returns true if array contains the element.

SELECT ARRAY_CONTAINS(ARRAY_CREATE(1, 2, 3), 2)

SELECT ARRAY_CONTAINS(ARRAY_CREATE(1, 2, 3), 12)

NOT ARRAY_CONTAINS

NOT ARRAY_CONTAINS(array, element) Returns true if array does not contain the element.

SELECT NOT ARRAY_CONTAINS(ARRAY_CREATE(1, 2, 3), 4)

💡

Note on NULL values with [NOT] ARRAY_CONTAINS

When using ARRAY_CONTAINS, NULL values of element will return false unless NULL is specified as a value in array.

When using NOT ARRAY_CONTAINS, NULL values of element will return true unless NULL is specified as a value in array.

Please note this handling of NULL values when using these functions in queries.

ARRAY_CONTAINS_ANY

ARRAY_CONTAINS_ANY(array1, array2) Returns true if array1 and array2 have any elements in common.

SELECT ARRAY_CONTAINS_ANY([1, 2, 3], [0, 2, 4])

SELECT ARRAY_CONTAINS_ANY([1, 2, 3], [4, 5, 6])

SELECT ARRAY_CONTAINS_ANY([1, 2, 3], [])

NOT ARRAY_CONTAINS_ANY

NOT ARRAY_CONTAINS_ANY(array1, array2) Returns true if array1 and array2 do not have any elements in common.

SELECT NOT ARRAY_CONTAINS_ANY([1, 2, 3], [0, 4, 9])

💡

Note on NULL values with [NOT] ARRAY_CONTAINS_ANY

When using ARRAY_CONTAINS_ANY, NULL values in array2 will return false unless NULL is specified as a value in array1.

When using NOT ARRAY_CONTAINS_ANY, NULL values in array2 will return true unless NULL is specified as a value in array1.

Please note this handling of NULL values when using these functions in queries.

ARRAY_CONTAINS_PREFIX

ARRAY_CONTAINS_PREFIX(string_array, prefix) returns true if any element of string_array starts with the given prefix, case-sensitively. Please note the function will error if string_array contains any non-nullish non-string elements (e.g. if it contains numbers, booleans, etc). Nullish values (null, undefined) will simply be skipped over.

SELECT ARRAY_CONTAINS_PREFIX(['rockset', 'rockstar'], 'rock')

SELECT ARRAY_CONTAINS_PREFIX(['rockset', 'rockstar', NULL], 'rock')

SELECT ARRAY_CONTAINS_PREFIX(['anything'], '')

SELECT ARRAY_CONTAINS_PREFIX([], '')

SELECT ARRAY_CONTAINS_PREFIX(['welcome', 'wellness', 1], 'wel')

Attempting to pass in a non-nullish non-string value as the prefix will similarly raise an error:

SELECT ARRAY_CONTAINS_PREFIX(['rockset', 'rockstar'], 4)

Attempting to pass NULL for either parameter will propagate the null:

SELECT ARRAY_CONTAINS_PREFIX(NULL, 'rock');
SELECT ARRAY_CONTAINS_PREFIX(['rockset', 'rockstar'], NULL);

ARRAY_DISTINCT

ARRAY_DISTINCT(array) Returns an array with all duplicates removed.

SELECT ARRAY_DISTINCT(ARRAY_CREATE(1, 1, 1))

SELECT ARRAY_DISTINCT(ARRAY_CREATE(1, 2, 3))

ARRAY_FLATTEN

ARRAY_FLATTEN(array) Flattens an array(array(T)) to array(T) by concatenating the contained arrays. Each element in the array must be of array type.

SELECT ARRAY_FLATTEN(ARRAY_CREATE(ARRAY_CREATE(1, 2, 3)))

SELECT ARRAY_FLATTEN(ARRAY_CREATE(ARRAY_CREATE(1, 2), ARRAY_CREATE(3)))

ARRAY_INTERSECT

ARRAY_INTERSECT(array1, array2) Returns an intersection of the two arrays, with all duplicates removed.

SELECT ARRAY_INTERSECT(ARRAY_CREATE(1, 1, 2, 3), ARRAY_CREATE(1, 3, 4))

ARRAY_UNION

ARRAY_UNION(array1, array2) Returns a union of the two arrays, with all duplicates removed.

SELECT ARRAY_UNION(ARRAY_CREATE(1, 1, 2, 3), ARRAY_CREATE(1, 3, 4))

ARRAY_MAX

ARRAY_MAX(array) Returns an element which is greater than or equal to all other elements of the array. Returns null if one of the array elements is null.

SELECT ARRAY_MAX(ARRAY_CREATE(1))

SELECT ARRAY_MAX(ARRAY_CREATE(1, 2, 3))

SELECT ARRAY_MAX(ARRAY_CREATE(1, null, 3))

ARRAY_MIN

ARRAY_MIN(array) Returns an element which is less than or equal to all other elements of the array. Returns null if one of the array elements is null.

SELECT ARRAY_MIN(ARRAY_CREATE(1))

SELECT ARRAY_MIN(ARRAY_CREATE(1, 2, 3))

SELECT ARRAY_MIN(ARRAY_CREATE(1, null, 3))

ARRAY_MAP

ARRAY_MAP(function_name, array) Given the name of a function as a string, and an array, apply the function to each element of the array. The function must be a scalar function, and must accept exactly one argument.

SELECT ARRAY_MAP('round', ARRAY_CREATE(0, 0.4, 1.2))

ARRAY_JOIN

ARRAY_JOIN(array, delimiter, nullReplacement) Concatenates the elements of array using delimiter and an optional nullReplacement string to replace nulls. Accepts only string types.

SELECT ARRAY_JOIN(ARRAY_CREATE('a', 'b', 'c'), ':') x

SELECT ARRAY_JOIN(ARRAY_CREATE('a', 'b', 'c'), '') x

SELECT ARRAY_JOIN(ARRAY_CREATE('a', null, 'c'), ':', 'nullReplacementStr') x

SELECT ARRAY_JOIN(ARRAY_CREATE(null, null, null), ',', 'nullReplacementStr') x

SELECT ARRAY_JOIN(ARRAY_CREATE('a', 'b', 'c'), ':,%') x

SELECT ARRAY_JOIN(ARRAY_CREATE('a', null, 'b'), ',') x

ARRAY_EXCEPT

ARRAY_EXCEPT(array1, array2) Returns an array of elements in array1 but not in array2, without duplicates.

SELECT ARRAY_EXCEPT(ARRAY_CREATE(1, 2, 3), ARRAY_CREATE(1, 3, 4)) x

SELECT ARRAY_EXCEPT(ARRAY_CREATE(1, 2, 3), ARRAY_CREATE(4, 5, 6)) x

SELECT ARRAY_EXCEPT(ARRAY_CREATE(1, 2, 1, 2, 3), ARRAY_CREATE(1, 2, 1, 1, 2, 2, 4)) x

SELECT ARRAY_EXCEPT(ARRAY_CREATE(1, 1, 1, 1, 3), ARRAY_CREATE(4)) x

ARRAY_SHUFFLE

ARRAY_SHUFFLE(array) Returns a shuffled copy of the input array in any order.

SELECT ARRAY_SHUFFLE(ARRAY_CREATE(1, 2, 3)) x

ARRAY_SORT

ARRAY_SORT(array) Returns a sorted copy of the input array.

SELECT ARRAY_SORT(ARRAY_CREATE(3, 1, 2)) x

SELECT ARRAY_SORT(ARRAY_CREATE('c', 'b', 'a')) x

SELECT ARRAY_SORT(ARRAY_CREATE(3, 1.6, -0.83)) x

SELECT ARRAY_SORT(ARRAY_CREATE(OBJECT(ARRAY_CREATE('hhh'), ARRAY_CREATE(1)), 'def', 'abc', 3, 1.6)) x

ARRAY_CONCAT

ARRAY_CONCAT(array1, array2, ...) Returns the concatenation of the input arrays.

SELECT ARRAY_CONCAT([10], [20], [30]) x

SELECT ARRAY_CONCAT([10], ['hello', 'world']) x

REPEAT

REPEAT(val, count) Constructs an array of val repeated count times. count must be an integer.

SELECT REPEAT(1, 5)

SELECT REPEAT('hello', 3)

SELECT REPEAT('rockset', 0)

SEQUENCE

SEQUENCE(start, stop[, step]) Constructs an array from start to stop with each value increasing or decreasing by step. If step is not provided, it defaults to 1 if start is less than stop, or -1 if start is greater than stop. start, stop, and step must all be integers.

SELECT SEQUENCE(1, 5)

SELECT SEQUENCE(5, 1)

SELECT SEQUENCE(1, 10, 2)

SELECT SEQUENCE(10, 1, -3)

List of functions defined in this section: