parse_json function

  • Article

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