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({"abc": 1}, {"def": 2}, {"ghi": 3}, {"ghi": 4}, {"ghi": [5, 6, 7]});
    Results
    [
      {
        "$1": {
          "abc": 1,
          "def": 2,
          "ghi": [
            5,
            6,
            7
          ]
        }
      }
    ]

    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)

    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.

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

    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

    Single object
    Query
    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;
    Results
    [
      {
        "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
          }
        ]
      }
    ]
    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 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; 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_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"
          ]
        ]
      }
    ]