Rockset

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