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:
Oracle Database SQL Language Reference for information about json_value
Topics:
- Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
JSON has the Boolean valuestrue
andfalse
. Oracle SQL has no Boolean data type. When SQL/JSON functionjson_value
evaluates a SQL/JSON path expression and the result istrue
orfalse
, there are two ways to handle the result in SQL: as a string or as a number. - SQL/JSON Function JSON_VALUE Applied to a null JSON Value
SQL/JSON functionjson_value
applied to JSON valuenull
returns SQLNULL
, not the SQL string'null'
. This means, in particular, that you cannot usejson_value
to distinguish the JSON valuenull
from the absence of a value; SQLNULL
indicates both cases. - JSON_VALUE as JSON_TABLE
SQL/JSON functionjson_value
can be viewed as a special case of functionjson_table
.
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";