Overview of JSON in 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.

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 where JSON type didn't exist (releases prior to 21c).
For use with a database where initialization parameter compatible needs to be less than 20 for some reason, so JSON 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 or BLOB.
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 convert JSON-type values (returned from queries, for example) to textual JSON (VARCHAR2(4000), by default).

If you use function json_serialize with keyword EXTENDED, 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, a BINARY_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 a BINARY_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 a RETURNING clause that returns a SQL type that corresponds to an Oracle-specific JSON scalar type. See RETURNING Clause for SQL Functions.

Related Topics

Parent topic: Overview of JSON in Oracle Database

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 a NULL value for type VARCHAR2, one for type NUMBER, …, and one for type JSON (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 is NULL, 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 `NULL`	`NULL`	`NULL`	`NULL`	`NULL`
Case 2: targeted data is missing	`{}`	`NULL`	`NULL`	`NULL`
Case 3, with `JSON` type support: JSON `null` value selected	`{"a":null}`	With `JSON` type input: `JSON` type `null` value (the same thing that `json_scalar('null')` returns) Otherwise: `NULL`	`NULL`	With either `JSON` type input or `RETURNING JSON`: `JSON` type `null` value (same thing that `json_scalar('null')` returns) Otherwise: the textual JSON `null` value of the `RETURNING` or input type (same thing that `json_serialize(json_scalar('null'))` returns)
Case 3, without `JSON` type support: JSON `null` value selected	`{"a":null}`	`NULL`	`NULL`	`NULL`

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, and json_table.

These evaluate SQL/JSON path expressions against JSON data to produce SQL values.
Oracle SQL condition json_textcontains and SQL/JSON conditions json_exists, is json, and is not json.

Condition json_exists checks for the existence of given JSON data; json_textcontains provides full-text querying of JSON data; and is json and is not json check whether given JSON data is well-formed.

json_exists and json_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 and json_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, and json_arrayagg.

These gather SQL data to produce JSON object and array data (as a SQL value).
SQL/JSON functions json_serialize and json_scalar, and Oracle SQL condition json_equal.

Function json_serialize returns a textual representation of JSON data; json_scalar returns a JSON type scalar value that corresponds to a given SQL scalar value; and json_equal tests whether two JSON values are the same.
JSON data type constructor JSON.

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 data of table j_purchaseorder (aliased here as po). It obtains all purchase-order requestors (JSON field Requestor).

SELECT po.data.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, and json_equal are Boolean functions.
json_value can return a BOOLEAN value. json_table columns with json_value semantics can be of type BOOLEAN.
json_scalar can accept a BOOLEAN value as argument, in which case it returns a Boolean JSON type instance (true or false).
json_object, json_objectagg, json_array, and json_arrayagg can generate JSON objects and arrays that contain values true and false, corresponding to PL/SQL values TRUE and FALSE.

Similarly, if you pass SQL TRUE or FALSE to json_transform then these are mapped to JSON true and false if included in the transformation result.
json_exists and json_transform can use BOOLEAN 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 returns 1 for valid and 0 for invalid (invalid data can optionally raise an error). The procedure returns TRUE for valid and FALSE for invalid as the value of an OUT 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.

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 of JSON 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.