SQL/JSON Function JSON

15 SQL/JSON Function JSON_VALUE

SQL/JSON function json_value selects a scalar value from JSON data and returns it as a SQL value.

You can also use json_value to create function-based B-tree indexes for use with JSON data — see Indexes for JSON Data.

Function json_value has two required arguments and accepts optional returning and error clauses.

The first argument to json_value is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the SQL expression is used as the context item for evaluating the path expression.

The second argument to json_value is a SQL/JSON path expression followed by optional clauses RETURNING, ON ERROR, and ON EMPTY. The path expression must target a single scalar value, or else an error occurs.

The default error-handling behavior is NULL ON ERROR, which means that no value is returned if an error occurs — an error is not raised. In particular, if the path expression targets a non-scalar value, such as an array, no error is raised, by default. To ensure that an error is raised, use ERROR ON ERROR.

Note:

Each field name in a given JSON object is not necessarily unique; the same field name may be repeated. The streaming evaluation that Oracle Database employs always uses only one of the object members that have a given field name; any other members with the same field name are ignored. It is unspecified which of multiple such members is used.

15.1 Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value

JSON has the Boolean values true and false. Oracle SQL has no Boolean data type. When SQL/JSON function json_value evaluates a SQL/JSON path expression and the result is true or false, there are two ways to handle the result in SQL: as a string or as a number.

By default, the returned data type is a SQL string (VARCHAR2), meaning that the result is the string 'true' or 'false'. You can alternatively return the result as a SQL number, in which case the JSON value true is returned as the number 1, and false is returned as 0.

Example 15-1 illustrates this. The first query returns the string 'true'; the second query returns the number 1.

You can also use json_value in PL/SQL code. In that case, BOOLEAN is a valid PL/SQL return type for built-in PL/SQL function json_value. Example 15-2 illustrates this.

Example 15-1 JSON_VALUE: Two Ways to Return a JSON Boolean Value in SQL

SELECT json_value(po_document, '$.AllowPartialShipment')
 FROM j_purchaseorder;

SELECT json_value(po_document, '$.AllowPartialShipment' RETURNING NUMBER)
  FROM j_purchaseorder;

Example 15-2 Returning a BOOLEAN PL/SQL Value From JSON_VALUE

Unlike Oracle SQL, PL/SQL has a BOOLEAN data type, which you can use for the return value of PL/SQL built-in function json_value.

PL/SQL also has exception handling. This example uses clause ERROR ON ERROR, to raise an error (which can be handled by user code) in case of error.

DECLARE
  b BOOLEAN;
  jsonData CLOB;
BEGIN
  SELECT po_document INTO jsonData FROM j_purchaseorder WHERE rownum = 1;
     b := json_value(jsonData, '$.AllowPartialShipment'
                    RETURNING BOOLEAN
                    ERROR ON ERROR);
END;
/

15.2 SQL/JSON Function JSON_VALUE Applied to a null JSON Value

SQL/JSON function json_value applied to JSON value null returns SQL NULL, not the SQL string 'null'. This means, in particular, that you cannot use json_value to distinguish the JSON value null from the absence of a value; SQL NULL indicates both cases.

15.3 JSON_VALUE as JSON_TABLE

SQL/JSON function json_value can be viewed as a special case of function json_table.

Example 15-3 illustrates the equivalence: the two SELECT statements have the same effect.

In addition to perhaps helping you understand json_value better, this equivalence is important practically, because it means that you can use either function to get the same effect.

In particular, if you use json_value more than once, or you use it in combination with json_exists or json_query (which can also be expressed using json_table), to access the same data, then a single invocation of json_table presents the advantage that the data is parsed only once.

Because of this, the optimizer often automatically rewrites multiple invocations of json_exists, json_value and json_query (any combination) to fewer invocations of json_table.

Example 15-3 JSON_VALUE Expressed Using JSON_TABLE

SELECT json_value(column, json_path RETURNING data_type error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";

Related Topics

JSON_TABLE Generalizes SQL/JSON Query Functions and Conditions