SQL Reference > Object Functions

Object Functions

This page covers functions for constructing and manipulating objects (string-keyed maps) in Rockset.

Note: All examples that use the collection data refer to

OBJECT

OBJECT(keys, values)

Construct an object from an array of keys and an array of values. keys must be an array of strings. values must be an arbitrary array of the same length as keys.

ZIP

ZIP(entries)

Construct an object from an array of entries. Each entry in entry must itself be an array of size 2: the first element is the key (and must be a string), and the second element is the value.

ELEMENT_AT

ELEMENT_AT(obj, key)

Return the value corresponding to key, or NULL if key does not exist in obj.

25
SELECT
    ELEMENT_AT(data.obj, 'bar')
FROM
    data
'hello world'
SELECT
    ELEMENT_AT(data.obj, 'baz')
FROM
    data
[1, 2, 3]
SELECT
    ELEMENT_AT(data.obj, 'abc')
FROM
    data
null
SELECT
    ELEMENT_AT(data.obj, null)
FROM
    data
null
obj.key

Equivalent dot-notation to return the value corresponding to key, or NULL if key does not exist in obj.

SELECT
    data.obj.foo
FROM
    data
25
SELECT
    data.obj.bar
FROM
    data
'hello world'
SELECT
    data.obj.baz
FROM
    data
[1, 2, 3]
SELECT
    data.obj.abc
FROM
    data
null

LENGTH

LENGTH(obj)

Returns number of elements in obj.

SELECT
    LENGTH(data.obj)
FROM
    data
3
SELECT
    LENGTH(OBJECT())
0
SELECT
    LENGTH(null)
null

KEYS

KEYS(obj)

Return an array containing the keys of obj. The order is unspecified, but will be the same between KEYS(), VALUES(), and ITEMS().

SELECT
    KEYS(data.obj)
FROM
    data
['baz', 'bar', 'foo']
SELECT
    KEYS(OBJECT())
[]
SELECT
    KEYS(null)
null

VALUES

VALUES(obj)

Return an array containing the values of obj. The order is unspecified, but will be the same between KEYS(), VALUES(), and ITEMS().

SELECT
VALUES(data.obj)
FROM
    data
[[1, 2, 3], 'hello world', 25]
SELECT
VALUES(OBJECT())
[]
SELECT
VALUES(null)
null

ITEMS

ITEMS(obj)

Return an array containing the entries of obj. Each entry is a 2-element array; the first is the key, the second is the value. The order is unspecified, but will be the same between KEYS(), VALUES(), and ITEMS().

SELECT
    ITEMS(data.obj)
FROM
    data
[['baz', [1, 2, 3]], ['bar', 'hello world'], ['foo', 25]]
SELECT
    ITEMS(null)
null
SELECT
    ITEMS(OBJECT())
[]
SELECT
    ZIP(ITEMS(data.obj))
FROM
    data
{'baz': [1, 2, 3], 'bar': 'hello world', 'foo': 25}

MERGE

MERGE(a, b)

Return a new object containing the values from a and b. If the same key exists in both a and b, the value from b will overwrite the value from a.

For example if collection X has the form

+----------------------+----------------------+
| a                    | b                    |
|----------------------+----------------------|
| {'baz': 2, 'foo': 1} | {'baz': 4, 'bar': 3} |
+----------------------+----------------------+

Then

SELECT
    MERGE(X.a, X.b)
FROM
    X
{'baz': 4, 'bar': 3, 'foo': 1}
SELECT
    MERGE(X.a, OBJECT())
FROM
    X
{'baz': 2, 'foo': 1}
SELECT
    MERGE(OBJECT(), X.b)
FROM
    X
{'baz': 4, 'bar': 3}
SELECT
    MERGE(OBJECT(), OBJECT())
{}
SELECT
    MERGE(OBJECT(), null)
null

ERASE

ERASE(obj, k)

If k is a string, return a new object where the key k is erased. If k is an array of strings, return a new object where the keys in k are erased.

SELECT
    ERASE(data.obj, 'foo')
FROM
    data
{'baz': [1, 2, 3], 'bar': 'hello world'}
SELECT
    ERASE(data.obj, 'abc')
FROM
    data
{'baz': [1, 2, 3], 'bar': 'hello world', 'foo': 25}
SELECT
    ERASE(data.obj, null)
FROM
    data
null
SELECT
    ERASE(data.obj, ARRAY_CREATE('foo', 'bar'))
FROM
    data
{'baz': [1, 2, 3]}