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_REMOVE

ARRAY_REMOVE(array, val)

Returns the array with all occurrences of value removed.

SELECT

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

[3, 2]

SELECT

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

["a", "b"]

SELECT

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

[3, -0.83]

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)

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'

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)

5

SELECT

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

6

SELECT

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

{ "__rockset_type": "undefined" }

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_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])

true

SELECT

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

false

SELECT

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

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_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)))

[1, 2, 3]

SELECT

    ARRAY_FLATTEN(

        ARRAY_CREATE(ARRAY_CREATE(1, 2), ARRAY_CREATE(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]

ARRAY_SORT

ARRAY_SORT(array)

Returns a sorted copy of the input array.

SELECT

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

[1, 2, 3]

SELECT

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

["a", "b", "c"]

SELECT

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

[-0.83, 1.6, 3]

SELECT

    ARRAY_SORT(

        ARRAY_CREATE(

            OBJECT(ARRAY_CREATE('hhh'), ARRAY_CREATE(1)),

            'def',

            'abc',

            3,

            1.6

        )

    ) x

[1.6, 3, "abc", "def", {"hhh": 1}]

ARRAY_CONCAT

ARRAY_CONCAT(array1, array2, ...)

Returns the concatenation of the input arrays.

SELECT

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

[10, 20, 30]

SELECT

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

[10, "hello", "world"]

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]