Skip to main content

try_variant_get function

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

Extracts a value of type type from variantExpr, specified by path, or NULL if it is not possible to cast to the target type.

Syntax​

try_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 or it cannot be cast to type, NULL is returned. To raise an error when the cast fails use variant_get.

Examples​

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

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

-- Invalid cast
> SELECT try_variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.key', 'boolean')
  null

-- These are synonymous to:
> SELECT '{"key": 123, "data": [4, {"a": "hello"}, "str"]}':data[1].a ?::STRING;
 hello

> SELECT '{"key": 123, "data": [4, {"a": "hello"}, "str"]}':missing ?::STRING;
 null

> SELECT '{"key": 123, "data": [4, {"a": "hello"}, "str"]}':key ?::BOOLEAN;
 null
Last updated on