OBJECT type
Applies to: 
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
OBJECTyou can:- variant_get function using a JSON path expression to navigate into the
OBJECTtype. - : (colon sign) operator to parse the
OBJECTusing a JSON path expression. - try_variant_get function using a JSON path to navigate into a
OBJECTtype with error toleration. - cast function or :: (colon colon sign) operator to cast the
OBJECTto a STRUCT or MAP. - try_cast function or ?:: (triple colon sign) operator to cast the
OBJECTto a STRUCT or MAP.
- variant_get function using a JSON path expression to navigate into the
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"}