`OBJECT` type

Article
08/19/2024

Applies to: check marked yes Databricks Runtime 15.3 and later

Represents values in a VARIANT with the structure described by a set of fields.

Refer to STRUCT for storing and processing structured types described by a sequence of fields.

Important

The OBJECT cannot be stored in a table column. It is only exposed when calling schema_of_variant or schema_of_variant_agg. To use an OBJECT type, you must cast it to a STRUCT or MAP.

Syntax

OBJECT < [fieldName [:] fieldType [, ...] ] >

fieldName: An identifier naming the field. The names must be unique.
fieldType: Any data type.

Limits

The type supports any number of fields greater or equal to 0.

Literals

Values of OBJECT cannot be created outside of a VARIANT. They are the result of parsing a JSON string into a VARIANT using the parse_json() function.

Notes

To extract an OBJECT you can:
- variant_get function using a JSON path expression to navigate into the OBJECT type.
- : (colon sign) operator to parse the OBJECT using a JSON path expression.
- try_variant_get function using a JSON path to navigate into a OBJECT type with error toleration.
- cast function or :: (colon colon sign) operator to cast the OBJECT to a STRUCT or MAP.
- try_cast function or ?:: (triple colon sign) operator to cast the OBJECT to a STRUCT or MAP.

Examples

> SELECT schema_of_variant(parse_json('{"key": 123, "data": 5.1 }'));
  OBJECT<data: DECIMAL(2,1), key: BIGINT>

-- Casting from a an OBJECT to a STRUCT is by name, because OBJECT fields are not ordered.
> SELECT CAST(parse_json('{"key": 123, "data": 5.1 }') AS STRUCT<data: DECIMAL(2,1), key: BIGINT>);
  {"data":5.1,"key":123}

> SELECT CAST(parse_json('{"key": 123, "data": 5.1 }') AS STRUCT<key: BIGINT, data: DECIMAL(2,1)>);
  {"key":123, "data":5.1}

> SELECT CAST(parse_json('{"key": 123, "data": 5.1 }') AS MAP<STRING, DECIMAL(20, 1)>);
  {"data":"5.1","key":"123.0"}

OBJECT type