parse_json function

Applies to: Databricks SQL Databricks Runtime 15.3 and later

Returns a VARIANT value from the jsonStr.

Syntax

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 the JSON string is not valid, the result is an error. To return NULL instead of an error, use the try_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(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 parse_json('{"key": 123, "data": [4, 5, "str"]}');
  {"data":[4,5,"str"],"key":123}

-- Parsing a scalar value
> SELECT parse_json('123');
  123

-- invalid JSON string
> SELECT parse_json('{ bad }');
  [MALFORMED_RECORD_IN_PARSING.WITHOUT_SUGGESTION] Malformed records are detected in record parsing: { bad }.

Related functions

• try_parse_json function
• to_json function
• variant_get function
• from_json function