The script content on this page is for navigation purposes only and does not alter the content in any way.
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 is
VARCHAR2(4000)
.
If
json_value
targets the JSON scalar value
null
it returns SQL
NULL
of whatever
the SQL return type is. (For example, for the default return type
VARCHAR2(4000)
it does not return 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.
If
json_value
targets a JSON
array
, and
you specify a SQL
collection type
(varray or nested table) as the
return type, then
json_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, then
json_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
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.
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).
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
.
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
).
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.
JSON_VALUE as JSON_TABLE
SQL/JSON function
json_value
can be viewed as a special case of function
json_table
.
Related Topics
RETURNING Clause for SQL Functions
Error Clause for SQL Functions and Conditions
Empty-Field Clause for SQL/JSON Query Functions
PASSING Clause for SQL Functions and Conditions
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 20-1
illustrates this — the query returns
'true'
.
With a
RETURNING
clause you can specify the return data type.
Example 20-2
illustrates the use of
RETURNING BOOLEAN
to return a
BOOLEAN
value (
true
or
false
) in SQL —
the query returns
true
.
Example 20-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 20-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
.
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 20-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);
Example 20-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;
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 18-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 18-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 20-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.
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 20-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)))
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.
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";
Footnote Legend
Footnote 1: You can use
