1 JSON in Oracle Database
Oracle Database supports JavaScript Object Notation (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 in Oracle Database. In particular, it covers how to use SQL and PL/SQL with JSON data.
Note:
Oracle also provides a family of Simple Oracle Document Access (SODA) APIs for access to JSON data stored in the database. SODA is designed for schemaless application development without knowledge of relational database features or languages such as SQL and PL/SQL. It lets you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know how the documents are stored in the database.
There are several implementations of SODA:
-
SODA for REST — Representational state transfer (REST) requests perform collection and document operations, using any language capable of making HTTP calls.
-
SODA for Java — Java classes and interfaces represent databases, collections, and documents.
-
SODA for PL/SQL — PL/SQL object types represent collections and documents.
-
SODA for C — Oracle Call Interface (OCI) handles represent collections and documents.
For information about SODA see Oracle as a Document Store.
- Overview of JSON in Oracle Database
JSON data and XML data can be used in Oracle Database in similar ways. Unlike relational data, both can be stored, indexed, and queried without any need for a schema that defines the data. Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views. - Getting Started Using JSON with Oracle Database
In general, you will perform the following tasks when working with JSON data in Oracle Database: (1) create a JSON column with anis json
check constraint, (2) insert JSON data into the column, and (3) query the JSON data. - 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.
Parent topic: Introduction to JSON Data and Oracle Database
1.1 Overview of JSON in Oracle Database
JSON data and XML data can be used in Oracle Database in similar ways. Unlike relational data, both can be stored, indexed, and queried without any need for a schema that defines the data. Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.
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, including transactions, indexing, declarative querying, and views.
Structured Query Language (SQL) queries 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), Microsoft .NET Framework, and Java Database Connectivity (JDBC).
In Oracle Database, JSON data is stored using the common SQL data types VARCHAR2
, CLOB
, and BLOB
(unlike XML data, which is stored using abstract SQL data type XMLType
). Oracle recommends that you always use an is_json
check constraint to ensure that column values are valid JSON instances (see Example 4-1).
By definition, textual JSON data is encoded using a Unicode encoding, either UTF-8 or UTF-16. You can use textual 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.
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 are using JSON 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.
Use SQL With JSON Data
In SQL, you can access JSON data stored in Oracle Database using either 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 generation functions
json_object
,json_array
,json_objectagg
, andjson_arrayagg
. They gather SQL data to produce JSON data (as a SQL value). -
The same is true of Oracle SQL aggregate function
json_dataguide
, but the JSON data it produces is a data guide, which you can use to discover information about the structure and content of other JSON data in the database. -
SQL/JSON query functions
json_value
,json_query
, andjson_table
, SQL/JSON conditionsjson_exists
,is json
,is not json
, andjson_textcontains
, and Oracle SQL conditionjson_equal
. Except foris json
,is not json
, andjson_equal
, these evaluate SQL/JSON path expressions against JSON data to produce SQL values. -
A dot notation that acts similar to a combination of query functions
json_value
andjson_query
and 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.
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;
Use PL/SQL With JSON Data
You can generally use SQL code, including SQL code that accesses JSON data, within PL/SQL code. You cannot use an empty JSON field name in any SQL code that you use in PL/SQL.
The following SQL functions and conditions are also available as built-in PL/SQL functions: json_value
, json_query
, json_object
, json_array
, and json_exists
. (In PL/SQL, SQL condition json_exists
is a Boolean function.)
Unlike the case for Oracle SQL, which has no BOOLEAN
data type, PL/SQL BOOLEAN
is a valid return data type for SQL/JSON function json_value
.
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 introspect it, modify it, and serialize it back to textual JSON data.
1.2 Getting Started Using JSON with Oracle Database
In general, you will perform the following tasks when working with JSON data in Oracle Database: (1) create a JSON column with an is json
check constraint, (2) insert JSON data into the column, and (3) query the JSON data.
Parent topic: JSON in Oracle Database
1.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.
See Also:
-
ISO/IEC 9075-2:2016, Information technology—Database languages—SQL—Part 2: Foundation (SQL/Foundation)
Parent topic: JSON in Oracle Database