November 20, 2020

Oracle Autonomous JSON Database (AJD) - From Notation to Autonomous

By: Saleh Abed | Technology and Cloud Adoption & Implementation Consultant


What is JSON

Oracle brings the Autonomous Database to JSON | ZDNetJSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language. A simple example, I can present my information using JSON as follows:

var jason = {

            "Full Name" : "Saleh AbdelQader",

            "Age" : "45",

            "Gender" : "M",

            "Address" : "Amman, Jordan"


JSON grew out of a need for stateless, real-time server-to-browser communication protocol without using browser plugins such as Flash or Java applets, the dominant methods used in the early 2000s.

For the history, Douglas Crockford first specified and popularized the JSON format. The acronym originated at State Software, a company co-founded by Crockford and others in March 2001. The co-founders agreed to build a system that used standard browser capabilities and provided an abstraction layer for Web developers to create stateful Web applications that had a persistent duplex connection to a Web server by holding two Hypertext Transfer Protocol (HTTP) connections open and recycling them before standard browser time-outs if no further data were exchanged.

JSON was based on a subset of the JavaScript scripting language and is commonly used with JavaScript, but it is a language-independent data format. Code for parsing and generating JSON data is readily available in many programming languages.


JSON is relatively easy for humans to read and write, and easy for software to parse and generate. It is often used for serializing structured data and exchanging it over a network, typically between a server and web applications.

Since the JSON format is text only, it can easily be sent to and from a server, and used as a data format by any programming language. JSON became the fat-free alternative to XML, because it is light weighted, shorter, and quicker to read & write.

Most important, JSON can be parsed by a standard JavaScript function, no special parser is needed. JavaScript has a built-in function to convert a string, written in JSON format, into native JavaScript objects:


So, if you receive data from a server, in JSON format, you can use it like any other JavaScript object.

JSON Database and JSON in Oracle Database

There's been a growth in the number of database management systems that use JSON to store data. While some of these are relational databases, most are from the NoSQL breed of DBMS, an example on that is Oracle NoSQL Database.

Database management systems that store data as JSON documents are often referred to as document store. They are also known as document-oriented database, aggregate database, or simply document store or document database.

With 12c Rel2, Oracle Database starts supporting JavaScript Object Notation (JSON) data 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 the benefits of relational database features for use with JSON, including transactions, indexing, declarative querying, and views. In addition, SQL is used to join JSON data with relational data, and project JSON data relationally, making it available for relational processes and tools, also, you can query, from within the database, JSON data that is stored outside Oracle Database in an external table.

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, check the following example:

CREATE TABLE j_purchaseorder

  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,

   date_loaded TIMESTAMP (6) WITH TIME ZONE,

   po_document VARCHAR2 (23767)

   CONSTRAINT ensure_json CHECK (po_document IS JSON));

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.

Oracle Autonomous JSON Database

What is Oracle Autonomous JSON Database (AJD)?

Oracle Autonomous JSON Database is a cloud document database service that makes it simple to develop JSON-centric applications. It features simple document APIs, serverless scaling, high performance ACID transactions, comprehensive security, and low pay-per-use pricing. Autonomous JSON Database automates provisioning, configuring, tuning, scaling, patching, encrypting, and repairing of databases, eliminating database management and delivering 99.995% availability.

The following features, in particular, support the development of high-performing, high-security applications:

  1. Automatic database administration. Routine database administration tasks such as patching and taking backups are performed automatically, so you can concentrate on developing your application.
  2. Automatic performance tuning. You spend less time defining and tuning your database. See Autonomous JSON Database Oracle Database 19c Features.
  3. Preconfigured high performance. When you connect to the database with an Oracle client using connection pools you take advantage of high-performance features configured on the database side of your connection. See Code for High Performance.
  4. Predefined, workload-specific database services. Client applications can connect to the database using a connection service that best matches the type of database operations they need. (For most applications that use JSON documents you use the typical connection service for transaction processing, tp.) See Predefined Database Service Names for Autonomous Transaction Processing.

Autonomous JSON Database is specialized for developing NoSQL-style applications that use JavaScript Object Notation (JSON) documents using Simple Oracle Document Access (SODA) APIs. You can also use Oracle Application Express (APEX) for low-code development of dashboards over your JSON data.

But just as for Autonomous Transaction Processing, JSON data stored in a JSON database is also fully accessible using Structured Query Language (SQL), for analytics and interfacing with relational tools. You can promote an Autonomous JSON Database anytime to an Autonomous Transaction Processing database, which lets you store more non-JSON data.

Autonomous JSON Database provides all of the same features as Autonomous Transaction Processing Database, with this important limitation: you can store only up to 20 GB of data other than JSON document collections. There is no storage limit for JSON collections.

Working with JSON Documents In AJD

Let’s see some examples about how to work with JSON data in AJD. For example, this Java code opens a SODA 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\":[...]}");


While the following example Java 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 is your main way of working with JSON documents when developing applications, the data in SODA 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.

SODA gives 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 SODA collection, here is a query that gets the customerId values of all documents in collection carts. (Database column json_document of table carts underlies SODA collection carts.)

SELECT c.json_document.customerId

  FROM carts c;

In Autonomous Database, JSON data is stored in Oracle's native binary format, OSON. But when you retrieve it, such binary values are automatically serialized to textual format. The only exception is if you retrieve an entire JSON document. In that case, you can serialize it to text using SQL/JSON function json_serialize. Look to this SQL statement:

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. For complete information about SODA, see Simple Oracle Document Access (SODA).


Autonomous JSON Database Restrictions

Finally, let’s talk about the restrictions come with ADJ. There are some restrictions you need to take in consideration while working with AJD. I will list some of these restrictions, and for full details check all restrictions within Autonomous JSON Database for Experienced Oracle Database Users:

  1. Restrictions for Database Initialization Parameters
  2. Restrictions for SQL Commands
  3. Restrictions for Data Types
  4. Restrictions for Database PL/SQL Packages
  5. Restrictions for SODA and JSON
  6. Restrictions for Database Features


What makes Autonomous JSON Database better than MongoDB?

Here are three points that make AJD better choice than MongoDB Atlas:

  1. AJD costs $2.74/hr, where MongoDB Atlas $3.95/hr on same configurations, which almost 30% less.
  2. AJD gives 2x throughput consistently across different workload types and collection sizes
  3. AJD has more capabilities than MongoDB Atlas, like: full document indexing, and SQL queries on JSON documents.

For comprehensive comparison between AJD and MongoDB, read this blog Introducing Oracle Autonomous JSON Database for application developers.


What use cases Autonomous JSON Database is used for?

Autonomous JSON Database use cases are:

  1. Mobile applications: Build rich, immersive experiences for mobile apps.
  2. Applications with dynamic personalized experiences: Deliver engaging personalized customer experiences and provide real-time recommendations on multiple digital properties.
  3. Content and catalog management: Develop content management and product catalog services that handle a wide variety of attributes and metadata.
  4. Integrated IoT applications: Create integrated applications for clickstream, monitoring, logging, and sensor systems to process billions of inserts and retrievals a second, generating new insights without compromising data accuracy.
  5. Digital payment applications: Build payment processing services with a consistent view of data and data integrity without impacting application performance.


Where to Start?

Start with your Oracle Cloud Free account, you can have the 30-day Free Trial, or the Always Free account.

Then after signing up for free account, start with this Tutorial “Autonomous JSON Database” to create your AJD, and build your first application. Also, allocate sometime to watch “Getting Started with JSON Document Collections in the Autonomous JSON Database” video.




Technology and Cloud Adoption & Implementation Consultant

Oracle EMEA A&C Technology and Cloud Adoption & Implementation Consultant

23 years of experience in IT in various capacities and organizations with substantial knowledge and understanding of ICT business needs, ranging from DBA, implementation, project management, technical management, and account management.

My role is aiming to simplify and enhance the partner experience throughout their enablement, adoption, implementation and cloud usage. Working closely with partners supporting them in accelerating workload consumption on cloud, driving PoC & deployment projects.

Having a specific focus on ISVs, being closely engage with ISVs, conduct ISV solution clinic evaluations and support the provisioning of ISV solutions on Oracle Cloud Infrastructure Saudi Data Centers.

More about Saleh Abed