JSON_VALUE

Description

The JSON_VALUE scalar function extracts a single scalar value located by a SQL/JSON path from a JSON document and returns it cast to the RETURNING type (STRING by default). This is the SQL-standard way (SQL:2016) to pull an individual value out of JSON, and is commonly used to migrate queries from other systems such as Oracle, DB2, and MySQL. Unlike JSON_TABLE, which produces rows in a FROM clause, JSON_VALUE is an expression that can appear anywhere a scalar is allowed.

The function returns a scalar only. A path that matches an object or array is an error case (see ON ERROR), not a value. To extract an object or array as a JSON fragment, use JSON_QUERY; to produce rows from a JSON array, use JSON_TABLE (the built-in get_json_object function also extracts fragments).

Syntax

JSON_VALUE ( json_expr, path
             [ RETURNING data_type ]
             [ empty_behavior ON EMPTY ]
             [ error_behavior ON ERROR ] )

empty_behavior
    { NULL | ERROR | DEFAULT default_expr }

error_behavior
    { NULL | ERROR | DEFAULT default_expr }

Parameters

• json_expr

  An expression that evaluates to a STRING containing the JSON document. A NULL input yields NULL directly (it triggers neither the ON EMPTY nor the ON ERROR behavior).

• path

  A SQL/JSON path literal that locates the value, for example '$.a.b' or '$.items[0]'. The path must be wildcard-free, since JSON_VALUE returns a single value; a path containing [*] is rejected at analysis time.

• RETURNING data_type

  The type the extracted value is cast to. It must be a scalar (atomic) type: a string, numeric, boolean, or datetime type. Non-atomic types (STRUCT, ARRAY, MAP) and VARIANT / BINARY are not supported. If RETURNING is omitted, the result type is STRING.

• empty_behavior ON EMPTY

  What to produce when path matches nothing:

  • NULL (the default) returns SQL NULL.
  • ERROR raises an error.
  • DEFAULT default_expr returns default_expr, cast to the RETURNING type.

• error_behavior ON ERROR

  What to produce when the extraction fails: the input is not well-formed JSON, the path matches a non-scalar (object or array) value, or casting the matched scalar to the RETURNING type fails.

  • NULL (the default) returns SQL NULL.
  • ERROR raises an error.
  • DEFAULT default_expr returns default_expr, cast to the RETURNING type.

  The cast of the matched scalar to the RETURNING type always follows ANSI semantics (a failed conversion routes to ON ERROR), independently of the session’s spark.sql.ansi.enabled setting.

A path that matches an explicit JSON null is a present scalar value and returns SQL NULL (it is neither the ON EMPTY nor the ON ERROR case).

Examples

-- Extract a scalar as STRING (the default)
SELECT json_value('{"id":7,"name":"Ada"}', '$.name');
+-------------------------------------------+
|json_value({"id":7,"name":"Ada"}, $.name)  |
+-------------------------------------------+
|Ada                                        |
+-------------------------------------------+

-- Cast the extracted value with RETURNING
SELECT json_value('{"id":7}', '$.id' RETURNING INT) + 1;
+---------------------------------------------+
|(json_value({"id":7}, $.id) + 1)             |
+---------------------------------------------+
|8                                            |
+---------------------------------------------+

-- A missing path defaults to NULL; supply a fallback with DEFAULT ... ON EMPTY
-- (RETURNING, when present, comes before the ON EMPTY / ON ERROR clauses)
SELECT json_value('{"id":7}', '$.missing' RETURNING INT DEFAULT -1 ON EMPTY);
+---------------------------------------------------------------+
|json_value({"id":7}, $.missing RETURNING INT DEFAULT -1 ON EMPTY)|
+---------------------------------------------------------------+
|-1                                                             |
+---------------------------------------------------------------+

-- A non-scalar match or malformed input is an ON ERROR case
SELECT json_value('{"addr":{"city":"NYC"}}', '$.addr' DEFAULT 'n/a' ON ERROR);
+---------------------------------------------------------------+
|json_value({"addr":{"city":"NYC"}}, $.addr DEFAULT n/a ON ERROR)|
+---------------------------------------------------------------+
|n/a                                                            |
+---------------------------------------------------------------+

-- ERROR ON ERROR raises instead of returning a value
SELECT json_value('not json', '$.a' ERROR ON ERROR);
[JSON_VALUE_ON_ERROR.ERROR] ...

Related Statements

• SELECT
• JSON_TABLE
• Built-in Functions