VARIANT type

Applies to: Databricks SQL Databricks Runtime 15.3 and above

Preview

This feature is in Public Preview.

Represents semi-structured data.

note

Iceberg v2 tables do not support VARIANT columns. Apache Iceberg v3 supports VARIANT columns. See Use Apache Iceberg v3 features.

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

SQL

> 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