19 SQL/JSON Function JSON_VALUE
SQL/JSON function json_value
selects JSON data and
returns a SQL scalar or an instance of a user-defined SQL object type or SQL collection type
(varray, nested table).
-
If
json_value
targets a single scalar JSON value then it returns a corresponding scalar SQL value. You can specify the SQL data type for the returned scalar value. By default it isVARCHAR2(4000)
.If
json_value
targets the JSON scalar valuenull
it returns SQLNULL
of whatever the SQL return type is. (For example, for the default return typeVARCHAR2(4000)
it does not return 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. -
If
json_value
targets a JSON array, and you specify a SQL collection type (varray or nested table) as the return type, thenjson_value
returns an instance of that collection type.The elements of a targeted JSON array provide the elements of the returned collection-type instance. A scalar JSON array element produces a scalar SQL value in the returned collection instance (see previous). A JSON array element that is an object (see next) or an array is handled recursively.
-
If
json_value
targets a JSON object, and you specify a user-defined SQL object type as the return type, thenjson_value
returns an instance of that object type.The field values of a targeted JSON object provide the attribute values of the returned object-type instance. The field names of the targeted JSON object are compared with the SQL names of the SQL object attributes. A scalar field value produces a scalar SQL value in the returned object-type instance (see above). A field value that is an array (see previous) or an object is handled recursively,
Ultimately it is the names of JSON fields with scalar values that are compared with the names of scalar SQL object attributes. If the names do not match exactly, case-sensitively, then a mismatch error is handled at query compile time.
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 it
accepts some optional 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). A scalar value returned from json_value
can
be of any of these data types: BINARY_DOUBLE
,
BINARY_FLOAT
, BOOLEAN
, CHAR
,
CLOB
, DATE
, INTERVAL DAY TO
SECOND
, INTERVAL YEAR TO MONTH
,
NCHAR
, NCLOB
, NVARCHAR2
,
NUMBER
, RAW
Foot 1,
SDO_GEOMETRY
, TIMESTAMP
, TIMESTAMP
WITH TIME ZONE
, VARCHAR2
, and
VECTOR
.
If the RETURNING
type is VECTOR
then the behavior
depends on the targeted input data as follows:
-
If the targeted data is a JSON-scalar vector value then that value is returned as a
VECTOR
instance. -
If the targeted data is a JSON array with only number elements then the array is converted to a
VECTOR
instance, which is returned. -
If the targeted data is any other JSON value (that is, a non-vector scalar, an array with any non-number elements, or an object) then an error is raised.
Note:
In general, if you produce SQL character data of a type other than
NVARCHAR2
, NCLOB
, and NCHAR
from a JSON
string, and if the character set of that target data type is not Unicode-based, then the
conversion can undergo a lossy character-set conversion for characters that can't be
represented in the character set of that SQL type.
The first argument 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
,
PASSING
, ON ERROR
, ON EMPTY
,
and ON MISMATCH
. 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 nonscalar
value, such as an array, no error is raised, by default. To ensure that an error is
raised, use ERROR ON ERROR
.
In a path-expression array step, if only one position is specified
then it is matched against the data. Otherwise, there is no match (by default,
NULL
is returned).
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
- Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
JSON has Boolean valuestrue
andfalse
. When SQL/JSON functionjson_value
evaluates a path expression to JSONtrue
orfalse
, it can return aBOOLEAN
or aVARCHAR2
value ('true'
or'false'
), or aNUMBER
value (1
fortrue
,0
forfalse
). - Using JSON_VALUE To Instantiate a User-Defined Object-Type or Collection-Type Instance
You can use SQL/JSON functionjson_value
to instantiate an instance of a user-defined SQL object type or collection type. You do this by targeting a JSON object or array in the path expression and specifying the object or collection type, respectively, in theRETURNING
clause. - JSON_VALUE as JSON_TABLE
SQL/JSON functionjson_value
can be viewed as a special case of functionjson_table
.
Related Topics
Parent topic: Query JSON Data
19.1 Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
JSON has Boolean values true
and false
.
When SQL/JSON function json_value
evaluates a path expression to JSON
true
or false
, it can return a BOOLEAN
or a
VARCHAR2
value ('true'
or 'false'
), or a
NUMBER
value (1
for true
,
0
for false
).
By default, json_value
returns a VARCHAR2
(string) value. If the targeted data is a JSON Boolean value then by default the
returned value is the string
'true'
or 'false'
. Example 19-1 illustrates this — the query returns
'true'
.
With a RETURNING
clause you can specify the return data type.
Example 19-2 illustrates the use of RETURNING BOOLEAN
to return a
BOOLEAN
value (true
or false
) in SQL —
the query returns true
. Example 19-3 illustrates the same thing in PL/SQL, and it shows the use
of clause ERROR ON ERROR
.
By default, RETURNING NUMBER
raises an error when the targeted
data is a JSON Boolean value. However, if you include the clause ALLOW BOOLEAN TO
NUMBER CONVERSION
then no error is raised; in that case, 1
is returned for a true
JSON value, and 0
is returned for a
false
value. Example 19-4 illustrates this — the query returns 1
.
SQL/JSON function json_table
generalizes other SQL/JSON query
functions, including 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, by default. By default, the data type of the
projection column is therefore VARCHAR2
.
But just as for json_value
, you can project a JSON Boolean
value as a BOOLEAN
value. And you can project it as a
NUMBER
value, by specifying NUMBER
data type for the
column and including the clause ALLOW BOOLEAN TO NUMBER CONVERSION
.
Example 19-1 JSON_VALUE: Returning a JSON Boolean Value as VARCHAR2
Returning a VARCHAR2
is the default behavior for function
json_value
.
SELECT json_value(po_document, '$.AllowPartialShipment')
FROM j_purchaseorder;
Example 19-2 JSON_VALUE: Returning a JSON Boolean Value to SQL as BOOLEAN
This example returns a SQL BOOLEAN
value for Boolean JSON
data. (BOOLEAN
data type is available in Oracle SQL starting with Release
23ai.)
SELECT json_value(po_document, '$.AllowPartialShipment'
RETURNING BOOLEAN)
FROM j_purchaseorder;
Example 19-3 JSON_VALUE: Returning a JSON Boolean Value to PL/SQL as BOOLEAN
This example uses clause ERROR ON ERROR
, to raise an error in
case of error. (User exception-handling code can then handle the error.)
DECLARE
b BOOLEAN;
jdata CLOB;
BEGIN
SELECT po_document INTO jdata FROM j_purchaseorder
WHERE rownum = 1;
b := json_value(jdata, '$.AllowPartialShipment'
RETURNING BOOLEAN
ERROR ON ERROR);
END;
/
Example 19-4 JSON_VALUE: Returning a JSON Boolean Value to SQL as NUMBER
This examples uses clause ALLOW BOOLEAN TO NUMBER CONVERSION
to return the SQL NUMBER
value 1
, meaning true.
Without that clause, RETURNING NUMBER
raises an error for Boolean JSON
data.
SELECT json_value(po_document, '$.AllowPartialShipment'
RETURNING NUMBER
ALLOW BOOLEAN TO NUMBER CONVERSION)
FROM j_purchaseorder;
Related Topics
Parent topic: SQL/JSON Function JSON_VALUE
19.2 Using JSON_VALUE To Instantiate a User-Defined Object-Type or Collection-Type Instance
You can use SQL/JSON function json_value
to instantiate
an instance of a user-defined SQL object type or collection type. You do this by targeting a
JSON object or array in the path expression and specifying the object or collection type,
respectively, in the RETURNING
clause.
The elements of a targeted JSON array provide the elements of a returned collection-type instance. The JSON array elements must correspond, one-to-one, with the collection-type elements. If they do not then a mismatch error occurs. A JSON array element that is an object (see next) or an array is handled recursively.
The fields of a targeted JSON object provide the attribute values of a returned object-type instance. The JSON fields must correspond, one-to-one, with the object-type attributes. If they do not then a mismatch error occurs.
The field names of the targeted JSON object are compared with the SQL names of the object attributes. A field value that is an array or an object is handled recursively, so that ultimately it is the names of JSON fields with scalar values that are compared with the names of scalar SQL object attributes. If the names do not match (case insensitively, by default), then a mismatch error occurs.
If all names match then the corresponding data types are checked for compatibility. If there is any type incompatibility then a mismatch error occurs. Table 17-2 specifies the compatible scalar data types — any other type combinations are incompatible, which entails a mismatch error.
A mismatch error occurs at query compile time if any of the following
are true. By default, mismatch errors are ignored, but you can change this
error handling by including one or more ON MISMATCH
clauses in your
invocation of json_value
.
-
The fields of a targeted JSON object, or the elements of a targeted JSON array, do not correspond in number and kind to the attributes of the specified object-type instance, or to the elements of the specified collection-type instance, respectively.
-
The fields of a targeted JSON object do not have the same names as the attributes of a specified object-type instance. By default this matching is case-insensitive.
- The JSON and Oracle SQL scalar data types of a JSON value and its corresponding object attribute value or collection element value are not compatible, according to Table 17-2.
You can use json_value
to return an object-type or
collection-type instance in PL/SQL, as well as SQL. However, the behavior of clauses
NULL ON MISMATCH
and NULL ON EMPTY
is slightly
different when returning a record-type or an index table-type instance, because values
of these types cannot be atomically NULL
. See their documentation for
details.
Example 19-5 Instantiate a User-Defined Object Instance From JSON Data with JSON_VALUE
This example defines SQL object types shipping_t
and
addr_t
. Object type shipping_t
has attributes
name
and address
, which have types
VARCHAR2(30)
and addr_t
, respectively.
Object type addr_t
has attributes
street
and city
.
The
example uses json_value
to select the JSON object that is the value
of field ShippingInstructions
and return an instance of SQL object
type shipping_t
. Names of the object-type attributes are matched
against JSON object field names case-insensitively, so that, for example,
attribute address
(which is the same as ADDRESS
)
of SQL object-type shipping_t
matches JSON field
address
.
(The query output is shown pretty-printed here, for clarity.)
CREATE TYPE addr_t AS OBJECT
(street VARCHAR2(100),
city VARCHAR2(30));
-- Create after type addr_t, because that's referred to here.
--
CREATE TYPE shipping_t AS OBJECT
(name VARCHAR2(30),
address addr_t);
-- Query data to return shipping_t instances:
SELECT json_value(po_document, '$.ShippingInstructions'
RETURNING shipping_t)
FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS'RETURNING
--------------------------------------------------------
SHIPPING_T('Alexis Bull',
ADDR_T('200 Sporting Green',
'South San Francisco'))
SHIPPING_T('Sarah Bell',
ADDR_T('200 Sporting Green',
'South San Francisco'))
Example 19-6 Instantiate a Collection Type Instance From JSON Data with JSON_VALUE
This example defines
SQL collection type items_t
and SQL object types
part_t
and item_t
. An instance of collection
type items_t
is a varray of item_t
instances.
Attribute part
of object-type item_t
is itself of
SQL object-type part_t
.
It then uses
json_value
to select the JSON
(The query output is shown pretty-printed here, for clarity.)
CREATE TYPE part_t AS OBJECT
(description VARCHAR2(30),
unitprice NUMBER);
CREATE TYPE item_t AS OBJECT
(itemnumber NUMBER,
part part_t);
CREATE TYPE items_t AS VARRAY(10) OF item_t;
-- Query data to return items_t collections of item_t objects
SELECT json_value(po_document, '$.LineItems' RETURNING items_t)
FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.LINEITEMS'RETURNINGITEMS_TUSIN
--------------------------------------------------------
ITEMS_T(ITEM_T(1, PART_T('One Magic Christmas', 19.95)),
ITEM_T(2, PART_T('Lethal Weapon', 19.95)))
ITEMS_T(ITEM_T(1, PART_T('Making the Grade', 20)),
ITEM_T(2, PART_T('Nixon', 19.95)),
ITEM_T(3, PART_T(NULL, 19.95)))
Related Topics
See Also:
Oracle Database SQL
Language Reference for information about
json_value
Parent topic: SQL/JSON Function JSON_VALUE
19.3 JSON_VALUE as JSON_TABLE
SQL/JSON function json_value
can be viewed as a special
case of function json_table
.
Example 19-7 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
.
Note:
You can use SQL hint NO_JSON_TABLE_TRANSFORM
to prevent rewriting of
multiple invocations of json_exists
, json_value
and json_query
(any combination) to fewer invocations of
json_table
.
Example 19-7 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";
Parent topic: SQL/JSON Function JSON_VALUE
Footnote Legend
Footnote 1: You can useRAW
as the return type only when the input data is of
JSON
data type, and only if the underlying JSON-language
scalar type is binary. Otherwise, an error is processed (handled).