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. When SQL/JSON function
json_value evaluates a SQL/JSON path expression and the result is JSON
false, it can be returned to PL/SQL as a
BOOLEAN value, or it can be returned to SQL as the
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
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
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
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";