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.

See also Unique Versus Duplicate Fields in JSON Objects.

See Also:

Oracle Database SQL Language Reference for information about json_value

• Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
  JSON has the Boolean values true and false. When SQL/JSON function json_value evaluates a SQL/JSON path expression and the result is JSON true or false, it can be returned to PL/SQL as a BOOLEAN value, or it can be returned to SQL as the VARCHAR2 value 'true' or 'false'.
• 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.
• JSON_VALUE as JSON_TABLE
  SQL/JSON function json_value can be viewed as a special case of function json_table.

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

JSON has the Boolean values true and false. When SQL/JSON function json_value evaluates a SQL/JSON path expression and the result is JSON true or false, it can be returned to PL/SQL as a BOOLEAN value, or it can be returned to SQL as the VARCHAR2 value 'true' or 'false'.

In PL/SQL code, BOOLEAN is a valid PL/SQL return type for built-in PL/SQL function json_value. Example 15-1 illustrates this.

Oracle SQL has no Boolean data type, so a string (VARCHAR2) value is used to return a JSON Boolean value. Example 15-2 illustrates this — the query returns the string 'true'.

SQL/JSON function json_table generalizes other SQL/JSON query functions such as json_value. When you use it to project a JSON Boolean value, json_value is used implicitly, and the resulting SQL value is returned as a VARCHAR2 value. The data type of the projection column must therefore be VARCHAR2.

Example 15-1 JSON_VALUE: Returning a JSON Boolean Value to PL/SQL as BOOLEAN

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;
/ 

Example 15-2 JSON_VALUE: Returning a JSON Boolean Value to SQL as VARCHAR2

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

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";