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.

SELECT
    ARRAY_CREATE(5, 'hello', null)
[5, "hello", null]
SELECT
    ARRAY_CREATE()
[]

#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)
1
SELECT
    ARRAY_POSITION(ARRAY_CREATE(5, null, 5), 'hello')
0
SELECT
    ARRAY_POSITION(ARRAY_CREATE(5, 'hello', null), null)
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)
3
SELECT
    ARRAY_LAST_POSITION(ARRAY_CREATE(5, null, 5), 'hello')
0
SELECT
    ARRAY_LAST_POSITION(ARRAY_CREATE(5, 'hello', null), null)
3

#LENGTH

LENGTH(obj)

Returns number of elements in obj.

SELECT
    LENGTH(ARRAY_CREATE())
0
SELECT
    LENGTH(ARRAY_CREATE(1, 2, 'hello', null))
4
SELECT
    LENGTH(null)
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)
[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_CONTAINS

ARRAY_CONTAINS(array, element)

Returns true if array contains the element.

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

#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]

#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))
[1, 3]

#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))
[1, 2, 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))
1
SELECT
    ARRAY_MAX(ARRAY_CREATE(1, 2, 3))
3
SELECT
    ARRAY_MAX(ARRAY_CREATE(1, null, 3))
null

#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))
1
SELECT
    ARRAY_MIN(ARRAY_CREATE(1, 2, 3))
1
SELECT
    ARRAY_MIN(ARRAY_CREATE(1, null, 3))
null

#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))
[0, 0, 1]

#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
a:b:c
SELECT
    ARRAY_JOIN(ARRAY_CREATE('a', 'b', 'c'), '') x
abc
SELECT
    ARRAY_JOIN(
        ARRAY_CREATE('a', null, 'c'),
        ':',
        'nullReplacementStr'
    ) x
a:nullReplacementStr:c
SELECT
    ARRAY_JOIN(
        ARRAY_CREATE(null, null, null),
        ',',
        'nullReplacementStr'
    ) x
nullReplacementStr,nullReplacementStr,nullReplacementStr
SELECT
    ARRAY_JOIN(ARRAY_CREATE('a', 'b', 'c'), ':,%') x
a:,%b:,%c
SELECT
    ARRAY_JOIN(ARRAY_CREATE('a', null, 'b'), ',') x
a,,b

#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
[2]
SELECT
    ARRAY_EXCEPT(ARRAY_CREATE(1, 2, 3), ARRAY_CREATE(4, 5, 6)) x
[1, 2, 3]
SELECT
    ARRAY_EXCEPT(
        ARRAY_CREATE(1, 2, 1, 2, 3),
        ARRAY_CREATE(1, 2, 1, 1, 2, 2, 4)
    ) x
[3]
SELECT
    ARRAY_EXCEPT(ARRAY_CREATE(1, 1, 1, 1, 3), ARRAY_CREATE(4)) x
[1, 3]

#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
[2, 1, 3]

#REPEAT

REPEAT(val, count)

Constructs an array of val repeated count times. count must be an integer.

SELECT
    REPEAT(1, 5)
[1, 1, 1, 1, 1]
SELECT
    REPEAT("hello", 3)
["hello", "hello", "hello"]
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)
[1, 2, 3, 4, 5]
SELECT
    SEQUENCE(5, 1)
[5, 4, 3, 2, 1]
SELECT
    SEQUENCE(1, 10, 2)
[1, 3, 5, 7, 9]
SELECT
    SEQUENCE(10, 1, 3)
[10, 7, 4, 1]
Join us on Slack!
Building on Rockset? Come chat with us!