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

Description

This function adds new attributes and values to a given object.

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 or new_attr_value is MISSING, or if new_attr_key is NULL, it returns the object 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.

Query

SELECT schedule[0] AS original,
       OBJECT_ADD(schedule[0], "day_new", 1) AS output
FROM route
LIMIT 1;

Results

[
  {
    "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.

Arguments

expr: An expression representing an object.

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.

Examples

Query

SELECT OBJECT_CONCAT({"flight": "AF198"},
    {"utc": "4:44:44"},
    {"code": "green"},
    {"code": "yellow"},
    {"passengers": ["Corrine Hill", "Vallie Ryan"]}
);

Results

[
  {
    "$1": {
      "flight": "AF198",
      "utc": "4:44:44",
      "code": "yellow",
      "passengers": [
        "Corrine Hill",
        "Vallie Ryan"
      ]
    }
  }
]

OBJECT_CONCAT2(`expr`, `expr` …)

Description

This function concatenates multiple input objects into a single new object, and requires at least two input objects to work. Unlike OBJECT_CONCAT(), this function supports both plain objects and arrays of objects as arguments.

Arguments

expr: An expression representing an object or an array of objects. The first argument must be a plain object and cannot be an array.

Return Value

Examples

Query

SELECT OBJECT_CONCAT2(
    {"flight": "AF198"},
    [ {"utc": "4:44:44"},
      {"code": "green"},
      {"airline": "Air France"},
      {"airline": "Delta"}
    ]
);

Results

[
  {
    "$1": {
      "flight": "AF198",
      "utc": "4:44:44",
      "code": "green",
      "airline": "Delta"
    }
  }
]

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.

Top-Level Fields

This example returns the complete values of the specified attributes at the top level of the object.

Query

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;

Results

[
  {
    "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"
  }
]

Nested Fields

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.

Query

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;

Results

[
  {
    "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.

Arguments

expression: An expression representing an object.

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

Single object

Query

SELECT OBJECT_INNER_PAIRS({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
    AS inner_pairs;

Results

[
  {
    "inner_pairs": [
      {
        "name": "codename",
        "val": "green"
      },
      {
        "name": "flight",
        "val": "AI444"
      },
      {
        "name": "utc",
        "val": "4:44:44"
      }
    ]
  }
]

Iterating over objects in an array

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.

Query

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;

Results

[
  {
    "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.

Arguments

expression: An expression representing an object.

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

Single object

Query

SELECT OBJECT_INNER_VALUES({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
    AS inner_values;

Results

[
  {
    "inner_values": [
      "green",
      "AI444",
      "4:44:44"
    ]
  }
]

Iterating over objects in an array

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.

Query

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;

Results

[
  {
    "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.

Arguments

expression: An object or an expression that evaluates to an object.

Return Value

An integer.

If the input expression is not an object, the function returns null; if the input expression is missing, the function returns missing.

Examples

Query

SELECT OBJECT_LENGTH({"abc": 1, "def": 2, "ghi": {"uvw": 3, "xyz": 4}});

Results

[
    {
        "$1": 3
    }
]

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.

Arguments

expression: An expression representing an object.

Return Value

An array of the attribute names contained within the source object. The attribute names are sorted in SQL++ collation order.

Examples

Single object

Query

SELECT OBJECT_NAMES({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
    AS names;

Results

[
  {
    "names": [
      "codename",
      "flight",
      "utc"
    ]
  }
]

Iterating over objects in an array

Query

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;

Results

[
  {
    "names": [
      "alert",
      "codename",
      "flight",
      "utc"
    ]
  }
]

OBJECT_PAIRS(`expression` [, `options` ])

Alias: OBJECT_OUTER_PAIRS(expression [, options ])

Description

This function returns an array of objects, containing the names and values of each attribute in the input object. It also provides an option to return attribute types instead of values.

The function 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. However, if an object does not contain a shared attribute name, it returns a null entry, similar to an OUTER JOIN.

For an illustration, refer to the examples below.

Arguments

expression: An expression representing an object.
options: [Optional] A JSON object specifying options for the function.

Options

Name Description Schema

Name	Description	Schema
types required	Determines whether to return attribute types or values. If TRUE, the function returns the name and type of each attribute. If FALSE, the function returns the name and value of each attribute. Default: `FALSE`	Boolean

types
required

Determines whether to return attribute types or values.

If TRUE, the function returns the name and type of each attribute.

If FALSE, the function returns the name and value of each attribute.

Default: FALSE

Boolean

Return Value

An array of objects, each containing the following attributes:

Name Description Schema

Name	Description	Schema
name	The name of an attribute in the source object.	String
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.	Depends on the value returned. It can be a string, number, boolean, or others.
type	The type of an attribute in the source object. Returned only when the `types` parameter is set to `TRUE`.	String

name

The name of an attribute in the source object.

String

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.

Depends on the value returned. It can be a string, number, boolean, or others.

type

The type of an attribute in the source object. Returned only when the types parameter is set to TRUE.

String

Each returned object will have either val or type (depending on the specified options), and not both. Also, the objects in the array are sorted by attribute name, in SQL++ collation order.

Examples

A single input object returning names and values

Query

SELECT OBJECT_PAIRS({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
    AS outer_pairs;

Results

[
  {
    "outer_pairs": [
      {
        "name": "codename",
        "val": "green"
      },
      {
        "name": "flight",
        "val": "AI444"
      },
      {
        "name": "utc",
        "val": "4:44:44"
      }
    ]
  }
]

A single input object returning names and types

Query

SELECT OBJECT_PAIRS({"flight": "AI444", "utc": "4:44:44", "codename": "green"},{"types":TRUE})
    AS outer_pairs;

Results

[
  {
    "outer_pairs": [
      {
        "name": "codename",
        "type": "string"
      },
      {
        "name": "flight",
        "type": "string"
      },
      {
        "name": "utc",
        "type": "string"
      }
    ]
  }
]

Iterating over objects in an array

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.

Query

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;

Results

[
  {
    "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. It also provides an option to return field types instead of values. 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. However, if an object does not contain a shared field name, it returns a null entry, similar to an OUTER JOIN.

For an illustration, refer to the examples below.

Arguments

object: An expression representing an object.
options: [Optional] A JSON object specifying options for the function.

Options

Name Description Schema

Name	Description	Schema
composites optional	If TRUE, every level of every nested field is displayed. If FALSE, only the deepest possible nested fields are returned. Default: `FALSE`	Boolean
pattern optional	A regular expression used to filter the returned paths. The pattern is matched against the composite path names, not the individual field names.	String
types optional	Determines whether to return field types or values. If TRUE, the function returns the name and type of each field. If FALSE, the function returns the name and value of each field. Default: `FALSE`	Boolean

composites
optional

If TRUE, every level of every nested field is displayed.

If FALSE, only the deepest possible nested fields are returned.

Default: FALSE

Boolean

pattern
optional

A regular expression used to filter the returned paths. The pattern is matched against the composite path names, not the individual field names.

String

types
optional

Determines whether to return field types or values.

If TRUE, the function returns the name and type of each field.

If FALSE, the function returns the name and value of each field.

Default: FALSE

Boolean

Return Value

An array of objects, each containing the following attributes:

Name Description Schema

Name	Description	Schema
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 (``).	String
val	The value of a field in the source object; or an array, containing the collated values of similarly-named fields in the source objects.	Depends on the value returned. It can be a string, number, boolean, or others.
type	The type of a field in the source object. Returned only when the 'types' parameter is set to 'TRUE'.	String

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 (``).

String

val

The value of a field in the source object; or an array, containing the collated values of similarly-named fields in the source objects.

Depends on the value returned. It can be a string, number, boolean, or others.

type

The type of a field in the source object. Returned only when the 'types' parameter is set to 'TRUE'.

String

Each returned object will have either val or type (depending on the specified options), and not both. Also, the objects in the array are sorted by field name, in SQL++ collation order.

Examples

Single object

Query

WITH input AS ({
    "attribute": {"flight-name": "AI444", "flight-number": 737}
  })
SELECT OBJECT_PAIRS_NESTED(input) AS nested_pairs,
       OBJECT_PAIRS_NESTED(input, {"composites": true}) AS nested_pairs_comp,
       OBJECT_PAIRS_NESTED(input, {"pattern": "name"}) AS nested_pairs_pattern,
       OBJECT_PAIRS_NESTED(input, {"types": true}) AS nested_pairs_types;

Results

[
  {
    "nested_pairs": [
      {
        "name": "attribute.flight-name",
        "val": "AI444"
      },
      {
        "name": "attribute.flight-number",
        "val": 737
      }
    ],
    "nested_pairs_comp": [
      {
        "name": "attribute",
        "val": {
          "flight-name": "AI444",
          "flight-number": 737
        }
      },
      {
        "name": "attribute.flight-name",
        "val": "AI444"
      },
      {
        "name": "attribute.flight-number",
        "val": 737
      }
    ],
    "nested_pairs_pattern": [
      {
        "name": "attribute.flight-name",
        "val": "AI444"
      }
    ],
    "nested_pairs_types": [
      {
        "name": "attribute.flight-name",
        "type": "string"
      },
      {
        "name": "attribute.flight-number",
        "type": "number"
      }
    ]
  }
]

Iterating over objects in an array

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.

Query

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;

Results

[
  {
    "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 3.

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; if false, only the deepest possible nested fields are returned. Default true.

arraysubscript

A boolean. If true, array subscripts are returned; if false, array subscripts are replaced by *. Default true.

unique

A boolean. If true, duplicate field names are collapsed to single unique field name; if false, all duplicate field names are returned. Typically used when arrays are expanded and array subscripts are not returned. Default true.

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.

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

Composite paths

Query

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;

Results

[
  {
    "composite": [
      "attribute",
      "attribute.first-part",
      "attribute.second-part"
    ],
    "non_composite": [
      "attribute.first-part",
      "attribute.second-part"
    ]
  }
]

Array subscripts and unique field names

Query

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;

Results

[
  {
    "no_subscripts_not_unique": [
      "attribute",
      "attribute[*].name",
      "attribute[*].name"
    ],
    "no_subscripts_unique": [
      "attribute",
      "attribute[*].name"
    ],
    "subscripts": [
      "attribute",
      "attribute[0].name",
      "attribute[1].name"
    ]
  }
]

Pattern matching and pattern space

This example searches for strings beginning with "n" in the given object paths.

Query

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;

Results

[
  {
    "all_paths": [
      "attribute",
      "attribute.name"
    ],
    "field_starts_with_n": [
      "attribute.name"
    ],
    "path_starts_with_n": []
  }
]

Complex example

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Query

SELECT OBJECT_PATHS(hotel, {"composites": false, "arraysubscript": false}) AS paths
FROM hotel
LIMIT 1;

Results

[
  {
    "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`)

Description

This function adds new or updates existing attributes and values to a given object.

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 by attr_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.

Query

SELECT schedule[0] AS original,
       OBJECT_PUT(schedule[0], "day", 1) AS output
FROM route
LIMIT 1;

Results

[
  {
    "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`)

Description

Renames the attribute old_field to new_field 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_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 object input_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.

Changing a field name

Query

SELECT t AS original,
       OBJECT_RENAME(t, "name", "new_name") AS output
FROM airline AS t
LIMIT 1;

Results

[
  {
    "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.

Query

SELECT schedule[0] AS original,
       OBJECT_REMOVE(schedule[0], "day") AS output
FROM route
LIMIT 1;

Results

[
  {
    "original": {
      "day": 0,
      "flight": "AF198",
      "utc": "10:13:00"
    },
    "output": {
      "flight": "AF198",
      "utc": "10:13:00"
    }
  }
]

Query

SELECT OBJECT_REMOVE({"abc": 1, "def": 2, "ghi": 3}, "def");

Results

[
  {
    "$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 object input_obj.

Return Value

The JSON object input_obj with replaced values.

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.

Replace any occurrences of "airline" with "airplane"

Query

SELECT t AS original,
       OBJECT_REPLACE(t, "airline", "airplane") AS output
FROM airline AS t
LIMIT 1;

Results

[
  {
    "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_TYPES(`expression`)

Description

This function returns the data type of every field in the supplied object, as reported by the TYPE() function.

Arguments

expression: An expression representing an object.

Return Value

An object with the same fields as the input object, but with all values replaced by their corresponding data types.

Example

Query

SELECT OBJECT_TYPES({"flight": "AI444",
                     "duration": 180,
                     "gate": NULL,
                     "aircraft": {"model": "Boeing 737"}})
    AS object_types;

Results

[
    {
        "object_types":{
            "flight":"string",
            "duration":"number",
            "gate":"null",
            "aircraft":"object"
        }
    }
]

OBJECT_TYPES_NESTED(`expression`)

Description

This function returns the data type of every non-composite field in the supplied object, as reported by the TYPE() function. Additionally, all composite type fields (ARRAYs and OBJECTs) are recursively processed in the same way, returning the data types of all nested values.

Arguments

expression: An expression representing an object.

Return Value

An object with the same fields as the input object, but with all values replaced by their corresponding non-composite data types.

Example

Query

SELECT OBJECT_TYPES_NESTED({"flight":"AI444",
                            "crewMembers": ["Alice Bobson",
                                            "John Flo",
                                            true],
                            "gate": NULL,
                            "aircraft": {"model": "Boeing 737",
                                         "capacity":200}})
    AS object_types_nested;

Results

[
  {
    "object_types_nested": {
      "flight": "string",
      "crewMembers": [
        "string",
        "string",
        "boolean"
      ],
      "gate": "null",
      "aircraft": {
        "model": "string",
        "capacity":"number"
      }
    }
  }
]

OBJECT_UNWRAP(`expression`)

Description

This function enables you to unwrap an object without knowing the name of the attribute.

Arguments

expression: An expression representing an object.

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

Query

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`;

Results

[
  {
    "missing": null,
    "multiple": null,
    "single": "value",
    "string": null
  }
]

OBJECT_VALUES(`expression`)

Alias: OBJECT_OUTER_VALUES(expression)

Description

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.

Arguments

expression: An expression representing an object.

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

Single object

Query

SELECT OBJECT_VALUES({"flight": "AI444", "utc": "4:44:44", "codename": "green"})
    AS outer_values;

Results

[
  {
    "outer_values": [
      "green",
      "AI444",
      "4:44:44"
    ]
  }
]

Iterating over objects in an array

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.

Query

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;

Results

[
  {
    "outer_values": [
      [
        null,
        "red",
        null
      ],
      [
        "green",
        null,
        "yellow"
      ],
      [
        "AI444",
        "AI333",
        "AI222"
      ],
      [
        "4:44:44",
        "3:33:33",
        "2:22:22"
      ]
    ]
  }
]

Object Functions

OBJECT_ADD(object, new_attr_key, new_attr_value)

Description

Arguments

Return Value

Examples

OBJECT_CONCAT(expr, expr …​)

Description

Arguments

Return Value

Examples

OBJECT_CONCAT2(expr, expr …​)

Description

Arguments

Return Value

Examples

OBJECT_FIELD(object, field)

Description

Arguments

Return Value

Examples

OBJECT_INNER_PAIRS(expression)

Description

Arguments

Return Value

Examples

OBJECT_INNER_VALUES(expression)

Description

Arguments

Return Value

Examples

OBJECT_LENGTH(expression)

Description

Arguments

Return Value

Examples

OBJECT_NAMES(expression)

Description

Arguments

Return Value

Examples

OBJECT_PAIRS(expression [, options ])

Description

Arguments

Options

Return Value

Examples

OBJECT_PAIRS_NESTED(object [, options])

Description

Arguments

Options

Return Value

Examples

OBJECT_PATHS(object [, options] )

Description

Arguments

Return Value

Examples

OBJECT_PUT(object, attr_key, attr_value)

Description

Arguments

Return Value

Examples

OBJECT_RENAME(input_obj, old_field, new_field)

Description

Arguments

Return Value

Examples

OBJECT_REMOVE(object, attr_key)

Description

Attributes

Return Value

Examples

OBJECT_REPLACE(input_obj, old_value, new_value)

Description

Arguments

Return Value

Examples

OBJECT_TYPES(expression)

Description

OBJECT_ADD(`object`, `new_attr_key`, `new_attr_value`)

OBJECT_CONCAT(`expr`, `expr` …)

OBJECT_CONCAT2(`expr`, `expr` …)

OBJECT_FIELD(`object`, `field`)

OBJECT_INNER_PAIRS(`expression`)

OBJECT_INNER_VALUES(`expression`)

OBJECT_LENGTH(`expression`)

OBJECT_NAMES(`expression`)

OBJECT_PAIRS(`expression` [, `options` ])

OBJECT_PAIRS_NESTED(`object` [, `options`])

OBJECT_PATHS(`object` [, `options`] )

OBJECT_PUT(`object`, `attr_key`, `attr_value`)

OBJECT_RENAME(`input_obj`, `old_field`, `new_field`)

OBJECT_REMOVE(`object`, `attr_key`)

OBJECT_REPLACE(`input_obj`, `old_value`, `new_value`)

OBJECT_TYPES(`expression`)

OBJECT_TYPES_NESTED(`expression`)

OBJECT_UNWRAP(`expression`)

OBJECT_VALUES(`expression`)