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.
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
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
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.
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.
Oracle Database SQL Language Reference for information about
15.1 Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
JSON has the Boolean values
false. Oracle SQL has no Boolean data type. When SQL/JSON function
json_value evaluates a SQL/JSON path expression and the result is
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
'false'. You can alternatively return the result as a SQL number, in which case the JSON value
true is returned as the number
false is returned as
Example 15-1 illustrates this. The first query returns the string
'true'; the second query returns the number
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
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
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
json_value can be viewed as a special case of function
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_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_query (any combination) to fewer invocations of
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";