2 JSON in Oracle Database
Oracle Database supports JSON data natively with relational database features, including transactions, indexing, declarative querying, and views.
This documentation covers the use of database languages and features to work with JSON data that is stored as such in Oracle Database or is generated from relational data. In particular, it covers how to use SQL and PL/SQL with JSON data.
JSON data generated from relational data can be made accessible as JSON documents through database views.
Use of JSON data by document-centric client applications to create, read, update, and delete JSON documents is not covered in this documentation. That information is provided in the documentation for Oracle Database API for MongoDB and Simple Oracle Document Access (SODA). Using these document APIs your applications can directly create, read, update, and delete JSON documents stored in JSON collection tables or supported by JSON collection views, including JSON-relational duality views.
- Overview of JSON in Oracle Database
Oracle Database supports JSON data natively with relational database features, including transactions, indexing, declarative querying, and views. JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data. You can optionally require JSON data to respect a JSON schema. - JSON Data Type
SQL data typeJSON
represents JSON data using a native binary format, OSON, which is Oracle's optimized format for fast query and update in both Oracle Database server and Oracle Database clients. You can createJSON
type instances from other SQL data, and conversely. - Oracle Database Support for JSON
Oracle Database support for JavaScript Object Notation (JSON) is designed to provide the best fit between the worlds of relational storage and querying JSON data, allowing relational and JSON queries to work well together. Oracle SQL/JSON support is closely aligned with the JSON support in the SQL Standard.
See Also:
-
Overview of JSON-Relational Duality Views in JSON-Relational Duality Developer's Guide
-
Overview of Oracle Database API for MongoDB in Oracle Database API for MongoDB
-
Overview of SODA in Oracle Database Introduction to Simple Oracle Document Access (SODA)
Parent topic: JSON Data and Oracle Database
2.1 Overview of JSON in Oracle Database
Oracle Database supports JSON data natively with relational database features, including transactions, indexing, declarative querying, and views. JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data. You can optionally require JSON data to respect a JSON schema.
Although JSON data can itself be schemaless, when it is stored in the database a database schema is used to define the table and column in which it is stored. Nothing in a database schema specifies the structure of the JSON data itself.
You can optionally validate given JSON data against a JSON schema (see JSON Schema). But most uses of JSON data don't involve JSON Schema; in particular, schema flexibility is an important advantage for application development.
JSON data has often been stored in NoSQL databases such as Oracle NoSQL Database and Oracle Berkeley DB. These allow for storage and retrieval of data that is not based on any schema, but they do not offer the rigorous consistency models of relational databases.
To compensate for this shortcoming, a relational database is sometimes used in parallel with a NoSQL database. Applications using JSON data stored in the NoSQL database must then ensure data integrity themselves.
Native support for JSON by Oracle Database obviates such workarounds. It provides all of the benefits of relational database features for use with JSON data, including transactions, indexing, declarative querying, and views.
Database queries with Structured Query Language (SQL) are declarative. With Oracle Database you can use SQL to join JSON data with relational data. And you can project JSON data relationally, making it available for relational processes and tools. You can also query, from within the database, JSON data that is stored outside Oracle Database in an external table.
You can access JSON data stored in the database the same way you access other database data, including using Oracle Call Interface (OCI), and Java Database Connectivity (JDBC).
With its native binary JSON format, OSON, Oracle
extends the JSON language by adding scalar types, such as date and double, which are not
part of the JSON standard. Oracle SQL data type JSON
uses format
OSON.
Besides storing JSON data you can generate it from stored relational data — see Generation of JSON Data. And the same data can be made available both relationally and as a set of JSON documents — see Overview of JSON-Relational Duality Views in JSON-Relational Duality Developer's Guide.
You can approach storing or generating JSON data in multiple ways. For some use cases a particular approach might be more useful than others. With a use case that's document-centric, an application stores its data as JSON (object) documents. With a hybrid use case, an application uses JSON data together with relational data. Document-centric applications often use a document API or REST, but with Oracle Database they can equally use SQL.
The following breakdown might help you decide which approach to take for a given use case (Figure 2-1 presents the same information graphically).
-
If your use case is hybrid, not mainly document-centric, then use ordinary database tables with
JSON
-type columns as well as relational columns. -
Otherwise (document-centric application), use JSON collections.
-
If you do not want to map JSON documents to relational data, sharing their data with SQL, then use JSON collection tables.
-
Otherwise (JSON data shared with SQL), use JSON views.
-
If you want to be able to update documents directly, then use JSON-relational duality views.
- Otherwise, use JSON collection views.
-
-
See Also:
Overview of JSON-Relational Duality Views in JSON-Relational Duality Developer's Guide
Figure 2-1 JSON Data: Use Cases and Storage/Generation Options
![Description of Figure 2-1 follows Description of Figure 2-1 follows](img/json-data-use-cases-and-storage-or-generation-options.png)
Description of "Figure 2-1 JSON Data: Use Cases and Storage/Generation Options"
- Data Types for JSON Data
SQL data typeJSON
is Oracle's binary JSON format for fast query and update. It extends the standard JSON scalar types (number, string, Boolean, andnull
), to include types that correspond to SQL scalar types. This makes conversion of scalar data between JSON and SQL simple and lossless. - JSON null and SQL NULL
When both SQL code and JSON code are involved, the code and descriptions of it can sometimes be confusing when "null" is involved. Keeping JSON-languagenull
and SQLNULL
values straight requires close attention sometimes. And SQLNULL
can itself be confusing. - JSON Columns in Database Tables
Oracle Database places no restrictions on the tables that can be used to store JSON documents. A column containing JSON documents can coexist with any other kind of database data. A table can also have multiple columns that contain JSON documents. - Use SQL with JSON Data
In SQL, you can create and access JSON data in Oracle Database usingJSON
data type constructorJSON
, specialized functions and conditions, or a simple dot notation. Most of the SQL functions and conditions belong to the SQL/JSON standard, but a few are Oracle-specific. - Use PL/SQL with JSON Data
You can useJSON
data type instances with PL/SQL subprograms. - Use JavaScript with JSON Data
You can use Oracle Database Multilingual Engine (MLE) to exchange JSON data between PL/SQL or SQL code and JavaScript code running in the database server. You can use the node-oracledb driver to run JavaScript code in a database client.
Related Topics
Parent topic: JSON in Oracle Database
2.1.1 Data Types for JSON Data
SQL data type JSON
is Oracle's binary JSON format for fast
query and update. It extends the standard JSON scalar types (number, string, Boolean, and
null
), to include types that correspond to SQL scalar types.
This makes conversion of scalar data between JSON and SQL simple and lossless.
Standard JSON, as a language or notation, has predefined data types: object,
array, number, string, Boolean, and null
. All JSON-language
types except object and array are scalar types.
The standard defines JSON data in a textual way: it is composed of Unicode characters in a standard syntax.
When actual JSON data is used in a programming language or is stored in some way, it is realized using a data type in that particular language or storage format. For example, a JDBC client application might fill a Java string with JSON data, or a database column might store JSON data using a SQL data type.
It's important to keep these two kinds of data type in mind. For example,
though the JSON-language type of JSON value "abc"
is string,
this value can be represented, or realized, using a value of any of several SQL data
types: JSON
, VARCHAR2
, CLOB
, or
BLOB
.
SQL type JSON
is designed specifically for JSON
data. Oracle recommends that for use with Oracle Database you use JSON
type for your JSON data.
JSON
data type uses a binary format, OSON, which is Oracle's optimized binary JSON format for fast query and
update in both Oracle Database server and Oracle Database clients. JSON
type is available only if database initialization parameter compatible
is at least 20
.
Note:
To avoid confusion, this documentation generally refers to the types in
the JSON language as JSON-language types, and it refers to the SQL data
type JSON
as "JSON
type". Paying close attention
to this wording can help you keep straight which meaning of JSON "type" is meant in
a given context.
SQL code that makes use of JSON data can include expressions in both languages, SQL
and JSON. Within SQL code, literal JSON code is typically enclosed within
single-quote characters ('
). Paying attention to this
'
…'
language boundary can
also help understanding.
When you use a SQL type other than
JSON
for JSON data (VARCHAR2
, CLOB
,
or BLOB
), the JSON data is said to be textual — it is unparsed character data (even when it is stored as a
BLOB
instance).
Although Oracle recommends that you use JSON
data type, you might want
to use textual JSON in these use cases:
-
For legacy data that you don't want to convert to
JSON
type for some reason, from releases whereJSON
type didn't exist (releases prior to 21c). -
For use with a database where initialization parameter
compatible
needs to be less than20
for some reason, soJSON
type is not supported. -
For JSON data that exceeds the 32 MB storage limit for
JSON
type. -
For JSON data that must be stored textually, with no alterations, for archival or legal reasons.
You can migrate existing textual JSON data in the database to
JSON
type data, and Oracle recommends that you do so — see
Migrating Textual JSON Data to JSON Data Type.
Note:
By default, a JSON value returned by a simple dot notation query or a SQL operator (such as
json_query
) is returned as JSON
data type if
the input data is JSON
type; otherwise it's returned as type
VARCHAR2(4000)
.
Be aware of this difference in default return type if you migrate JSON data
stored textually to JSON
-type storage. You can override the default
return type by specifying RETURNING VARCHAR2(4000)
for a SQL
operator or using item method string()
, to obtain the previous
behavior. See RETURNING Clause for SQL Functions and SQL/JSON Path Expression Item Methods.
Textual JSON data supports only the standard JSON-language scalar types:
number, string, Boolean, and null. But when JSON data is of SQL type
JSON
, Oracle Database adds types that correspond directly to SQL
scalar data types. This enhances the JSON language, and it makes conversion of
scalar data between that language and SQL simple and lossless. These are the
Oracle-specific JSON-language scalar types:
-
binary — Corresponds to SQL
RAW
orBLOB
. -
date — Corresponds to SQL
DATE
. -
day-second interval — Corresponds to SQL
INTERVAL DAY TO SECOND
. -
double — Corresponds to SQL
BINARY_DOUBLE
. -
float — Corresponds to SQL
BINARY_FLOAT
. -
timestamp — Corresponds to SQL
TIMESTAMP
. -
timestamp with time zone — Corresponds to SQL
TIMESTAMP WITH TIME ZONE
. -
vector — Corresponds to SQL
VECTOR
. -
year-month interval — Corresponds to SQL
INTERVAL YEAR TO MONTH
.
Note:
You can use the JSON path-expression item method type()
to determine the JSON-language type of any JSON scalar value.
It returns the type name as one of these JSON strings:
"binary"
, "boolean"
, "date"
,
"daysecondInterval"
, "double"
,
"float"
, "number"
, "null"
,
"string"
, "timestamp"
, "timestamp with
time zone"
, "vector"
,
"yearmonthInterval"
. For example, if the targeted scalar JSON value is
of type timestamp with time zone then type()
returns the string
"timestamp with time zone"
. See:
Note:
Some tools you use might not print JSON
-type values in a way that
distinguishes their JSON-language type well. For example, a JSON string might be
printed without its double-quote ("
) delimiters —
42
instead of "42"
, for instance, with no
indication whether the value is the JSON number 42
or the JSON
string "42"
. Similarly, a JSON
-type date value
might be printed as "2025-11-01"
, which is indistinguishable from a
JSON string value.
Other tools might not understand JSON
-type at all, and just raise an
error when trying to print a JSON
-type value.
You can determine the types of JSON values in these ways:
-
Use item method
type()
to return the type of a JSON value. -
Use SQL/JSON function
json_serialize
to convertJSON
-type values (returned from queries, for example) to textual JSON (VARCHAR2(4000)
, by default).If you use function
json_serialize
with keywordEXTENDED
, then a JSON scalar of a Oracle-specific JSON-language type is serialized as a textual JSON object that unambiguously and completely represents the Oracle JSON scalar value. For example, the object{"$numberDecimal":31}
represents a JSON scalar value of the nonstandard type decimal number.
Here are some ways to obtain JSON scalar values of such
Oracle-specific JSON-language types in your JSON data that is stored as
JSON
type:
-
Use SQL/JSON generation functions with keywords
RETURNING JSON
. Scalar SQL values used in generating array elements or object field values result in JSON scalar values of corresponding JSON-language types. For example, aBINARY_FLOAT
SQL value results in a float JSON value. See Generation of JSON Data Using SQL. -
Use SQL/JSON function
json_scalar
. For example, applying it to aBINARY_FLOAT
SQL value results in a float JSON value. See SQL/JSON Function JSON_SCALAR. -
Use a database client with client-side encoding to create an Oracle-specific JSON value as
JSON
type before sending that to the database. -
Instantiate PL/SQL object types for JSON with JSON data having Oracle-specific JSON scalar types. This includes updating existing such object-type instances. See PL/SQL Object Types for JSON.
-
Use PL/SQL method
to_json()
on a PL/SQL DOM instance (JSON_ELEMENT_T
instance).
Here are some ways to make use of JSON scalar values of Oracle-specific JSON-language types:
-
Use SQL/JSON condition
json_exists
, comparing the value of a SQL bind variable with the result of applying an item method that corresponds to an Oracle-specific JSON scalar type. See SQL/JSON Condition JSON_EXISTS. -
Use SQL/JSON function
json_value
with aRETURNING
clause that returns a SQL type that corresponds to an Oracle-specific JSON scalar type. See RETURNING Clause for SQL Functions.
2.1.2 JSON null and SQL NULL
When both SQL code and JSON code are involved, the code and descriptions of
it can sometimes be confusing when "null" is involved. Keeping JSON-language
null
and SQL NULL
values straight requires close
attention sometimes. And SQL NULL
can itself be confusing.
-
In the JSON language,
null
is both a value and the name of a (JSON-language) type. Type null has only one possible value,null
. -
In SQL, each data type has a
NULL
value. There is aNULL
value for typeVARCHAR2
, one for typeNUMBER
, …, and one for typeJSON
(Oracle's native binary format for JSON data).
NULL
in SQL typically represents the absence
of a value (missing, unknown, or inapplicable data). But SQL does not distinguish the
absence of a value from the presence of a (SQL) NULL
value.
A SQL value can hold a scalar JSON-language value, and JSON null
is one such value. The SQL value in this case is non-NULL
(of whatever
SQL type is being used to hold the JSON data).
When a JSON
-type instance (for example, a row of a
JSON
-type column) has the SQL value NULL
it
generally means that there is no JSON data present in that instance.
A JSON value of null
is a non-NULL
value
as far as SQL is concerned; it is not the SQL value NULL
. In
particular, SQL condition IS NULL
returns false for a JSON
null
value, and SQL condition IS NOT NULL
returns
true. And SQL/JSON condition json_exists
returns true
when the value whose existence it tests for is JSON null
.
SQL/JSON function json_value
extracts a SQL scalar
value from its input JSON data. If the value to be extracted is JSON
null
, then, by default, json_value
returns SQL
NULL
. (You can override this behavior for a given use of
json_value
by using an ON ERROR
handling clause or
an ON EMPTY
handling clause.)
The same is not true, however, for SQL/JSON function
json_query
or for a simple-dot-notation query. Those return
JSON data. If your database supports JSON
data type, and if
the value to be extracted is JSON null
then they both return that
existing JSON null
value as such; that is, they return what
json_scalar('null')
returns.
Remember that the purpose of json_value
is to return a SQL scalar
value that corresponds to a JSON scalar value that you extract from some JSON
data. There is no SQL scalar value that corresponds to JSON null
in the
same way that, say, SQL value TRUE
corresponds to JSON
true
or SQL number 42
corresponds to JSON number
42
. Oracle JSON
data type has a
null
scalar value, but SQL does not have any equivalent scalar
value.
Q: What's the SQL type of the JSON value
null
?
A: That depends on the code/context. It could be any SQL type that you can use to store JSON data — see Data Types for JSON Data.
Q: What determines the order of JSON null
values and SQL
NULL
values, if both are present in a query result set?
A: By default, returned rows containing SQL NULL
values
are last in the sequence when sorting in ascending order, and they are
first when sorting in descending order. You can use keywords
NULLS FIRST
or NULLS LAST
to override this default
behavior. See SELECT in Oracle Database SQL
Language Reference.
When you extract a scalar value from JSON data, the following can occur:
-
The input JSON data itself is (SQL)
NULL
, so no value is selected. This is the case when a row of data isNULL
, for example. -
The input JSON data is not (SQL)
NULL
but the query (path expression, for example) does not select any scalar value — the targeted value is missing. -
The query selects a JSON
null
value.
The behavior for Case 3 depends on whether your database supports
JSON
data type, that is, whether the value of initialization
parameter compatible
is at least 20.
All data in Table 2-1 is SQL data. Uppercase NULL
indicates a SQL NULL
value. JSON data shown indicates the content of a SQL type (such as
VARCHAR2
or JSON
) that can contain JSON data. A
JSON-language null
value is written in lowercase.
Table 2-1 Handling of SQL NULL, missing, and JSON null Input for JSON-Type Data
Case | JSON Input Data | Dot Notation .a | JSON_VALUE('$.a') | JSON_QUERY('$.a') |
---|---|---|---|---|
Case 1: input data is |
|
|
|
|
Case 2: targeted data is missing |
|
|
|
|
Case 3, with
|
|
|
|
|
Case 3, without
|
|
|
|
|
Note:
Oracle SQL NULL
can itself be a bit confusing. Except for the
large-object (LOB) data types (BLOB
,
(N
)CLOB
, and BFILE
), Oracle
SQL types that can have zero-length values do not distinguish a zero-length value
from the NULL
value. Such types include RAW
and
the character types, such as
(N
)VARCHAR
(2
) and
(N
)CHAR
. This means, in effect, that an "empty
string" value in such a type is no different from the NULL
value of
that type.
Related Topics
Parent topic: Overview of JSON in Oracle Database
2.1.3 JSON Columns in Database Tables
Oracle Database places no restrictions on the tables that can be used to store JSON documents. A column containing JSON documents can coexist with any other kind of database data. A table can also have multiple columns that contain JSON documents.
When using Oracle Database as a JSON document store, your tables that contain JSON columns typically also have a few non-JSON housekeeping columns. These typically track metadata about the JSON documents.
If you use JSON data to add flexibility to a primarily relational application then some of your tables likely also have a column for JSON documents, which you use to manage the application data that does not map directly to your relational model.
Oracle recommends that you use data type JSON
for JSON
columns. If you instead use textual JSON storage (VARCHAR2
,
CLOB
, or BLOB
) then Oracle recommends that you use
an is json
check constraint to ensure that column values are valid JSON instances (see Example 4-2).
By definition, textual JSON data is encoded using a Unicode encoding, either
UTF-8 or UTF-16. You can use VARCHAR2
or CLOB
data
that is stored in a non-Unicode character set as if it were JSON data, but in that case
Oracle Database automatically converts the character set to UTF-8 when processing the
data.
Data stored using data type JSON
or BLOB
is independent of character sets and does not undergo conversion when processing the
data.
Parent topic: Overview of JSON in Oracle Database
2.1.4 Use SQL with JSON Data
In SQL, you can create and access JSON data in Oracle Database using
JSON
data type constructor JSON
, specialized functions
and conditions, or a simple dot notation. Most of the SQL functions and conditions belong to
the SQL/JSON standard, but a few are Oracle-specific.
-
SQL/JSON query functions
json_value
,json_query
, andjson_table
.These evaluate SQL/JSON path expressions against JSON data to produce SQL values.
-
Oracle SQL condition
json_textcontains
and SQL/JSON conditionsjson_exists
,is json
, andis not json
.Condition
json_exists
checks for the existence of given JSON data;json_textcontains
provides full-text querying of JSON data; andis json
andis not json
check whether given JSON data is well-formed.json_exists
andjson_textcontains
check the data that matches a SQL/JSON path expression. -
A simple dot notation that acts similar to a combination of query functions
json_value
andjson_query
.This resembles a SQL object access expression, that is, attribute dot notation for an abstract data type (ADT). This is the easiest way to query JSON data in the database.
-
SQL/JSON generation functions
json_object
,json_array
,json_objectagg
, andjson_arrayagg
.These gather SQL data to produce JSON object and array data (as a SQL value).
-
SQL/JSON functions
json_serialize
andjson_scalar
, and Oracle SQL conditionjson_equal
.Function
json_serialize
returns a textual representation of JSON data;json_scalar
returns aJSON
type scalar value that corresponds to a given SQL scalar value; andjson_equal
tests whether two JSON values are the same. -
JSON
data type constructorJSON
.This parses textual JSON data to create an instance of SQL data type
JSON
. -
Oracle SQL aggregate function
json_dataguide
.This produces JSON data that is a data guide, which you can use to discover information about the structure and content of other JSON data in the database.
As a simple illustration of querying, here is a dot-notation query of the
documents stored in JSON column po_document
of table
j_purchaseorder
(aliased here as po
). It obtains
all purchase-order requestors (JSON field Requestor
).
SELECT po.po_document.Requestor FROM j_purchaseorder po;
Parent topic: Overview of JSON in Oracle Database
2.1.5 Use PL/SQL with JSON Data
You can use JSON
data type instances with PL/SQL
subprograms.
You can manipulate JSON data within PL/SQL code using SQL code or PL/SQL object types.
You can generally use SQL code, including SQL code that accesses JSON data, within PL/SQL code.
The following SQL functions and conditions are also available as
built-in PL/SQL functions: json_value
, json_query
,
json_object
, json_array
,
json_scalar
, json_serialize
,
json_exists
, is json
, is not
json
, and json_equal
.
There are also PL/SQL object types for JSON, which you can use for fine-grained construction and manipulation of In-Memory JSON data. You can construct object-type data, introspect it, modify it, compare it, sort it, and serialize it back to textual JSON data.
You can use JSON
data type instances as input and output
of PL/SQL subprograms. You can manipulate JSON
-type data in PL/SQL
by instantiating JSON object types, such as JSON_OBJECT_T
.
Oracle Database prior to Release 23ai has no BOOLEAN
data type. But for all Oracle Database releases PL/SQL has a
BOOLEAN
data type. For PL/SQL (as well as for SQL, starting
with Release 23ai):
-
json_exists
,is json
,is not json
, andjson_equal
are Boolean functions. -
json_value
can return aBOOLEAN
value.json_table
columns withjson_value
semantics can be of typeBOOLEAN
. -
json_scalar
can accept aBOOLEAN
value as argument, in which case it returns a BooleanJSON
type instance (true
orfalse
). -
json_object
,json_objectagg
,json_array
, andjson_arrayagg
can generate JSON objects and arrays that contain valuestrue
andfalse
, corresponding to PL/SQL valuesTRUE
andFALSE
.Similarly, if you pass SQL
TRUE
orFALSE
tojson_transform
then these are mapped to JSONtrue
andfalse
if included in the transformation result. -
json_exists
andjson_transform
can useBOOLEAN
bind variables.
Using PL/SQL you can create JSON schemas from relational or object-relational data.
PL/SQL also provides subprograms to use JSON Schema, in package
DBMS_JSON_SCHEMA
:
-
You can validate JSON data against a JSON schema using PL/SQL function or procedure
DBMS_JSON_SCHEMA.is_valid()
. The function returns1
for valid and0
for invalid (invalid data can optionally raise an error). The procedure returnsTRUE
for valid andFALSE
for invalid as the value of anOUT
parameter. -
You can use PL/SQL function
DBMS_JSON_SCHEMA.validate_report
to read a validity-check error report. -
You can use PL/SQL function
DBMS_JSON_SCHEMA.is_schema_valid
to check whether a given JSON schema is itself valid according to the JSON Schema standard. -
You can use PL/SQL function
DBMS_JSON_SCHEMA.describe
to generate a JSON schema from a table, view, object type, or collection type, or from a synonym that resolves to one of those.
See Also:
json-schema.org for information about JSON Schema
Parent topic: Overview of JSON in Oracle Database
2.1.6 Use JavaScript with JSON Data
You can use Oracle Database Multilingual Engine (MLE) to exchange JSON data between PL/SQL or SQL code and JavaScript code running in the database server. You can use the node-oracledb driver to run JavaScript code in a database client.
MLE runs JavaScript code dynamically using (1) PL/SQL package
DBMS_MLE
and (2) MLE modules that persist in the database. Using
MLE modules generally offers more flexibility and a better way of separating JavaScript
code from PL/SQL code. MLE modules are analogous to PL/SQL packages, the difference
being that the code is JavaScript instead of PL/SQL.
You can exchange JSON data between JavaScript code running in the database server and database storage in these ways:
- Use server-side MLE JavaScript driver mle-js-oracledb.
- Use JavaScript stored subprograms that refer to an MLE module.
Subprogram arguments (
IN
,OUT
,INOUT
) and return values can be ofJSON
data type. - Use procedures in PL/SQL package
DBMS_MLE
to exchange JSON values between PL/SQL code and JavaScript code.
The data-type mappings used by server-side MLE JavaScript driver mle-js-oracledb, between JSON values (objects, arrays, and scalars) and JavaScript values, are generally aligned with the mappings used by client-side JavaScript driver node-oracledb. The mappings between scalar values differ in some respects however — see MLE Type Conversions.
You can use PL/SQL procedure DBMS_MLE.export_to_mle
to export JSON data
from PL/SQL to a dynamic MLE execution context, and then use it there with JavaScript
code. In the other direction, you can use PL/SQL procedure
DBMS_MLE.import_from_mle
to import objects from MLE JavaScript code
to PL/SQL, and then use them in PL/SQL as JSON objects.
You use JavaScript function importValue()
from built-in
module mle-js-bindings
to import, into the current dynamic MLE
execution context, a value that was previously exported along with a JavaScript variable
name, using PL/SQL procedure DBMS_MLE.export_to_mle
. Function
importValue()
takes that variable name as argument and returns a
JavaScript value, with all scalar values of the JSON data converted to the corresponding
native JavaScript type.
Similarly, you use JavaScript function exportValue()
to export a value
from the current dynamic MLE execution context.
See Also:
-
Overview of Dynamic MLE Execution in Oracle Database JavaScript Developer's Guide
-
MLE JavaScript Functions in Oracle Database JavaScript Developer's Guide
-
MLE Type Conversions in Oracle Database JavaScript Developer's Guidefor information about data-type conversions between JavaScript and PL/SQL or SQL, including to and from JSON-language types represented in SQL data type
JSON
. -
MLE Modules on GitHub for information about mle-js-oracledb and mle-js-bindings
-
Node.js node-oracledb on GitHub
Parent topic: Overview of JSON in Oracle Database
2.2 JSON Data Type
SQL data type JSON
represents JSON data using a native
binary format, OSON, which is Oracle's optimized format for fast
query and update in both Oracle Database server and Oracle Database clients. You can create
JSON
type instances from other SQL data, and conversely.
The other SQL data types that support JSON data, besides
JSON
type, are VARCHAR2
, CLOB
,
and BLOB
. This non-JSON
type data is called textual, or serialized,
JSON data. It is unparsed character data (even when stored as a BLOB
instance, as the data is a sequence of UTF-8 encoded bytes).
Using data type JSON
avoids costly parsing of textual JSON data and
provides better query performance.
You can convert textual JSON data to JSON
type data by
parsing it with type constructor JSON
. JSON text that you
insert into a database column of type JSON
is parsed implicitly — you
need not use the constructor explicitly.
In the other direction, you can convert JSON
type data to
textual JSON data using SQL/JSON function json_serialize
.
JSON
type data that you insert into a database column of a JSON
textual data type (VARCHAR2
, CLOB
, or
BLOB
) is serialized implicitly — you need not use
json_serialize
explicitly.
Regardless of whether the JSON
type data uses
Oracle-specific scalar JSON types (such as date), the resulting serialized JSON data
always conforms to the JSON standard.
You can create complex JSON
type data from
non-JSON
type data using the SQL/JSON generation functions:
json_object
, json_array
,
json_objectagg
, and json_arrayagg
.
You can create a JSON
type instance with a scalar JSON
value using SQL/JSON function json_scalar
. In particular, the value can
be of an Oracle-specific JSON-language type, such as a date, which is not part of the
JSON standard.
In the other direction, you can use SQL/JSON function
json_value
to query JSON
type data and return an
instance of a SQL object type or collection type.
JSON
data type, its constructor JSON
, and
SQL/JSON function json_scalar
can be used only if database
initialization parameter compatible
is at least 20
.
Otherwise, trying to use any of them raises an error.
- JSON Data Type Constructor
TheJSON
data type constructor,JSON
, takes as input a textual JSON value (a scalar, object, or array), parses it, and returns the value as an instance ofJSON
type. Alternatively, the input can be an instance of SQL typeVECTOR
, a user-defined PL/SQL type, or a SQL aggregate type. - SQL/JSON Function JSON_SCALAR
SQL/JSON functionjson_scalar
accepts a SQL scalar value as input and returns a corresponding JSON scalar value as aJSON
type instance. The value can be of an Oracle-specific JSON-language type (such as a date), which is not part of the JSON standard. - SQL/JSON Function JSON_SERIALIZE
SQL/JSON functionjson_serialize
takes JSON data (of SQL data typeBLOB
,CLOB
,JSON
,VARCHAR2
) as input and returns a textual representation of it (asBLOB
orVARCHAR2
data).VARCHAR2(4000)
is the default return type. - JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary
Relations amongJSON
data type constructorJSON
, SQL/JSON functionjson_scalar
, and SQL/JSON functionjson_serialize
are summarized. - Textual JSON Objects That Represent Extended Scalar Values
Native binary JSON data (OSON format) extends the JSON language by adding scalar types, such as date, that correspond to SQL types and are not part of the JSON standard. Oracle Database also supports the use of textual JSON objects that represent JSON scalar values, including such nonstandard values. - Comparison and Sorting of JSON Data Type Values
The canonical sort order for values of SQL data typeJSON
is described. It is used to compare all JSON values. - Comparison of SQL Values With JSON Data Type Values
When comparing aJSON
-type value with a SQL value of a type other thanJSON
, the same rules apply as when comparing twoJSON
-type values of the same family (e.g. numeric values), provided that the SQL type corresponds to one of the JSON-language types of that family.
Related Topics
See Also:
-
Oracle Database SQL Language Reference for information about
JSON
data type -
Oracle Database SQL Language Reference for information about constructor
JSON
-
Oracle Database SQL Language Reference for information about SQL/JSON function
json_scalar
-
Oracle Database SQL Language Reference for information about SQL/JSON function
json_serialize
Parent topic: JSON in Oracle Database
2.2.1 JSON Data Type Constructor
The JSON
data type constructor,
JSON
, takes as input a textual JSON value (a scalar, object,
or array), parses it, and returns the value as an instance of JSON
type.
Alternatively, the input can be an instance of SQL type VECTOR
, a
user-defined PL/SQL type, or a SQL aggregate type.
Note:
You can use constructor JSON
only if database
initialization parameter compatible
is at least
20
. Otherwise, the constructor raises an error (regardless of what
input you pass it).
For example, given SQL string '{}'
as input, the
JSON
type instance returned is the empty object
{}
. The input '{a : {"b":"beta", c:[+042,
"gamma",]},}'
results in the JSON
instance
{"a":{"b":"beta","c":[42,"gamma"]}}
.
(Note that this contrasts with the behavior of SQL/JSON function
json_scalar
, which does not parse textual input but just
converts it to a JSON string value: json_scalar('{}')
returns
the JSON string "{}"
. To produce the same JSON string using constructor
JSON
, you must add explicit double-quote characters:
JSON('"{}"')
.)
Textual input to constructor JSON
can be either a
literal SQL string or data of type VARCHAR2
, CLOB
, or
BLOB
. A SQL NULL
value as input results in a
JSON
type instance of SQL NULL
.
Non-textual input to the constructor can be an instance of type
VECTOR
or any of the following user-defined data types:
-
PL/SQL Varray
-
PL/SQL record
-
SQL object type
-
PL/SQL index by
binary_integer
collection (IBBI) -
PL/SQL nested table
-
PL/SQL associative array
A VECTOR
instance as argument results in an Oracle JSON
scalar value of type vector.
A varray instance as argument results in a JSON array. The JSON-array elements are created from the elements of the varray collection (in order).
Each of the other instances results in a JSON object. The JSON-object members are created from the attributes of a record instance or a SQL object instance, the indexes of an IBBI or nested-table instance, and the key–value pairs of an associative-array instance.
The value returned by the constructor can be any JSON value that is
supported by Oracle. This includes values of the standard JSON-language types: object,
array, string, Boolean, null
, and number. It also includes any
non-standard Oracle scalar JSON values, that is, values of the Oracle-specific scalar
types: binary, date, day-second interval, double, float, timestamp, timestamp with time
zone, vector, and year-month interval. If the constructor is used with keyword
EXTENDED
then the values of the Oracle-specific types
can be derived from Oracle extended-object patterns in the textual JSON input.
If the textual input is not well-formed JSON data then an error is raised. This includes the case where it has one or more objects in it that have duplicate field (key) names. It can, however, have lax JSON syntax. Other than this syntax relaxation, to be well-formed the input data must conform to RFC 8259.
If you need to ensure that the textual input uses only strict JSON
syntax then use SQL condition is json
to filter it. This code prevents
acceptance of non-strict syntax:
SELECT JSON(jcol) FROM table WHERE jcol is json (STRICT);
As a convenience, when using textual JSON data to perform an
INSERT
or UPDATE
operation on a
JSON
type column, the textual data is implicitly wrapped
with constructor JSON
.
Use cases for constructor JSON
include on-the-fly parsing
and conversion of textual JSON data to JSON
type. (An alternative is to
use condition is json
in a WHERE
clause.) You can pass
the constructor a bind variable with a string value or data from an external table, for
instance.
As one example, you can use constructor JSON
to ensure that
textual data that is not stored in the database with an is json
check
constraint is well-formed. You can then use the simple dot-notation query syntax with
the resulting JSON
type data. (You cannot use the dot notation with
data that is not known to be well-formed.) Example 2-1 illustrates this.
Example 2-1 Converting Textual JSON Data to JSON Type On the Fly
This example uses simple dot-notation syntax to select a field from some
textual JSON data that is not known to the database to be well-formed. It converts
the data to JSON
type data, before selecting. Constructor
JSON
raises an error if its argument is not well-formed. (Note
that dot-notation syntax requires the use of a table alias — j
in
this case.)
WITH jtab AS
(SELECT JSON(
'{ "name" : "Alexis Bull",
"Address": { "street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America" } }')
AS jcol FROM DUAL)
SELECT j.jcol.Address.city FROM jtab j;
Related Topics
See Also:
-
JSON Type Constructor in Oracle Database SQL Language Reference for information about constructor
JSON
-
PL/SQL and JSON Type Conversions in Oracle Database PL/SQL Language Reference for information about the conversion of a non-textual argument to a
JSON
-type value
Parent topic: JSON Data Type
2.2.2 SQL/JSON Function JSON_SCALAR
SQL/JSON function json_scalar
accepts a SQL scalar
value as input and returns a corresponding JSON scalar value as a JSON
type
instance. The value can be of an Oracle-specific JSON-language type (such as a date), which
is not part of the JSON standard.
You can use function json_scalar
only if database
initialization parameter compatible
is at least 20
.
Otherwise it raises an error.
You can think of json_scalar
as a scalar generation
function. Unlike the SQL/JSON generation functions, which can return any SQL data type
that supports JSON data, json_scalar
always returns an instance of
JSON
type.
The argument to json_scalar
can be an instance of any of
these SQL data types: BINARY_DOUBLE
, BINARY_FLOAT
,
BLOB
, BOOLEAN
, CHAR
,
CLOB
, DATE
, INTERVAL DAY TO
SECOND
, INTERVAL YEAR TO MONTH
, JSON
,
NCHAR
, NCLOB
, NUMBER
,
NVARCHAR2
, RAW
, TIMESTAMP
,
TIMESTAMP WITH TIME ZONE
, VARCHAR
,
VARCHAR2
, or VECTOR
.
The returned JSON
type instance is a JSON-language scalar
value supported by Oracle. For example, json_scalar(current_timestamp)
returns an Oracle JSON value of type timestamp
(as an instance of SQL
data type JSON
).
With JSON
type input, json_scalar
behaves
as follows:
-
Input that corresponds to a JSON scalar value is simply returned.
-
Input that corresponds to a JSON nonscalar value results in an error. If the error handler is
NULL ON ERROR
, which it is by default, then SQLNULL
(ofJSON
data type) is returned.
Tip:
Because json_scalar
returns NULL
by
default for nonscalar input, and because comparison involving a nonscalar JSON value
can be more costly than scalar-with-scalar comparison, a simple manual optimization
when ordering or comparing JSON data is to do so after wrapping it with
json_scalar
, thus effectively pruning nonscalars from the data
to be compared. (More precisely, they are replaced with NULL
, which
is quickly compared.)
For example instead of this:
SELECT data FROM customers c
ORDER BY c.data.revenue;
Use this:
SELECT data FROM customers c
ORDER BY json_scalar(c.data.revenue);
Note:
You can use the JSON path-expression item method type()
to determine the JSON-language type of any JSON scalar value.
It returns the type name as one of these JSON strings:
"binary"
, "boolean"
, "date"
,
"daysecondInterval"
, "double"
,
"float"
, "number"
, "null"
,
"string"
, "timestamp"
, "timestamp with
time zone"
, "vector"
,
"yearmonthInterval"
. For example, if the targeted scalar JSON value is
of type timestamp with time zone then type()
returns the string
"timestamp with time zone"
. See:
Table 2-2 JSON_SCALAR Type Conversion: SQL Types to Oracle JSON Types
SQL Type (Source) | JSON Language Type (Destination) |
---|---|
VARCHAR2 , VARCHAR ,
NVARCHAR2 , CHAR , or
NCHAR |
string |
CLOB or NCLOB |
string |
BLOB |
binary |
RAW |
binary |
BOOLEAN |
boolean |
NUMBER |
number (or string if infinite or undefined value) |
BINARY_DOUBLE |
double (or string if infinite or undefined value) |
BINARY_FLOAT |
float (or string if infinite or undefined value) |
DATE |
date |
TIMESTAMP |
timestamp |
TIMESTAMP WITH TIME ZONE |
timestamp with time zone |
INTERVAL DAY TO SECOND |
daysecondInterval |
INTERVAL YEAR TO MONTH |
yearmonthInterval |
An exception are the numeric values of positive and negative infinity,
and values that are the undefined result of a numeric operation ("not a number" or
NaN
) — they cannot be expressed as JSON numbers. For those,
json_scalar
returns not numeric-type values but the JSON strings
"Inf"
, "-Inf"
, and
"Nan"
, respectively.
A JSON
type value returned by json_scalar
remembers the SQL data type from which it was derived. If you then use
json_value
(or a json_table
column with
json_value
semantics) to extract that JSON
type
value, and you use the corresponding type-conversion item method, then the value
extracted has the original SQL data type. For example, this query returns a SQL
TIMESTAMP
value:
SELECT json_value(json_scalar(current_timestamp), '$.timestamp()')
FROM DUAL;
Note that if the argument is a SQL string value
(VARCHAR2
. VARCHAR
, NVARCHAR
,
CHAR
, NCHAR
, or CLOB
) then
json_scalar
simply converts it to a JSON string value. It
does not parse the input as JSON data.
For example, json_scalar('{}')
returns the JSON string value
"{}"
. Because constructor JSON
parses a SQL
string, it returns the empty JSON object {}
for the same input. To
produce the same JSON string using constructor JSON
, the double-quote
characters must be explicitly present in the input:
JSON('"{}"')
.
If the argument to json_scalar
is a SQL
NULL
value then you can obtain a return value as follows:
-
SQL
NULL
, the default behavior -
JSON
null
, using keywordsJSON NULL ON NULL
(keywordJSON
is optional) -
An empty JSON string,
""
, using keywordsEMPTY STRING ON NULL
The default behavior of returning SQL NULL
is the only
exception to the rule that a JSON scalar value is returned.
Note:
Be aware that, although
function json_scalar
preserves timestamp values, it drops any
time-zone information from a timestamp. The time-zone information is taken into
account by converting to UTC time. See Table 2-4.
If you need to add explicit time-zone
information as JSON data then record it separately from a SQL TIMESTAMP WITH
TIME ZONE
instance and pass that to a JSON generation function. Example 2-2 illustrates this.
Example 2-2 Adding Time Zone Information to JSON Data
This example inserts a TIMESTAMP WITH TIME
ZONE
value into a table, then uses generation function
json_object
to construct a JSON object. It uses SQL functions
json_scalar
and extract
to provide the JSON
timestamp and numeric time-zone inputs for
json_object
.
CREATE TABLE t (tz TIMESTAMP WITH TIME ZONE);
INSERT INTO t
VALUES (to_timestamp_tz('2019-05-03 20:00:00 -8:30',
'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
-- This query returns the UTC timestamp value "2019-05-04T04:30:00"
SELECT json_scalar(tz) FROM t;
-- Create a JSON object that has 3 fields:
-- timestamp: JSON timestamp value (UTC time):
-- timeZoneHours: hours component of the time zone, as a JSON number
-- timeZoneMinutes: minutes component of the time zone, as a JSON number
SELECT json_object('timestamp' : json_scalar(tz),
'timezoneHours' : extract(TIMEZONE_HOUR FROM tz),
'timezoneMinutes' : extract(TIMEZONE_MINUTE FROM tz))
FROM t;
-- That query returns a JSON object and prints it in serialized form.
-- The JSON timestamp value is serialized as an ISO 8601 date-time string.
-- The time-zone values (JSON numbers) are serialized as numbers.
--
-- {"timestamp" : "2019-05-04T04:30:00",
-- "timezoneHours" : -8,
-- "timezoneMinutes" : -30}
Related Topics
See Also:
-
JSON_SCALAR in Oracle Database SQL Language Reference for information about SQL/JSON function
json_scalar
-
JSON Data Type in Oracle Database SQL Language Reference
-
Character Data Types in Oracle Database SQL Language Reference for information about SQL data types
CHAR
,NCHAR
,VARCHAR2
,VARCHAR
, andNVARCHAR2
-
Large Object (LOB) Data Types in Oracle Database SQL Language Reference for information about SQL data types
BLOB
,CLOB
, andNCLOB
-
Numeric Data Types in Oracle Database SQL Language Reference for information about SQL data types
NUMBER
,BINARY_DOUBLE
, andBINARY_FLOAT
-
Datetime and Interval Data Types in Oracle Database SQL Language Reference for information about SQL data types
DATE
,TIMESTAMP
,TIMESTAMP WITH TIME ZONE
,INTERVAL YEAR TO MONTH
, andINTERVAL DAY TO SECOND
-
Boolean Data Type in Oracle Database SQL Language Reference
-
RAW and LONG RAW Data Types in Oracle Database SQL Language Reference for information about SQL data type
RAW
Parent topic: JSON Data Type
2.2.3 SQL/JSON Function JSON_SERIALIZE
SQL/JSON function json_serialize
takes JSON data (of
SQL data type BLOB
, CLOB
, JSON
,
VARCHAR2
) as input and returns a textual representation of it
(as BLOB
or VARCHAR2
data).
VARCHAR2(4000)
is the default return type.
You typically use json_serialize
to transform the result of
a query. The function supports an error clause and a returning clause. You can
optionally do any combination of the following:Foot 1
-
Automatically escape all non-ASCII Unicode characters, using standard ASCII Unicode escape sequences (keyword
ASCII
). -
Pretty-print the result (keyword
PRETTY
). -
Order the members of objects in the result — ascending alphabetical order by field name (keyword
ORDERED
).The order is defined by the
VARCHAR2
collation with binary ordering as represented in the AL32UTF8 character set. Put differently, characters are ordered according to their Unicode code points. -
Truncate the result to fit the return type (keyword
TRUNCATE
). -
Translate values of Oracle-specific scalar JSON-language types to Oracle extended-object patterns (keyword
EXTENDED
) — see Textual JSON Objects That Represent Extended Scalar Values.
See Example 2-3 and Example 2-4.
By default, function json_serialize
always produces JSON
data that conforms to the JSON standard (RFC 8259), in which case the returned data uses
only the standard data types of the JSON language: object, array, and the scalar
types string, number, Boolean, and null.
The stored JSON data that gets serialized can also have values of scalar types that Oracle has added to the JSON language. JSON data of such types is converted when serialized according to Table 2-3. For example, a numeric value of JSON-language type double is serialized by converting it to a textual representation of a JSON number.
Note:
Input JSON string values are returned verbatim (no change). If you want to
serialize a nonstring scalar JSON value using a different format from what is
specified here, then first use a SQL conversion function such as
to_char
to produce the string value formatted as you want, and
pass that value to json_serialize
.
Table 2-3 JSON_SERIALIZE Converts Oracle JSON-Language Types To Standard JSON-Language Types
Oracle JSON Scalar Type (Reported by type()) | Standard JSON Type | Notes |
---|---|---|
binary | string |
Conversion is equivalent to the use of SQL function
|
date | string |
The string is in an ISO 8601 date format:
YYYY-MM-DD. For example:
|
daysecondInterval | string |
The string is in an ISO 8601 duration format that
corresponds to a ds_iso_format specified for SQL function
PdDThHmMsS,
where d, h, m, and s are digit sequences for the
number of days, hours, minutes, and seconds, respectively. For example:
s can also be an integer-part digit sequence
followed by a decimal point and a fractional-part digit sequence. For example:
Any sequence whose value would be zero is omitted,
along with its designator. For example: |
double | number |
Conversion is equivalent to the use of SQL function
|
float | number |
Conversion is equivalent to the use of SQL function
|
timestamp | string |
The string is in an ISO 8601 date-with-time format:
YYYY-MM-DDThh:mm:ss.ssssss. For example:
|
timestamp with time zone | string | The string is in an ISO 8601 date-with-time format:
YYYY-MM-DDThh:mm:ss.ssssss(+|-)hh:mm or, for a zero offset
from UTC, YYYY-MM-DDThh:mm:ss.ssssssZ For example:
"2019-05-21T10:04:02.123000-08:00" or
"2019-05-21T10:04:02.123000Z" .
|
vector | array | The elements of the JSON array are JSON numbers. They are converted from the SQL numbers in the vector. |
yearmonthInterval | string |
The string is in an ISO 8601 duration format that
corresponds to a ym_iso_format specified for SQL function
PyYmM, where y
is a digit sequence for the number of years and m is a digit sequence for
the number of months. For example: If the number of years or months is
zero then it and its designator are omitted. Examples: |
You can use json_serialize
to convert binary JSON data to
textual form (CLOB
or VARCHAR2
), or to transform
textual JSON data by pretty-printing it or escaping non-ASCII Unicode characters in it.
An important use case is serializing JSON data that is stored in a BLOB
or JSON
type column.
(You can use JSON
data type only if database initialization
parameter compatible
is at least 20.)
A BLOB
result is in the AL32UTF8 character set. But whatever the data type returned by
json_serialize
, the returned data represents textual JSON data.
Note:
You can use the JSON path-expression item method type()
to determine the JSON-language type of any JSON scalar value.
It returns the type name as one of these JSON strings:
"binary"
, "boolean"
, "date"
,
"daysecondInterval"
, "double"
,
"float"
, "number"
, "null"
,
"string"
, "timestamp"
, "timestamp with
time zone"
, "vector"
,
"yearmonthInterval"
. For example, if the targeted scalar JSON value is
of type timestamp with time zone then type()
returns the string
"timestamp with time zone"
. See:
Note:
You can serialize a SQL VECTOR
instance to a textual
JSON array of numbers using SQL function vector_serialize
.
(Function json_serialize
serializes only JSON data. See
vector_serialize in Oracle Database SQL
Language Reference.)
See Also:
-
JSON_SERIALIZE in Oracle Database SQL Language Reference for information about SQL/JSON function
json_serialize
-
RAWTOHEX in Oracle Database SQL Language Reference for information about SQL function
rawtohex
- TO_NUMBER in Oracle Database SQL
Language Reference for
information about SQL function
to_number
Example 2-3 Using JSON_SERIALIZE To Convert JSON type or BLOB Data To Pretty-Printed Text with Ordered Object Members
This example serializes, orders object members, and pretty-prints the JSON purchase
order that has 1600
as the value of field PONumber
data, which is selected from column po_document
of table
j_purchaseorder
. The return-value data type is
VARCHAR2(4000)
(the default return type).
Example 4-1 shows the creation of a table with a JSON
type
column. You can also use json_serialize
to serialize
BLOB
data.
SELECT json_serialize(po_document PRETTY ORDERED)
FROM j_purchaseorder po
WHERE po.po_document.PONumber = 1600;
Example 2-4 Using JSON_SERIALIZE To Convert Non-ASCII Unicode Characters to ASCII Escape Codes
This example serializes an object that has a string field value with a non-ASCII character (€). It also orders the fields alphabetically.
SELECT json_serialize('{"price" : 20, "currency" : "€"}' ASCII ORDERED)
FROM DUAL;
The query returns {"currency" : "\u20AC", "price" : 20}
.
Related Topics
- Overview of JSON in Oracle Database
- Character Sets and Character Encoding for JSON Data
- Support for RFC 8259: JSON Scalars
- Overview of Storing and Managing JSON Data
- Error Clause for SQL Functions and Conditions
- Textual JSON Objects That Represent Extended Scalar Values
- Comparison and Sorting of JSON Data Type Values
- SQL/JSON Path Expression Item Methods
Parent topic: JSON Data Type
2.2.4 JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary
Relations among JSON
data type constructor
JSON
, SQL/JSON function json_scalar
, and SQL/JSON
function json_serialize
are summarized.
Both constructor JSON
and function
json_scalar
accept an instance of a SQL type other than
JSON
and return an instance of JSON
data type.
The constructor accepts only (1) textual JSON data as input: a
VARCHAR2
, CLOB
, or BLOB
instance
or (2) a VECTOR
type instance. It raises an error for any other input
data type.
Function json_scalar
accepts an instance of any of several
scalar SQL types as input. For VARCHAR2
or CLOB
input
it always returns a JSON-language string, as an instance of
JSON
type.
The value returned by the constructor can be any JSON value that is
supported by Oracle, including values of the Oracle-specific scalar types: binary, date,
day-second interval, double, float, timestamp, timestamp with time zone, vector, and
year-month interval. If the constructor is used with keyword EXTENDED
then the values can be derived from Oracle extended-object patterns in the textual JSON
input.
The JSON value returned by json_scalar
is always a scalar —
same JSON-language types as for the constructor, except for the nonscalar types (object
and array). For example, an instance of SQL type DOUBLE
as input
results in a JSON
type instance representing a value of
(Oracle-specific) JSON-language type double.
When SQL/JSON function json_serialize
is applied to a
JSON
type instance, any non-standard Oracle scalar JSON value is
returned as a standard JSON scalar value. But if json_serialize
is used
with keyword EXTENDED
then values of Oracle-specific scalar
JSON-language types can be serialized to Oracle extended-object patterns in the textual
JSON output. (You can also apply json_serialize
to a
VECTOR
type instance, in which case it returns a textual JSON array
of numbers.)
Table 2-4 summarizes the effects of using constructor JSON
and
SQL function json_scalar
for various SQL values as JSON data, producing
JSON
type instances, and it shows the effect of serializing those
instances.
The constructor parses the input, which must be textual JSON data (or
else an error is raised). Function json_scalar
converts its input SQL
scalar value to a JSON-language scalar value. VARCHAR2
or
CLOB
input to json_scalar
always results in a JSON
string value (the input is not parsed as JSON data).
Except for the following facts, the result of serializing a value
produced by the constructor is the same textual representation as was accepted
by the constructor (but the textual SQL data type need not be the same, among
VARCHAR2
, CLOB
, and BLOB
):
-
The constructor accepts lax JSON syntax and
json_serialize
always returns strict syntax. -
If any input JSON objects have duplicate field names then all but one of the field–value pairs is dropped by the constructor.
-
The order of field–value pairs in an object is not, in general, preserved: output order can differ from input order.
-
If the textual data to which the constructor is applied contains extended JSON constructs (JSON objects that specify non-standard scalar JSON values), then the resulting
JSON
type data can (with keywordEXTENDED
) have some scalar values that result from translating those constructs to SQL scalar values. Ifjson_serialize
(with keywordEXTENDED
) is applied to the resultingJSON
type data then the result can include some extended JSON constructs that result from translating in the reverse direction.The translations in these two directions are not, in general, inverse operations, however. They are exact inverses only for Oracle, not non-Oracle, extended JSON constructs. Because extended JSON constructs are translated to Oracle-specific JSON scalar values in
JSON
type, their serialization back to textual JSON data as extended JSON objects can be lossy when they are originally of a non-Oracle format.
Table 2-4 Effect of Constructor JSON and SQL/JSON Function JSON_SCALAR: Examples
Input SQL Value | SQL Type | JSON Value from JSON Constructor | JSON Scalar Value from JSON_SCALAR |
---|---|---|---|
{a:1} |
VARCHAR2 |
|
|
[1,2,3] |
VARCHAR2 |
|
|
TRUE (case-insensitive)
|
BOOLEAN |
|
Same as |
true |
VARCHAR2 |
|
|
null |
VARCHAR2 |
|
|
NULL Foot 2
|
VARCHAR2 |
|
|
"city" |
VARCHAR2 |
|
|
city |
VARCHAR2 |
Error — input is not valid JSON data
(there is no JSON scalar value |
|
{"$numberDouble" : "1E300"} or
{"$numberDouble" : 1E300} (An extended JSON object.) |
VARCHAR2 |
JSON scalar of type double |
A JSON string with the same content as the input
|
{"$numberDecimal" : "1E300"} or
{"$numberDecimal" : 1E300} (An extended JSON object.) |
VARCHAR2 |
JSON scalar of type number, tagged internally as having
been derived from a |
A JSON string with the same content as the input
|
{"$oid" : "deadbeefcafe0123456789ab"} or
{"$rawid" : "deadbeefcafe0123456789ab"} (An extended JSON object.) |
VARCHAR2 |
JSON scalar of type binary, tagged internally as having
been derived from a |
A JSON string with the same content as the input
|
{"$date" : "2020-11-24T12:34:56"} or
{"$oracleDate" : "2020-11-24T12:34:56"} (An extended JSON object.) |
VARCHAR2 |
JSON scalar of type date, tagged internally as having
been derived from an |
A JSON string with the same content as the input
|
3.14 |
VARCHAR2 |
|
|
3.14 |
NUMBER |
Error — not textual JSON data (SQL types
other than |
|
3.14 |
BINARY_DOUBLE |
Error — not textual JSON data (SQL types
other than |
|
3.14
|
NUMBER , tagged internally as having
been derived from a $numberDecimal extended
object
|
JSON scalar of type number, tagged internally as having
been derived from a |
A JSON string with the same content as the original extended object |
A RAW value
|
RAW , tagged internally as having been derived from a
$rawid or $oid extended
object
|
JSON scalar of type binary, tagged internally as having
been derived from a |
A JSON string with the same content as the original extended object |
SQL date value from evaluating
to_date('20.07.1974') |
DATE |
Error — not textual JSON data |
|
SQL timestamp value from evaluating
to_timestamp('2019-05-23 11:31:04.123', 'YYYY-MM-DD
HH24:MI:SS.FF') |
TIMESTAMP |
Error — not textual JSON data |
|
SQL timestamp value from evaluating
to_timestamp_tz('2019-05-23 11:31:04.123 -8', 'YYYY-MM-DD
HH24:MI:SS.FF TZH') |
TIMESTAMP WITH TIMEZONE |
Error — not textual JSON data |
|
VECTOR instance
|
VECTOR |
JSON scalar of type vector | JSON scalar of type vector |
Footnote 2 This is the SQL
NULL
value for type VARCHAR2
,
not a SQL string with characters
NULL
.
Related Topics
See Also:
-
JSON Type Constructor in Oracle Database SQL Language Reference
-
JSON_SCALAR in Oracle Database SQL Language Reference
-
JSON_SERIALIZE in Oracle Database SQL Language Reference
Parent topic: JSON Data Type
2.2.5 Textual JSON Objects That Represent Extended Scalar Values
Native binary JSON data (OSON format) extends the JSON language by adding scalar types, such as date, that correspond to SQL types and are not part of the JSON standard. Oracle Database also supports the use of textual JSON objects that represent JSON scalar values, including such nonstandard values.
When you create native binary JSON data from textual JSON data that contains such extended objects, they can optionally be replaced with corresponding (native binary) JSON scalar values.
An example of an extended object is {"$numberDecimal":31}
.
It represents a JSON scalar value of the nonstandard type decimal number, and
when interpreted as such it is replaced by a decimal number in native binary format.
For example, when you use the JSON data type constructor,
JSON
, if you use keyword EXTENDED
then recognized
extended objects in the textual input are replaced with corresponding scalar values in
the native binary JSON result. If you do not include keyword EXTENDED
then no such replacement occurs; the textual extended JSON objects are simply converted
as-is to JSON objects in the native binary format.
In the opposite direction, when you use SQL/JSON function
json_serialize
to serialize binary JSON data as textual JSON data
(VARCHAR2
, CLOB
, or BLOB
), you
can use keyword EXTENDED
to replace (native binary) JSON scalar values
with corresponding textual extended JSON objects.
Note:
If the database you use is an Oracle Autonomous Database then you can
use PL/SQL procedure DBMS_CLOUD.copy_collection
to create a JSON
document collection from a file of JSON data such as that produced by common NoSQL
databases, including Oracle NoSQL Database.
If you use ejson
as the value of the
type
parameter of the procedure, then recognized extended JSON
objects in the input file are replaced with corresponding scalar values in the
resulting native binary JSON collection. In the other direction, you can use
function json_serialize
with keyword EXTENDED
to
replace scalar values with extended JSON objects in the resulting textual JSON
data.
These are the two main use cases for extended objects:
-
Exchange (import/export):
-
Ingest existing JSON data (from somewhere) that contains extended objects.
-
Serialize native binary JSON data as textual JSON data with extended objects, for some use outside the database.
-
-
Inspection of native binary JSON data: see what you have by looking at corresponding extended objects.
For exchange purposes, you can ingest JSON data from a file produced by common NoSQL databases, including Oracle NoSQL Database, converting extended objects to native binary JSON scalars. In the other direction, you can export native binary JSON data as textual data, replacing Oracle-specific scalar JSON values with corresponding textual extended JSON objects.
Tip:
As an example of inspection, consider an object such as {"dob" :
"2000-01-02T00:00:00"}
as the result of serializing native JSON data.
Is "2000-01-02T00:00:00"
the result of serializing a native binary
value of type date, or is the native binary value just a string? Using
json_serialize
with keyword EXTENDED
lets you
know.
The mapping of extended object fields to scalar JSON types is, in general,
many-to-one: more than one kind of extended JSON object can be mapped to a given scalar
value. For example, the extended JSON objects {"$numberDecimal":"31"}
and {"$numberLong:"31"}
are both translated as the value 31 of
JSON-language scalar type number, and item method type()
returns
"number"
for each of those JSON scalars.
Item method type()
reports the JSON-language scalar type of
its targeted value (as a JSON string). Some scalar values are distinguishable
internally, even when they have the same scalar type. This generally allows function
json_serialize
(with keyword EXTENDED
) to
reconstruct the original extended JSON object. Such scalar values are distinguished
internally either by using different SQL types to implement them or by tagging
them with the kind of extended JSON object from which they were derived.
When json_serialize
reconstructs the original extended JSON
object the result is not always textually identical to the original, but it is
always semantically equivalent. For example,
{"$numberDecimal":"31"}
and
{"$numberDecimal":31}
are semantically equivalent, even
though the field values differ in type (string and number). They are translated to the
same internal value, and each is tagged as being derived from a
$numberDecimal
extended object (same tag). But when serialized, the
result for both is {"$numberDecimal":31}
. Oracle always uses
the most directly relevant type for the field value, which in this case is the
JSON-language value 31
, of scalar type number.
Table 2-5 presents correspondences among the various types used. It maps across
(1) types of extended objects used as input, (2) types reported by item method
type()
, (3) SQL types used internally, (4) standard JSON-language
types used as output by function json_serialize
, and (5) types of
extended objects output by json_serialize
when keyword
EXTENDED
is specified.
Table 2-5 Extended JSON Object Type Relations
Extended Object Type (Input) | Oracle JSON Scalar Type (Reported by type()) | SQL Scalar Type | Standard JSON Scalar Type (Output) | Extended Object Type (Output) |
---|---|---|---|---|
$numberDouble with value a JSON number,
a string representing the number, or one of these strings:
"Infinity" , "-Infinity" ,
"Inf" , "-Inf" ,
"Nan" Foot 3
|
double | BINARY_DOUBLE |
number |
$numberDouble with value a JSON number
or one of these strings: "Inf" ,
"-Inf" , "Nan" Foot 4 |
$numberFloat with value the same as for
$numberDouble |
float | BINARY_FLOAT |
number |
$numberFloat with value the same as for
$numberDouble |
$numberDecimal with value the same as
for $numberDouble |
number | NUMBER |
number |
$numberDecimal with value the same as
for $numberDouble |
$numberInt with value a signed 32-bit
integer or a string representing the number
|
number | NUMBER |
number |
$numberInt with value the same as for
$numberDouble |
$numberLong with value a JSON number or
a string representing the number
|
number | NUMBER |
number |
$numberLong with value the same as for
$numberDouble |
When the value is a string of base-64 characters, the
extended object can also have field |
binary | BLOB or RAW |
string Conversion is equivalent to the use of SQL function
|
One of the following:
|
$oid with value a string of 24 hexadecimal
characters
|
binary | RAW(12) |
string Conversion is equivalent to the use of SQL function
|
$rawid with value a string of 24 hexadecimal
characters
|
$rawhex with value a string with an even number of
hexadecimal characters
|
binary | RAW |
string Conversion is equivalent to the use of SQL function
|
$binary with value a string of base-64 characters,
right-padded with = characters
|
$rawid with value a string of 24 or 32 hexadecimal
characters
|
binary | RAW |
string Conversion is equivalent to the use of SQL function
|
$rawid |
$oracleDate with value an ISO 8601 date
string
|
date | DATE |
string |
$oracleDate with value an ISO 8601 date
string
|
$oracleTimestamp with value an ISO 8601 timestamp
string
|
timestamp | TIMESTAMP |
string |
$oracleTimestamp with value an ISO 8601 timestamp
string
|
$oracleTimestampTZ with value an ISO
8601 timestamp string with a numeric time zone offset or with
Z |
timestamp with time zone | TIMESTAMP WITH TIME ZONE |
string |
$oracleTimestampTZ with value an ISO
8601 timestamp string with a numeric time zone offset or with
Z |
|
timestamp with time zone | TIMESTAMP WITH TIME ZONE |
string |
$oracleTimestampTZ with value an ISO
8601 timestamp string with a numeric time zone offset or with
Z |
$intervalDaySecond with value an ISO 8601 interval
string as specified for SQL function
to_dsinterval |
daysecondInterval | INTERVAL DAY TO SECOND |
string |
$intervalDaySecond with value an ISO 8601 interval
string as specified for SQL function
to_dsinterval |
$intervalYearMonth with value an ISO
8601 interval string as specified for SQL function
to_yminterval |
yearmonthInterval | INTERVAL YEAR TO MONTH |
string |
$intervalYearMonth with value an ISO
8601 interval string as specified for SQL function
to_yminterval |
Two fields:
|
vector | VECTOR |
array of numbers |
Two fields:
|
Footnote 3 The string values are
interpreted case-insensitively. For example, "NAN"
"nan"
, and "nAn"
are accepted and
equivalent, and similarly "INF"
,
"inFinity"
, and "iNf"
.
Infinitely large ("Infinity"
or
"Inf"
) and small ("-Infinity"
or "-Inf"
) numbers are accepted with either the
full word or the abbreviation.
Footnote 4 On output, only these string values are used — no full-word Infinity or letter-case variants.
Related Topics
Parent topic: JSON Data Type
2.2.6 Comparison and Sorting of JSON Data Type Values
The canonical sort order for values of SQL data type JSON
is described. It is used to compare all JSON values.
You can directly compare or sort values of JSON
data type
of any kind — whether scalar, object, or array. This means you can use
JSON
type directly in a WHERE
clause, an
ORDER BY
clause, or a GROUP BY
clause. The
canonical sort order is defined across all
JSON
-type values, including scalar values derived from Oracle
extended-object patterns in textual JSON input.
Note:
A JSON-language scalar value of type vector is compared or sorted by first converting it to a JSON array of numbers; the resulting array is the value that is then compared or sorted.
Note:
When comparing values of JSON
data type in SQL,
the size of the values being compared, as encoded for SQL comparison, must be
less than 32K bytes. Otherwise, an error is raised. In practice, this SQL
encoded-for-comparison size is roughly the size of a textual representation
of the same JSON data.
For example, in this query the encoded sizes of fields
dept
and name
must each be less than 32K:
SELECT *
FROM emp t
WHERE t.data.dept = 'SALES' ORDER BY t.data.name
This limit applies to SQL clauses ORDER BY
and
GROUP BY
, as well as to the use of SQL-value comparison
operators (such as >
in a WHERE
clause).
More precisely, the limit applies only to comparison and sorting done by
SQL itself. It does not apply to comparison or sorting done within the JSON
language. That is, there's no size limit for comparison or sorting done by a SQL
operator for JSON, such as json_transform
or
json_exists
. In particular, the limit doesn't apply to
comparisons made in SQL/JSON path expressions.
The JSON
-type sort (comparison) order is as follows.Foot 5
-
A scalar value sorts before a nonscalar value (after, with keyword
DESC
). -
An object sorts before an array (after, with keyword
DESC
). -
Two arrays are sorted by comparing their elements, in order. When two corresponding elements are unequal, the sort order of those elements determines the order of the two arrays. For example, with ascending sort order
[4, 2, 5]
sorts before[4, 2, 9]
because5
sorts before9
.If all elements of one array are equal to the corresponding elements of a longer array, the shorter array sorts before the longer one. For example, with ascending sort order
[4, 2]
sorts before[4, 2, 5]
, but it sorts after[4, 1, 5]
. -
Two objects are sorted first by field name, then by field value, as follows:
-
The members of each object are ordered by field name.
Field names are compared as JSON
string
values, which uses theVARCHAR2
collation with binary ordering as represented in the AL32UTF8 character set. -
Members of the sorted objects (from step 1) are compared, in order:
-
When two corresponding field names are different, the object with the field name that sorts first is sorted before the other object (after, with keyword
DESC
). - When two corresponding field names are the same, the
field values are compared, according to the
JSON
-type sort order. (That is, field values are compared recursively.) The order of the two objects being compared follows that of their field values.
-
-
-
Two scalars of different type families are sorted in this ascending order by family — so, for example, a number sorts before a string. (For descending sort the order is reversed.)
null
- Numeric (
number
,double
,float
) string
- Nonidentifier
binary
(e.g., images) - Identifier
binary
(e.g., values from extended objects with field$oid
or$rawid
) boolean
- Date and time points (
date
,timestamp
, ortimestamp with time zone
) yearmonthInterval
daysecondInterval
There are two separate families for both date-time interval values and binary values, because the values need to be compared and sorted separately. Different months can have a different number of days. Binary values that are used as identifiers are typically tested for equality; equality testing, even when possible, is typically not useful for nonidentifier binary values.
-
Two scalars of the same type family are sorted by the sort order defined for that family. For example, with ascending sort order,
100
sorts before200.0
regardless of the numeric types used, and"cat"
sorts before"dog"
regardless of the character set used.The scalar JSON comparison used is the collation for the corresponding SQL scalar type, except that a JSON
string
comparison uses theVARCHAR2
collation with binary ordering as represented in the AL32UTF8 character set. Forboolean
values,false
sorts beforetrue
(after, with keywordDESC
).
You can compare any values of JSON
type for purposes of sorting, such as
is done by ORDER BY
. But comparison for other purposes, for example in
a comparison filter condition, is more limited.
Apart from sorting, you can compare any values that are in the same
type family in any way. Values from different type families are always unequal:
comparison for equality (==
) yields false and comparison for inequality
(!=
, <>
) yields true). Comparisons
<
, <=
, >=
, and
>
are meaningful and useful only within the same family; if used
with values from different families then the comparison condition returns false. For
example, a JSON object, number, or boolean is neither greater than nor less than a JSON
array, string, or date.
Tip:
Because json_scalar
returns NULL
by
default for nonscalar input, and because comparison involving a nonscalar JSON value
can be more costly than scalar-with-scalar comparison, a simple manual optimization
when ordering or comparing JSON data is to do so after wrapping it with
json_scalar
, thus effectively pruning nonscalars from the data
to be compared. (More precisely, they are replaced with NULL
, which
is quickly compared.)
For example instead of this:
SELECT data FROM customers c
ORDER BY c.data.revenue;
Use this:
SELECT data FROM customers c
ORDER BY json_scalar(c.data.revenue);
You can use item method type()
to help you identify the type
family of a JSON value, which makes it useful for purposes of comparison or indexing.
However, it provides only a rough guide for this, because it generally reports only on
the SQL data type from which the JSON value was derived, or to which the JSON
value can be mapped.
-
null
type family:type()
returns "null". -
Numeric type family:
type()
returns different type names for different kinds of numeric value:-
"double", for a JSON value (a number) that corresponds to a SQL
BINARY_DOUBLE
value. This includes, for example, values that were derived from an extended object with$numberDouble
. -
"float", for a JSON value (a number) that corresponds to a SQL
BINARY_FLOAT
value. This includes, for example, values that were derived from an extended object with$numberFloat
. -
"number", for a JSON value (a number) that was derived from either (1) a textual JSON number or a string numeral (corresponding to the standard JSON-language number type) or (2) an extended object with
$numberInt
,$numberDecimal
, or$numberLong
.
-
string
type family:type()
returns "string".-
Binary type families:
type()
returns "binary" for both the identifier and nonidentifier binary families, that is, a value that corresponds to a SQLRAW
value.type()
cannot distinguish values of these two families. -
boolean
type family:type()
returns "boolean". -
Date and time point family returns different type names for different kinds:
-
"date" for a value that corresponds to a SQL
DATE
value. This includes, for example, values that were derived from an extended object with$oracleDate
. -
"timestamp" for a value that corresponds to a SQL
TIMESTAMP
value. This includes, for example, values that were derived from an extended object with$oracleTimestamp
. -
"timestamp with time zone" for a value that corresponds to a SQL
TIMESTAMP WITH TIME ZONE
value. This includes, for example, values that were derived from an extended object with$date
or$oracleTimestampTZ
. (A$date
field has a timestamp-with-timezone value, because it allows fractional seconds, and the value is given for Coordinated Universal Time (UTC).)
-
-
yearMonthInterval
type family:type()
returns "yearmonthInterval" for a value that corresponds to a SQLINTERVAL YEAR TO MONTH
value. This includes, for example, values that were derived from an extended object with$intervalYearMonth
. -
daysecondInterval
type family:type()
returns "daysecondInterval" for a value that corresponds to a SQLINTERVAL DAY TO SECOND
value. This includes, for example, values that were derived from an extended object with$intervalDaySecond
. -
vector
type family:type()
returns "vector".Foot 5
Related Topics
See Also:
Data Type Comparison Rules in Oracle Database SQL Language Reference for information about how Oracle Database compares values within each data type
Parent topic: JSON Data Type
2.2.7 Comparison of SQL Values With JSON Data Type Values
When comparing a JSON
-type value with a SQL value of a type
other than JSON
, the same rules apply as when comparing two
JSON
-type values of the same family (e.g. numeric values),
provided that the SQL type corresponds to one of the JSON-language types of that
family.
(Comparison of two JSON
-type values is described in Comparison and Sorting of JSON Data Type Values.)
This is the only useful kind of comparison to be made directly between JSON values and SQL values. All other such comparisons just return false or (exceptionally) raise an error.
For example, this is a useful comparison of a JSON
-type numeric value
with a SQL NUMBER
value, assuming that column DATA
is
of JSON
type:
SELECT c.data FROM customers
ORDER BY c.data.address.zip <= 12345;
That query is in fact equivalent to this one:
SELECT c.data FROM customers
ORDER BY c.data.address.zip.numberOnly() = 12345;
Only numeric JSON values can make such a comparison true. If
the value of field zip
is not numeric, that is, it's not in the number
family, then the comparison returns false. For example, if zip
is the string "314"
then the comparison is false — no type
conversion is performed automatically to convert the string "314"
to
the number 314
.
If the SQL value to be compared is not of a type that corresponds to any JSON-language scalar type then an error is raised.
For example, an SDO_GEOMETRY
value can't be compared
directly with any JSON value:
ERROR at line 1:ORA-00932: expression is of data type
MDSYS.SDO_GEOMETRY, which is incompatible with expected data type JSON
If the JSON value to be compared is nonscalar (an object or array) then the comparison returns false, regardless of the SQL value.
If you are unsure of the JSON-language type of the JSON
-type
value, and you want to convert it (when possible) to a type that's compatible with the
SQL value being compared, you can use a type-conversion item method (see Data-Type Conversion Item Methods).
For example, this query uses item method number()
to
interpret the value of field zip
as would function
json_value
with clause RETURNING NUMBER
, so a
zip
JSON string "314"
is converted to the JSON
number 314
(and the comparison is true).
SELECT data FROM customers
ORDER BY c.data.address.zip.number() = 12345
Tip:
You can use a JSON Data Guide to identify the data types of values inside unknown JSON data.
If the SQL value to be compared is a string that represents a JSON value,
you can convert it to a JSON
-type value using the JSON
constructor. This allows you to compare any JSON values, whether scalar or
nonscalar. (In effect, this is not a case of comparing JSON values with SQL
values.)
For example, this query compares the value of JSON
-type field
zip
with the JSON
-type number
12345
, which the constructor parses from the SQL string
'12345'
:
SELECT c.data FROM customers
ORDER BY c.data.address.zip <= JSON('12345');
And this query compares the value of field address
with the literal JSON
object shown:
SELECT c.data FROM customers
ORDER BY c.data.address <=
JSON('{"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236');
If the JSON value to be compared is textual (not
JSON
type) then it is treated as a SQL string
(VARCHAR2
) for the comparison.
For example, if column textualjson
is of type
VARCHAR2
and the value of field zip is the JSON number
314
, then this comparison is false because
314
is lexicographically
greater than 12345
.
SELECT c.textualjson FROM customers
ORDER BY c.textualjson.address.zip <= '12345';
The JSON number is converted to the SQL string '314'
, and that is
compared with the SQL string '12345'
— a string comparison, not a
number comparison.
Related Topics
Parent topic: JSON Data Type
2.3 Oracle Database Support for JSON
Oracle Database support for JavaScript Object Notation (JSON) is designed to provide the best fit between the worlds of relational storage and querying JSON data, allowing relational and JSON queries to work well together. Oracle SQL/JSON support is closely aligned with the JSON support in the SQL Standard.
Oracle Database supports the JSON format as specified in ECMAScript edition 5.1.
- Support for RFC 8259: JSON Scalars
Starting with Release 21c, Oracle Database supports IETF RFC 8259, which allows a JSON document to contain a JSON scalar value, instead of just an object or array, at top level. This support also means that functions that return JSON data can return scalar JSON values.
See Also:
-
ISO/IEC 9075-2:2023, Information technology—Database language SQL—Part 2: Foundation (SQL/Foundation)
-
ISO/IEC TR 19075-6:2021, Information technology — Guidance for the use of database language SQL, Part 6: Support for JSON
-
Oracle and Standard SQL in Oracle Database SQL Language Reference
-
ECMA 262 and ECMA 262, 5.1 Edition for the ECMAScript Language Specifications (JavaScript)
Parent topic: JSON in Oracle Database
2.3.1 Support for RFC 8259: JSON Scalars
Starting with Release 21c, Oracle Database supports IETF RFC 8259, which allows a JSON document to contain a JSON scalar value, instead of just an object or array, at top level. This support also means that functions that return JSON data can return scalar JSON values.
For this support, database initialization parameter
compatible
must be 20
or greater.
In database releases prior to 21c only IETF RFC 4627 was supported, which allows only a JSON object or array, not a scalar, at the top level of a JSON document. RFC 8259 support includes RFC 4627 support (and RFC 7159 support).
If parameter compatible
is 20
or greater
then JSON data, regardless of how it is stored (as JSON
type or
textually), supports RFC 8259 by default. But for a given JSON column you can use an
is json
check constraint to exclude the insertion of documents
there that have top-level JSON scalars (that is, support only RFC 4627, not RFC 8259),
by specifying the new is json
keywords DISALLOW
SCALARS
.
With parameter compatible
20
or greater you can also use keywords DISALLOW
SCALARS
with SQL/JSON function json_query
(or with a
json_table
column that has json_query
semantics)
to specify that the return value must be a JSON object or array. Without these keywords
a JSON scalar can be returned.
If parameter compatible
is 20
or greater
you can also use SQL data type JSON
, its constructor
JSON
, and SQL/JSON function json_scalar
. If
compatible
is less than 20
then an error is raised
when you try to use them.
If compatible
is 20
or greater you can nevertheless restrict some JSON data to not allow top-level scalars, by using keywords DISALLOW SCALARS
. For example, you can use an is json
check constraint with DISALLOW SCALARS
to prevent the insertion of documents that have a top-level scalar JSON value.
WARNING:
If you change the value of
parameter compatible
to 20
or greater then you
cannot later return it to a lower value.
Parent topic: Oracle Database Support for JSON
Footnote Legend
Footnote 1: KeywordsASCII
,
PRETTY
, ORDERED
, TRUNCATE
,
and EXTENDED
are Oracle extensions; they are not part of the
SQL/JSON standard.Footnote 5: Keep in mind that a scalar vector value is not handled as a scalar. Instead, for comparison purposes it is handled as an array of numbers.