try_parse_json
function
Applies to: Databricks SQL Databricks Runtime 15.3 and later
Returns a VARIANT
value from the jsonStr
if possible, or NULL
if not possible.
Syntax
try_parse_json ( jsonStr )
Arguments
jsonStr
: ASTRING
expression specifying a JSON document.
Returns
A VARIANT
value that represents the equivalent data as the jsonStr
JSON string.
If there is an error in parsing the JSON string, the result is NULL
.
To return an error instead of NULL
, use the parse_json function.
Notes
The to_json function converts a VARIANT
value to a STRING value
, so it is logically the inverse of parse_json
.
However, it is not an exact inverse, so to_json(try_parse_json(jsonStr)) = jsonStr
may not be true.
Whitespace is not perfectly preserved
{ "a" : 1, "b" : 2 }
is equivalent to{"a":1,"b":2}
Ordering of keys can be arbitrary
{"a" : 1, "b": 2}
is equivalent to{"b": 2, "a" : 1}
Trailing zeros in numbers
{"a" : 0.01000}
is equivalent to{"a" : 0.01}
Examples
-- Simple example
> SELECT try_parse_json('{"key": 123, "data": [4, 5, "str"]}');
{"data":[4,5,"str"],"key":123}
-- Parsing a scalar value
> SELECT try_parse_json('123');
123
-- invalid JSON string
> SELECT try_parse_json('{ bad }');
NULL