`VARIANT` type

Article
08/19/2024

Applies to: check marked yes Databricks Runtime 15.3 and later

Represents semi-structured data.

Syntax

VARIANT

Limits

The type supports storing any semi-structured data, including STRUCT, ARRAY, MAP, and scalar types. VARIANT can only store MAP types with keys of type STRING.

Literals

See parse_json function function for details on creating a VARIANT value. You can also use the CAST function to convert a literal of some type to VARIANT.

Notes

To extract a value from a VARIANT you can use the
- variant_get function using a JSON path expression to navigate into a complex type.
- : (colon sign) operator to parse the VARIANT using a JSON path expression.
- try_variant_get function using a JSON path to navigate into a complex type with error toleration.
- cast function or :: (colon colon sign) operator to cast the VARIANT to a specific type.
- try_cast function to cast the VARIANT to a specific type with error toleration.
To inspect the type of a VARIANT value, use the
- schema_of_variant function for an individual value.
- schema_of_variant_agg aggregate function for a collection of values.

Examples

> SELECT parse_json('{"key": 123, "data": [4, 5, "str"]}');
  {"data":[4,5,"str"],"key":123}

> SELECT parse_json(null);
  null

> SELECT parse_json('123');
  123

> SELECT CAST(123.456 AS VARIANT);
  123.456

VARIANT type