variant_get function

Applies to: Databricks SQL Databricks Runtime 15.3 and above

Extracts a value of type from variantExpr, specified by path.

Syntax

variant_get ( variantExpr, path, type )

Arguments

• variantExpr: A VARIANT expression.
• path: A STRING literal with a well-formed JSON path expression.
• type: A STRING literal defining the type.

Returns

A value of type type.

If the object cannot be found, NULL is returned. If the object is found but cannot be cast to the desired type, Azure Databricks raises INVALID_VARIANT_CAST. To return NULL instead of an error, use the try_variant_get function.

Examples

-- Simple example
> SELECT variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.data[1].a', 'string')
  hello

-- missing path
> SELECT variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.missing', 'int')
  null

-- Invalid cast
> SELECT variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.key', 'array<int>')
  Error: INVALID_VARIANT_CAST.

Related functions

• try_variant_get function
• parse_json function
• JSON path expression