Work with JSON Documents in Autonomous Database

Autonomous AI Database supports JavaScript Object Notation (JSON) data natively in the database. You can use NoSQL-style APIs to develop applications that use JSON document collections without needing to know Structured Query Language (SQL) or how the documents are stored in the database.

Oracle provides two sets of such APIs:

  • Simple Oracle Document Access (SODA)

  • Oracle Database API for MongoDB (also called the MongoDB API)

For example, this SODA for Java code opens a collection of cart documents, carts, then inserts and saves a new document: 

OracleCollection coll = db.openCollection("carts");

// Insert and save a cart document.
OracleDocument doc = db.createDocumentFromString(
                       "{\"customerId\":123, \"items\":[...]}");
coll.save(doc);

And this code finds a document that has a field customerId with a value of 123. 

// Find and retrieve a document having customerID 123.
doc = coll.find().filter("{\"customerId\"}:123").getOne();

Although SODA and the MongoDB API are your main ways of working with JSON documents when developing applications, the data in JSON collections, like other database data, can be accessed from outside an application, including using SQL and database clients such as Java Database Connectivity (JDBC), Oracle Call Interface, and Microsoft .NET Framework. For information about access using SQL see Oracle Tools for Database Access.

Oracle SQL and PL/SQL provide additional ways to use JSON data, beyond what is provided by SODA and the MongoDB API. All Oracle Autonomous Databases fully support the SQL/JSON standard, for example. See Oracle AI Database JSON Developer’s Guide for complete information.

And because collections are backed by ordinary database tables and views, you can take advantage of all sorts of standard Oracle Database features, for use with the content of JSON documents.

With Autonomous AI JSON Database a collection can only contain JSON data. But you can combine (join) JSON data in collections with other data (JSON or non-JSON) that is not in a collection, in arbitrarily complex ways. Then, using features such as Oracle Machine Learning, you can analyze the data and create reports.

SODA and the MongoDB API give you fast, flexible, scalable application development without losing the ability to leverage SQL for analytics, machine learning, and reporting. There are no restrictions on the types of SQL queries that you can express over JSON data.

As a simple example of using SQL with a collection, here is a query that gets the customerId values of all documents in collection carts. (Database column json_document of table carts underlies collection carts.) 

SELECT c.json_document.customerId FROM carts c;

And assuming fields unitPrice and quantity, this next query applies SQL aggregate function sum to the result of applying multiplication operator * to those field values for each document. That is, sum aggregates the products of unit price and quantity across all documents of the collection. (See https://github.com/oracle-quickstart/oci-cloudnative/blob/master/src/carts/sql/examples.sql for more such examples.) 

SELECT sum(c.json_document.unitPrice.number()
           *
           c.json_document.quantity.number())
  FROM carts c;

In Autonomous Database, JSON data can be stored in Oracle's native binary format, OSON. OSON format is always used for JSON data in a collection. For other JSON data, which you store directly in a relational column of type BLOB, Oracle recommends that you specify OSON format for that column using a check constraint of IS JSON FORMAT OSON with CREATE TABLE. For example: 

CREATE TABLE my_table (id NUMBER, json_doc BLOB
  CHECK (json_doc IS JSON FORMAT OSON))

If your database is release 19 or earlier and you use SQL/JSON function json_query to retrieve JSON data stored in OSON format, then by default (no RETURNING clause) native binary JSON values are automatically serialized to textual format (VARCHAR2(4000)).

But if you retrieve an entire JSON document then no such automatic serialization takes place. If you want the document in textual format then use SQL/JSON function json_serialize to serialize it. Here's an example: 

SELECT json_serialize(c.json_document) FROM carts c;

SODA drivers are available for several languages and frameworks: Java, Node.js, Python, C (using Oracle Call Interface), PL/SQL, and REST. SODA for REST maps SODA operations to Uniform Resource Locator (URL) patterns, so it can be used with most programming languages.

Note:

If you use SODA to access collections in Oracle Database 19c, Oracle recommends that you use the instant client for Oracle Database 21c or later, in order to smooth migration to the use of JSON data type when your database is upgraded to release 21 or greater.

To get started with SODA or the MongoDB API, see the following:

For complete information, see the following: