try_parse_json function

Applies to: check marked yes Databricks SQL check marked yes 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: A STRING 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