schema_of_variant function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 15.3 and later

Returns the schema of a VARIANT expression in DDL format.

Syntax 

schema_of_variant ( variantExpr )

Arguments

  • variantExpr: A VARIANT expression.

Returns

A STRING holding a schema definition of the variantExpr. The types in the schema are the derived formatted SQL types.

To derive the aggregated schema of a collection of VARIANT values, use the schema_of_variant_agg aggregate function.

Notes

When determining the schema for an ARRAY<elementType>, the elementType may be inferred as VARIANT if there are conflicting types found in the data.

Examples 

-- Simple example
> SELECT schema_of_variant(parse_json('{"key": 123, "data": [4, 5]}'))
  OBJECT<data: ARRAY<BIGINT>, key: BIGINT>

-- Conflicting element types in array
> SELECT schema_of_variant(parse_json('{"data": [{"a":"a"}, 5]}'))
  OBJECT<data: ARRAY<VARIANT>>

-- A typed literal
> SELECT schema_of_variant(123.4::VARIANT);
  DECIMAL(4,1)

-- Contrasting schema_of_variant() with typeof()
> SELECT typeof(123.4::VARIANT);
  VARIANT