SQL Reference > Array Functions

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.

NOTE: 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.

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 return null. If val does not exist within array, it returns 0.

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 is null, it will return null. If val does not exist within array, it returns 0.

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

LENGTH

LENGTH(array)

Returns length of array.

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

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)
[1, 2, 3]
SELECT
    SLICE(ARRAY_CREATE(1, 2, 3), 1, 2)
[1, 2]
SELECT
    SLICE(ARRAY_CREATE(1, 2, 3), -1)
[3]
SELECT
    SLICE(ARRAY_CREATE(1, 2, 3), 5)
Error: 'array index out of range'

ARRAY_DISTINCT

ARRAY_DISTINCT(array)

Returns an array with all duplicates removed.

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