Object Functions
- Capella Columnar
- reference
This topic describes the builtin SQL++ for Capella columnar object functions.
object_add
-
Syntax:
object_add(object, new_attr_key, new_attr_value)
-
Adds a new field—name-value pair—to a given object. This function does not update an existing field in a given object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object. -
new_attr_key
: A string, or an expression which evaluates to a string, representing a field name. -
new_attr_value
: A value, or any expression which evaluates to a value.
-
-
Return Value:
-
The original JSON object, also including the added field.
-
If you add a duplicate field—that is, if the name is found—this function returns the object unmodified.
-
If
new_attr_key
is NULL, it returns a NULL value. -
If
new_attr_key
is MISSING, it returns a MISSING value. -
If
new_attr_value
is MISSING, it returns the object unmodified. -
If
object
is not an object, or NULL, it returns a NULL value object.
-
-
Example:
object_add({"a": 1}, "b", 2);
-
The expected result is:
{ "a": 1, "b": 2 }
object_concat
-
Syntax:
object_concat(obj1, obj2 ...) object_concat(array)
-
Concatenates the input objects. This function has two possible syntaxes. The first requires any number of object arguments. The second requires a single array argument, containing any number of objects.
You can use the array syntax in situations where you do not know ahead of time how many input objects there are to concatenate; or where you want to concatenate objects which are generated dynamically, for example by a subquery.
-
Arguments:
-
obj1
,obj2
…: Objects, or expressions that evaluate to objects. -
array
: An array of objects, or an expression that evaluates to an array of objects.
-
-
Return Value:
-
An object constructed by concatenating all the input objects. If there is only one input object, it is returned unchanged. If any of the input objects contain the same attribute name, the attribute from the last relevant object in the input list is copied to the output; similarly-named attributes from earlier objects in the input list are ignored.
-
null
if the function contains more than one array argument; if the function contains a mixture of array and object arguments; if the function has an array argument which is empty; if the function has no arguments; or if the function contains any argument that is not an array or object.
-
-
Example — using object syntax:
object_concat({"abc": 0}, {"abc": 1}, {"def": 2}, {"ghi": 3});
-
The expected result is:
{ "ghi": 3, "def": 2, "abc": 1 }
-
Example — using array syntax:
object_concat([{"abc": 0}, {"abc": 1}, {"def": 2}, {"ghi": 3}]);
-
The expected result is:
{ "ghi": 3, "def": 2, "abc": 1 }
-
Example — using subquery:
object_concat((SELECT VALUE {custid: name} FROM customers));
-
The expected result is:
{ "C41": "R. Dodge", "C25": "M. Sinclair", "C31": "B. Pruitt", "C47": "S. Logan", "C37": "T. Henry", "C13": "T. Cody", "C35": "J. Roberts" }
object_length
-
Syntax:
object_length(object)
-
Counts the number of fields—name-value pairs—in an object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object.
-
-
Return Value:
-
The number of fields in the object.
-
-
Example:
object_length({"abc":1, "def":2, "ghi":3});
-
The expected result is:
3
object_names
-
Syntax:
object_names(object)
-
Returns the names of all fields—name-value pairs—in an object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object.
-
-
Return Value:
-
An array containing the field names of the object.
-
-
Example:
object_names({"a":1, "b":2, "c":3});
-
The expected result is:
[ "a", "b", "c" ]
object_pairs
-
Syntax:
object_pairs(object)
-
Returns the names and values of all fields—name-value pairs—in an object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object.
-
-
Return Value:
-
An array of objects, each of which contains the name and value of one field in the original object.
-
-
Example:
object_pairs({"abc":1, "def":2, "ghi":3});
-
The expected result is:
[ { "name": "abc", "value": 1 }, { "name": "def", "value": 2 }, { "name": "ghi", "value": 3 } ]
object_put
-
Syntax:
object_put(object, attr_key, attr_value)
-
Adds a new field (name-value pair), or updates an existing field in a given object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object. -
attr_key
: A string, or an expression which evaluates to a string, representing a field name. -
attr_value
: A value, or any expression which evaluates to a value.
-
-
Return Value:
-
The original JSON object, also including the field.
-
If
attr_key
is found in the object, this function replaces the corresponding field value byattr_value
. -
If
attr_key
is MISSING, it returns a MISSING value. -
If
attr_key
is not a string, it returns a NULL value. -
If
attr_value
is MISSING, it deletes the corresponding existing field if any, likeobject_remove()
.
-
-
Example:
object_put({"a": 1, "b": 2}, "a", 3);
-
The expected result is:
{ "a": 3, "b": 2 }
object_rename
-
Syntax:
object_rename(object, old_attr_key, new_attr_key)
-
Renames a field—name-value pair—in the JSON input object.
-
Arguments:
-
object
: Any JSON object, or SQL++ expression that can evaluate to a JSON object. -
old_attr_key
: A string, or an expression which evaluates to a string, representing the old, original field name inside the JSON object. -
new_attr_key
: A string, or an expression which evaluates to a string, representing the new field name to replaceold_attr_key
inside the JSON object.
-
-
Return Value:
-
The JSON object
object
with the updated field name. -
If
object
is not an object, or is NULL, the function returns a NULL value. -
If
old_attr_key
ornew_attr_key
is not a string, or is NULL, the function returns a NULL value. -
If any argument is MISSING, the function returns a MISSING value.
-
-
Example:
object_rename({"name": 1}, "name", "new_name");
-
The expected result is:
{ "new_name": 1 }
object_remove
-
Syntax:
object_remove(object, attr_key)
-
Removes the specified field—name-value pair—from the given object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object. -
attr_key
: A string, or an expression which evaluates to a string, representing the name of the field to remove.
-
-
Return Value:
-
The updated object.
-
If
object
is NULL, or is not an object, the function returns a NULL value. -
If
attr_key
is NULL, or is not a string, the function returns a NULL value. -
If any argument is MISSING, the function returns a MISSING value.
-
Example:
object_remove({"abc": 1, "def": 2, "ghi": 3}, "def");
-
The expected result is:
{ "abc": 1, "ghi": 3 }
object_replace
-
Syntax:
object_replace(object, old_attr_value, new_attr_value)
-
Replaces all occurrences of a value in the JSON input object.
-
Arguments:
-
object
: Any JSON object, or SQL++ expression that can evaluate to a JSON object. -
old_attr_value
: A value, or any expression which evaluates to a value, representing the old (original) value inside the JSON object. -
new_attr_value
: A value, or any expression which evaluates to a value, representing the new value to replaceold_attr_value
inside the JSON object.
-
-
Return Value:
-
The JSON object
object
with the new value. -
If
object
is not an object, the function returns a NULL value. -
If
object
orold_attr_value
is NULL, the function returns a NULL value. -
If any argument is MISSING, the function returns a MISSING value.
-
-
Example:
object_replace({"abc": 1, "def": 2, "ghi": 3}, 3, "xyz");
-
The expected result is:
{ "abc": 1, "def": 2, "ghi": "xyz" }
object_unwrap
-
Syntax:
object_unwrap(object)
-
Enables you to unwrap the value from an object containing a single field (name-value pair).
-
Arguments:
-
object
: An object, or an expression that evaluates to an object, containing exactly one field.
-
-
Return Value:
-
The value from the field.
-
If the
object
is MISSING, this function returns MISSING. -
For all other cases, or if the
object
contains more than one field, it returns NULL.
-
-
Example:
{ "v1": object_unwrap({"name": "value"}), "v2": object_unwrap(MISSING), "v3": object_unwrap({"name": "value", "name2": "value2"}), "v4": object_unwrap("some_string") };
-
The expected result is:
{ "v1": "value", "v3": null, "v4": null }
object_values
-
Syntax:
object_values(object)
-
Returns the values from all the fields—name-value pairs—in the object.
-
Arguments:
-
object
: An object, or an expression that evaluates to an object.
-
-
Return Value:
-
An array which contains the values from all the fields in the object.
-
If the
object
is MISSING, this function returns MISSING. -
If the
object
is NULL or not an object, it returns NULL.
-
-
Example:
object_values({"abc":1, "def":2, "ghi":3});
-
The expected result is:
[ 1, 2, 3 ]