Object Functions
- reference
You can use object functions to evaluate objects, perform computations on attributes in an object, and to return a new object based on a transformation.
OBJECT_ADD(object
, new_attr_key
, new_attr_value
)
Arguments
- object
-
An expression representing the object that you want to add to.
- new_attr_key
-
The name of the attribute to add.
- new_attr_value
-
The value of the attribute to add.
Return Value
The updated object.
-
This function does not perform key substitution.
-
If you add a duplicate attribute (that is, if the key is found), it returns an error or NULL object.
-
If
new_attr_key
ornew_attr_value
is MISSING, or ifnew_attr_key
is NULL, it returns theobject
unmodified. -
If
object
is not an object or NULL, it returns a NULL value object.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT schedule[0] AS original,
OBJECT_ADD(schedule[0], "day_new", 1) AS output
FROM route
LIMIT 1;
[
{
"original": {
"day": 0,
"flight": "AF198",
"utc": "10:13:00"
},
"output": {
"day": 0,
"day_new": 1,
"flight": "AF198",
"utc": "10:13:00"
}
}
]
OBJECT_CONCAT(expr
, expr
…)
Description
This function concatenates the input objects and returns a new object. It requires a minimum of two input objects.
Return Value
An object constructed by concatenating all the input objects. 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.
OBJECT_FIELD(object
, field
)
Description
This function returns the value of the specified field within the given object. A field in this context may be any attribute or element, nested at any level within the object.
Arguments
- expr
-
An expression representing an object.
- field
-
A string, or any valid expression which evaluates to a string, representing the path to a field within the object.
You can use nested operators to specify the path to a nested attribute or element. If any attribute names within the field path contain special characters, they must be escaped using backticks (
``
).
Return Value
The value of the specified field. If the object does not exist, or the field cannot be found within the object at the specified location, the function returns NULL.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
This example returns the complete values of the specified attributes at the top level of the object.
SELECT OBJECT_FIELD(hotel, "public_likes") AS `array`,
OBJECT_FIELD(hotel, "vacancy") AS `boolean`,
OBJECT_FIELD(hotel, "id") AS `number`,
OBJECT_FIELD(hotel, "geo") AS `object`,
OBJECT_FIELD(hotel, "name") AS `string`
FROM hotel
LIMIT 1;
[
{
"array": [
"Julius Tromp I",
"Corrine Hilll",
"Jaeden McKenzie",
"Vallie Ryan",
"Brian Kilback",
"Lilian McLaughlin",
"Ms. Moses Feeney",
"Elnora Trantow"
],
"boolean": true,
"number": 10025,
"object": {
"accuracy": "RANGE_INTERPOLATED",
"lat": 51.35785,
"lon": 0.55818
},
"string": "Medway Youth Hostel"
}
]
This example specifies a nested array element and a nested object attribute at different depths in the hierarchy. In the path to the nested object attribute, the final attribute name is escaped, as it contains special characters.
SELECT
OBJECT_FIELD(hotel, "reviews[1]")
AS array_element,
OBJECT_FIELD(hotel, "reviews[1].ratings.`Business service (e.g., internet access)`")
AS object_attribute
FROM hotel
LIMIT 1;
[
{
"array_element": {
"author": "Barton Marks",
"content": "We found the hotel de la Monnaie through Interval ...",
"date": "2015-03-02 19:56:13 +0300",
"ratings": {
"Business service (e.g., internet access)": 4,
"Check in / front desk": 4,
"Cleanliness": 4,
"Location": 4,
"Overall": 4,
"Rooms": 3,
"Service": 3,
"Value": 5
}
},
"object_attribute": 4
}
]
OBJECT_INNER_PAIRS(expression
)
Description
This function returns an array of objects, containing the names and values of each attribute in the input object. It is particularly useful when iterating over multiple objects in an array, as it collates the values from similarly-named attributes into a single nested array.
In this case, the function does not return a value from any object which does not contain the shared attribute name, rather like an INNER JOIN. For an illustration, refer to the examples below.
Return Value
An array of objects, each containing two attributes:
- name
-
The name of an attribute in the source object.
- val
-
The value of an attribute in the source object; or an array, containing the collated values of similarly-named attributes in the source objects.
The objects in the array are sorted by attribute name, in SQL++ collation order.
Examples
SELECT OBJECT_INNER_PAIRS({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
AS inner_pairs;
[
{
"inner_pairs": [
{
"name": "codename",
"val": "green"
},
{
"name": "flight",
"val": "AI444"
},
{
"name": "utc",
"val": "4:44:44"
}
]
}
]
In this example, notice that where the source objects have similarly-named attributes, the values from each of those attributes are collated into a single array in the output.
WITH special_flights AS ([{"flight": "AI444", "utc": "4:44:44", "codename": "green"},
{"flight": "AI333", "utc": "3:33:33", "alert": "red"},
{"flight": "AI222", "utc": "2:22:22", "codename": "yellow"}])
SELECT OBJECT_INNER_PAIRS(special_flights[*]) AS inner_pairs;
[
{
"inner_pairs": [
{
"name": "alert",
"val": "red"
},
{
"name": "codename",
"val": [
"green",
"yellow"
]
},
{
"name": "flight",
"val": [
"AI444",
"AI333",
"AI222"
]
},
{
"name": "utc",
"val": [
"4:44:44",
"3:33:33",
"2:22:22"
]
}
]
}
]
OBJECT_INNER_VALUES(expression
)
Description
This function returns an array, containing the values of each attribute in the input object. It is particularly useful when iterating over multiple objects in an array, as it collates the values from similarly-named attributes into a single nested array.
In this case, the function does not return a value from any object which does not contain the shared attribute name, rather like an INNER JOIN. For an illustration, refer to the examples below.
Return Value
An array of the values contained within the source object. The values in the array are sorted by the corresponding attribute names in the source object, in SQL++ collation order.
Examples
SELECT OBJECT_INNER_VALUES({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
AS inner_values;
[
{
"inner_values": [
"green",
"AI444",
"4:44:44"
]
}
]
In this example, notice that where the source objects have similarly-named attributes, the values from each of those attributes are collated into a single array in the output.
WITH special_flights AS ([{"flight": "AI444", "utc": "4:44:44", "codename": "green"},
{"flight": "AI333", "utc": "3:33:33", "alert": "red"},
{"flight": "AI222", "utc": "2:22:22", "codename": "yellow"}])
SELECT OBJECT_INNER_VALUES(special_flights[*]) AS inner_values;
[
{
"inner_values": [
"red",
[
"green",
"yellow"
],
[
"AI444",
"AI333",
"AI222"
],
[
"4:44:44",
"3:33:33",
"2:22:22"
]
]
}
]
OBJECT_LENGTH(expression
)
Equivalent: LEN()
Description
This function returns the number of name-value pairs in the object. It only counts the top-level attributes and does not recurse into nested objects.
OBJECT_NAMES(expression
)
Description
This function returns an array, containing the names of each attribute in the input object. It is particularly useful when iterating over multiple objects in an array, as it collates similar attribute names.
Return Value
An array of the attribute names contained within the source object. The attribute names are sorted in SQL++ collation order.
Examples
SELECT OBJECT_NAMES({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
AS names;
[
{
"names": [
"codename",
"flight",
"utc"
]
}
]
WITH special_flights AS ([{"flight": "AI444", "utc": "4:44:44", "codename": "green"},
{"flight": "AI333", "utc": "3:33:33", "alert": "red"},
{"flight": "AI222", "utc": "2:22:22", "codename": "yellow"}])
SELECT OBJECT_NAMES(special_flights[*]) AS names;
[
{
"names": [
"alert",
"codename",
"flight",
"utc"
]
}
]
OBJECT_PAIRS(expression
)
Alias: OBJECT_OUTER_PAIRS(expression
)
Description
This function returns an array of objects, containing the names and values of each attribute in the input object. It is particularly useful when iterating over multiple objects in an array, as it collates the values from similarly-named attributes into a single nested array.
In this case, the function returns a null entry from any object which does not contain the shared attribute name, rather like an OUTER JOIN. For an illustration, refer to the examples below.
Return Value
An array of objects, each containing two attributes:
- name
-
The name of an attribute in the source object.
- val
-
The value of an attribute in the source object; or an array, containing the collated values of similarly-named attributes in the source objects.
The objects in the array are sorted by attribute name, in SQL++ collation order.
Examples
SELECT OBJECT_PAIRS({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
AS outer_pairs;
[
{
"outer_pairs": [
{
"name": "codename",
"val": "green"
},
{
"name": "flight",
"val": "AI444"
},
{
"name": "utc",
"val": "4:44:44"
}
]
}
]
In this example, notice that where the source objects have similarly-named attributes, the values from each of those attributes are collated into a single array in the output.
WITH special_flights AS ([{"flight": "AI444", "utc": "4:44:44", "codename": "green"},
{"flight": "AI333", "utc": "3:33:33", "alert": "red"},
{"flight": "AI222", "utc": "2:22:22", "codename": "yellow"}])
SELECT OBJECT_PAIRS(special_flights[*]) AS outer_pairs;
[
{
"outer_pairs": [
{
"name": "alert",
"val": [
null,
"red",
null
]
},
{
"name": "codename",
"val": [
"green",
null,
"yellow"
]
},
{
"name": "flight",
"val": [
"AI444",
"AI333",
"AI222"
]
},
{
"name": "utc",
"val": [
"4:44:44",
"3:33:33",
"2:22:22"
]
}
]
}
]
OBJECT_PAIRS_NESTED(object
[, options
])
Description
Similar to OBJECT_PAIRS(), this function returns an array of objects, containing the names and values of each field in the input object. A field in this context may be any attribute or element, nested at any level within the object.
This function may be useful when iterating over multiple objects in an array, as it collates and unnests the values from similarly-named fields across all objects in the input array. In this case, the function returns a null entry from any object which does not contain the shared field name, rather like an OUTER JOIN. For an illustration, refer to the examples below.
Arguments
- object
-
An expression representing an object.
- options
-
[Optional] An object containing the following possible parameters:
- composites
-
A boolean. If
true
, every level of every nested field is displayed; iffalse
, only the deepest possible nested fields are returned. Defaultfalse
. - pattern
-
A regular expression used to filter the returned paths. The pattern is matched against the composite path names, not the individual field names.
Return Value
An array of objects, each containing two attributes:
- name
-
The full path to every possible field within the source object, subject to the specified options.
The result uses nested operators to specify the path to all nested attributes or elements. If any attribute names within a field path contain special characters, they are escaped using backticks (
``
). - val
-
The value of an attribute in the source object; or an array, containing the collated values of similarly-named attributes in the source objects.
The objects in the array are sorted by attribute name, in SQL++ collation order.
Examples
WITH input AS ({
"attribute": {"first-part": 1, "second-part": 2}
})
SELECT OBJECT_PAIRS_NESTED(input) AS nested_pairs,
OBJECT_PAIRS_NESTED(input, {"composites": true}) AS nested_pairs_comp,
OBJECT_PAIRS_NESTED(input, {"pattern": "first"}) AS nested_pairs_pattern;
[
{
"nested_pairs": [
{
"name": "attribute.first-part",
"val": 1
},
{
"name": "attribute.second-part",
"val": 2
}
],
"nested_pairs_comp": [
{
"name": "attribute",
"val": {
"first-part": 1,
"second-part": 2
}
},
{
"name": "attribute.first-part",
"val": 1
},
{
"name": "attribute.second-part",
"val": 2
}
],
"nested_pairs_pattern": [
{
"name": "attribute.first-part",
"val": 1
}
]
}
]
In this example, notice that where the source objects have similarly-named attributes, the values from each of those attributes are collated into a single array in the output. Each collated array is then unnested to show the name and value of its elements.
WITH special_flights AS ([{"flight": "AI444", "utc": "4:44:44", "codename": "green"},
{"flight": "AI333", "utc": "3:33:33", "alert": "red"},
{"flight": "AI222", "utc": "2:22:22", "codename": "yellow"}])
SELECT OBJECT_PAIRS_NESTED(special_flights[*], {"composites": true}) AS nested_pairs;
[
{
"nested_pairs": [
{
"name": "alert",
"val": [
null,
"red",
null
]
},
{
"name": "alert[0]",
"val": null
},
{
"name": "alert[1]",
"val": "red"
},
{
"name": "alert[2]",
"val": null
},
{
"name": "codename",
"val": [
"green",
null,
"yellow"
]
},
{
"name": "codename[0]",
"val": "green"
},
{
"name": "codename[1]",
"val": null
},
{
"name": "codename[2]",
"val": "yellow"
},
{
"name": "flight",
"val": [
"AI444",
"AI333",
"AI222"
]
},
{
"name": "flight[0]",
"val": "AI444"
},
{
"name": "flight[1]",
"val": "AI333"
},
{
"name": "flight[2]",
"val": "AI222"
},
{
"name": "utc",
"val": [
"4:44:44",
"3:33:33",
"2:22:22"
]
},
{
"name": "utc[0]",
"val": "4:44:44"
},
{
"name": "utc[1]",
"val": "3:33:33"
},
{
"name": "utc[2]",
"val": "2:22:22"
}
]
}
]
Compare this example with OBJECT_PAIRS() Example 2.
OBJECT_PATHS(object
[, options
] )
Description
This function returns the paths to all the fields within an object. A field in this context may be any attribute or element, nested at any level within the object.
Arguments
- object
-
An expression representing an object.
- options
-
[Optional] An object containing the following possible parameters:
- composites
-
A boolean. If
true
, every level of every nested field is displayed; iffalse
, only the deepest possible nested fields are returned. Defaulttrue
. - arraysubscript
-
A boolean. If
true
, array subscripts are returned; iffalse
, array subscripts are replaced by*
. Defaulttrue
. - unique
-
A boolean. If
true
, duplicate field names are collapsed to single unique field name; iffalse
, all duplicate field names are returned. Typically used when arrays are expanded and array subscripts are not returned. Defaulttrue
. - pattern
-
A regular expression used to filter the returned paths. Used in conjunction with the following setting.
- patternspace
-
A string literal with two possible values. Default
"path"
."field"
The pattern is matched against individual field names.
"path"
The pattern is matched against composite path names.
Return Value
An array containing the full path to every possible field within the source object, subject to the specified options.
The result uses nested operators to specify the path to all nested attributes or elements.
If any attribute names within a field path contain special characters, they are escaped using backticks (``
).
-
If
object
is MISSING, the function returns a MISSING value. -
If
object
is not an object, the function returns a NULL value. -
If
options
is not an object, the function returns a NULL value.
Examples
WITH input AS ({
"attribute": {"first-part": 1, "second-part": 2}
})
SELECT OBJECT_PATHS(input, {"composites": true}) AS composite,
OBJECT_PATHS(input, {"composites": false}) AS non_composite;
[
{
"composite": [
"attribute",
"attribute.first-part",
"attribute.second-part"
],
"non_composite": [
"attribute.first-part",
"attribute.second-part"
]
}
]
WITH input AS ({
"attribute": [ { "name": "elem1"}, {"name": "elem2"}]
})
SELECT
OBJECT_PATHS(input, {"arraysubscript": true})
AS subscripts,
OBJECT_PATHS(input, {"arraysubscript": false, "unique": false})
AS no_subscripts_not_unique,
OBJECT_PATHS(input, {"arraysubscript": false, "unique": true})
AS no_subscripts_unique;
[
{
"no_subscripts_not_unique": [
"attribute",
"attribute[*].name",
"attribute[*].name"
],
"no_subscripts_unique": [
"attribute",
"attribute[*].name"
],
"subscripts": [
"attribute",
"attribute[0].name",
"attribute[1].name"
]
}
]
This example searches for strings beginning with "n" in the given object paths.
WITH input AS ({
"attribute": {"name": "elem1"}
})
SELECT
OBJECT_PATHS(input)
AS all_paths,
OBJECT_PATHS(input, {"pattern": "^n", "patternspace": "field"})
AS field_starts_with_n,
OBJECT_PATHS(input, {"pattern": "^n", "patternspace": "path"})
AS path_starts_with_n;
[
{
"all_paths": [
"attribute",
"attribute.name"
],
"field_starts_with_n": [
"attribute.name"
],
"path_starts_with_n": []
}
]
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT OBJECT_PATHS(hotel, {"composites": false, "arraysubscript": false}) AS paths
FROM hotel
LIMIT 1;
[
{
"paths": [
"address",
"alias",
"checkin",
"checkout",
"city",
"country",
"description",
"directions",
"email",
"fax",
"free_breakfast",
"free_internet",
"free_parking",
"geo.accuracy",
"geo.lat",
"geo.lon",
"id",
"name",
"pets_ok",
"phone",
"price",
"public_likes",
"reviews[*].author",
"reviews[*].content",
"reviews[*].date",
"reviews[*].ratings[*].Cleanliness",
"reviews[*].ratings[*].Location",
"reviews[*].ratings[*].Overall",
"reviews[*].ratings[*].Rooms",
"reviews[*].ratings[*].Service",
"reviews[*].ratings[*].Value",
"reviews[*].ratings[*].`Business service (e.g., internet access)`",
"reviews[*].ratings[*].`Check in / front desk`",
"state",
"title",
"tollfree",
"type",
"url",
"vacancy"
]
}
]
OBJECT_PUT(object
, attr_key
, attr_value
)
Arguments
- object
-
An expression representing an object.
- attr_key
-
The name of the attribute to insert or update.
- attr_value
-
The value of the attribute.
Return Value
The updated object.
-
If
attr_key
is found in the object, it replaces the corresponding attribute value byattr_value
. -
If
attr_value
is MISSING, it deletes the corresponding existing key (if any), like OBJECT_REMOVE(). -
If
attr_key
is MISSING, it returns a MISSING value. -
If
attr_key
is not an object, it returns a NULL value.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT schedule[0] AS original,
OBJECT_PUT(schedule[0], "day", 1) AS output
FROM route
LIMIT 1;
[
{
"original": {
"day": 0,
"flight": "AF198",
"utc": "10:13:00"
},
"output": {
"day": 1,
"flight": "AF198",
"utc": "10:13:00"
}
}
]
OBJECT_RENAME(input_obj
, old_field
, new_field
)
Arguments
- input_obj
-
Any JSON object, or SQL++ expression that can evaluate to a JSON object, representing the search object.
- old_field
-
A string, or any valid expression which evaluates to a string, representing the old (original) attribute name inside the JSON object
input_obj
. - new_field
-
A string, or any valid expression which evaluates to a string, representing the new attribute name to replace
old_field
inside the JSON objectinput_obj
.
Return Value
The input object with the new attribute name. Note that if the new attribute name already exists in the input object, the original attribute with that name is replaced.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT t AS original,
OBJECT_RENAME(t, "name", "new_name") AS output
FROM airline AS t
LIMIT 1;
[
{
"original": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"name": "40-Mile Air",
"type": "airline"
},
"output": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"new_name": "40-Mile Air",
"type": "airline"
}
}
]
OBJECT_REMOVE(object
, attr_key
)
Description
This function removes the specified attribute and corresponding values from the given object.
Attributes
- object
-
An expression representing an object.
- attr_key
-
The name of the attribute to remove.
Return Value
The input object without the removed attribute.
-
If the
attr_key
is MISSING, it returns a MISSING value. -
If the
attr_key
is not an object, it returns a NULL value.
Examples
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT schedule[0] AS original,
OBJECT_REMOVE(schedule[0], "day") AS output
FROM route
LIMIT 1;
[
{
"original": {
"day": 0,
"flight": "AF198",
"utc": "10:13:00"
},
"output": {
"flight": "AF198",
"utc": "10:13:00"
}
}
]
SELECT OBJECT_REMOVE({"abc": 1, "def": 2, "ghi": 3}, "def");
[
{
"$1": {
"abc": 1,
"ghi": 3
}
}
]
OBJECT_REPLACE(input_obj
, old_value
, new_value
)
Description
Replaces all occurrences of the value value_old
to value_new
in the JSON input object input_obj
.
Arguments
- input_obj
-
Any JSON object, or SQL++ expression that can evaluate to a JSON object, representing the search object.
- old_value
-
A string, or any valid expression which evaluates to a string, representing the old (original) value name inside the JSON object
input_obj
. - new_value
-
A string, or any valid expression which evaluates to a string, representing the new value name to replace
old_value
inside the JSON objectinput_obj
.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT t AS original,
OBJECT_REPLACE(t, "airline", "airplane") AS output
FROM airline AS t
LIMIT 1;
[
{
"original": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"name": "40-Mile Air",
"type": "airline"
},
"output": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"name": "40-Mile Air",
"type": "airplane"
}
}
]
OBJECT_UNWRAP(expression
)
Description
This function enables you to unwrap an object without knowing the name of the attribute.
Return Value
If the argument is an object with exactly one attribute, this function returns the value in the attribute. If the argument is MISSING, it returns MISSING. For all other cases, it returns NULL.
Examples
SELECT OBJECT_UNWRAP({"name": "value"}) AS single,
OBJECT_UNWRAP({"name": MISSING}) AS `missing`,
OBJECT_UNWRAP({"name": "value", "name2": "value2"}) AS multiple,
OBJECT_UNWRAP("some-string") AS `string`;
[
{
"missing": null,
"multiple": null,
"single": "value",
"string": null
}
]
OBJECT_VALUES(expression
)
Alias: OBJECT_OUTER_VALUES(expression
)
Description
This function returns an array, containing the values of each attribute in the input object. It is particularly useful when iterating over multiple objects in an array, as it collates the values from similarly-named attributes into a single nested array.
In this case, the function returns a null entry from any object which does not contain the shared attribute name, rather like an OUTER JOIN. For an illustration, refer to the examples below.
Return Value
An array of the values contained within the source object. The values in the array are sorted by the corresponding attribute names in the source object, in SQL++ collation order.
Examples
SELECT OBJECT_VALUES({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
AS outer_values;
[
{
"outer_values": [
"green",
"AI444",
"4:44:44"
]
}
]
In this example, notice that where the source objects have similarly-named attributes, the values from each of those attributes are collated into a single array in the output.
WITH special_flights AS ([{"flight": "AI444", "utc": "4:44:44", "codename": "green"},
{"flight": "AI333", "utc": "3:33:33", "alert": "red"},
{"flight": "AI222", "utc": "2:22:22", "codename": "yellow"}])
SELECT OBJECT_VALUES(special_flights[*]) AS outer_values;
[
{
"outer_values": [
[
null,
"red",
null
],
[
"green",
null,
"yellow"
],
[
"AI444",
"AI333",
"AI222"
],
[
"4:44:44",
"3:33:33",
"2:22:22"
]
]
}
]