parse_json function

Applies to: Databricks SQL Databricks Runtime 15.3 and above

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, Azure Databricks raises MALFORMED_RECORD_IN_PARSING. To return NULL instead of an error, use the try_parse_json function.

Common error conditions

• MALFORMED_RECORD_IN_PARSING

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 }');
  Error: MALFORMED_RECORD_IN_PARSING

Related functions

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