VARIANT type

Applies to: Databricks SQL Databricks Runtime 15.3 and above

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

Related

• :: (colon colon sign) operator
• cast function
• parse_json function
• schema_of_variant function
• schema_of_variant_agg aggregate function
• try_cast function
• try_variant_get function
• variant_get function