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

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

    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

    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

    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

    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.

    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

    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

    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.

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