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