VARIANT type
Applies to: 
Databricks SQL Databricks Runtime 15.3 and above
Represents semi-structured data.
remarque
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 semi-structured data OBJECT, ARRAY, and scalar types.
To store STRUCT and MAP use the to_variant_object function.
MAP keys must be 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
VARIANTyou can use thevariant_getfunction using a JSON path expression to navigate into a complex type.:(colon sign) operator to parse theVARIANTusing a JSON path expression.try_variant_getfunction using a JSON path to navigate into a complex type with error toleration.castfunction or::(colon colon sign) operator to cast theVARIANTto a specific type.try_castfunction to cast theVARIANTto a specific type with error toleration.
- To inspect the type of a
VARIANTvalue, use theschema_of_variantfunction for an individual value.schema_of_variant_aggaggregate 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
> SELECT to_variant_object(map('key', 'val'));
{ "key": "val" }
> SELECT to_variant_object(struct('field', 'val'));
{ "field": "val" }