This is an updated English version of our German blog posting to make the content available to a broader community.
 
With Oracle Database 23ai, Oracle Database focuses on simplifying applications and development. Javascript Stored Procedures, SQL Domains, Operational Property Graph, In-database Saga and JSON relational duality views are only some examples to help to simplify Database application development. For example JSON Relational Duality, one of the highlights for JSON developers delivers a solution that provides the benefits of both relational tables and JSON documents, without the tradeoffs of either model. Using Duality Views, data is still stored in relational tables in a highly efficient normalized format but is accessed by apps in the form of JSON documents. Developers can thus think in terms of JSON documents for data access while using the highly efficient relational model for data storage, without having to compromise simplicity or efficiency. If you want to get more infromation check out Rajan Priyadarshi’s posting JSON Relational Duality: The Revolutionary Convergence of Document, Object, and Relational Models.

In general JSON in the Oracle Database can be viewed from different perspectives: On the one hand from the SQL database view, of course, but also from the document or collection view with associated APIs and tools. No matter which side you choose, the advantages of Oracle Database as a Converged Database, such as the common use of different data models independent of the choice of workload, are always included. 

In the following sections Beda and I focus on the document-oriented development and the related APIs in Oracle Database. The following graphic gives an overview of the interfaces&APIs and tools of the Oracle Database JSON Framework.
JSON in Oracle Database

image1: Oracle JSON Framework

To illustrate the different technologies Beda built 5 short screen recordings to show the following scenarios:  
Collection operations are demonstrated using different interfaces. That means collections are created, listed, data are inserted, loaded, modified and searched with QBE queries. Important is the usage of different tools like the Oracle tool Database Actions but also MongoDB Shell and Compass.
On the other side the access to JSON with SQL is always possible with Oracle tools. To show the SQL capabilities, data from JSON documents are extracted and prepared – even a transformation of Nested JSON arrays can be easily done in the context of SQL.  In order to make query results available for the MongoDB interface, a collection with the relevant metadata is created using PL/SQL database functionality, so that the transformed SQL result is also available uniformly across all interfaces. 

Before we start, let’s give some short descriptions of the different technologies and tools that were used.

SODA API
Simple Oracle Document Access (SODA) is a NoSQL query language for storing, editing and deleting JSON documents. With this the Oracle database can be used like a ‘Document Database’. In a ‘Document Database’ the data of an application is not mapped to one or more relational tables (with application specific columns). Instead, business objects (e.g. customer) are stored as JSON documents in a so-called ‘Collection’. 
Prerequisites for using SODA is an Oracle database in the cloud or an on-premises installation. SODA is supported from database version 12.1.0.2. Oracle SODA implementations are available in Python, C, Node.js, Java, PL/SQL, Oracle Call Interface and via REST.
Usage scenarios of the SODA interfaces can be found in the following blog post: NoSQL-style API SODA for beginners.

SQLcl
Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer. SQLcl allows interactive or batch execution of SQL and PL/SQL, inline editing, statement completion and custom command macros. Additionally, there is support for APEX, Data Modeler, Datapump, Data Guard Broker, OCI, Cloud Object store and also for SODA. 
To get the latest version of SQLcl, the ZIP file is downloaded from OTN, unzipped, and run with the ‘SQL’ program in the \bin directory. To use the latest version of SQLcl an installation of the Java Runtime Engine (JRE) 11.0.9 is required.
SQLcl Link 

Oracle REST Data Service (ORDS)
Oracle REST Data Services (ORDS) bridges the gap between HTTPS and Oracle Database. ORDS is a Java application available for all databases- on-premises and Oracle Cloud. Database operations can be mapped using GET, POST, PUT and DELETE and the results can be delivered in JSON. ORDS provides a database management REST API, Database Actions (also SQL Developer Web), a PL/SQL gateway, SODA for REST, and the ability to publish RESTful Web Services for interacting with data and stored procedures in Oracle Database. SODA for REST can be accessed from almost any programming language and maps SODA operations to Uniform Resource Locator (URL) patterns.
ORDS Link

Database Actions
Database Actions (aka as SQL Developer Web) is a web application supported by Oracle REST Data Services (ORDS). It provides a worksheet for running queries and scripts, the ability to manage and search the data dictionary, a REST development environment, an interface for the Oracle JSON Document Store (SODA), and more. Database Actions is automatically deployed with Oracle Autonomous Database Cloud Services, but is also available through any self-managed ORDS installation and its Oracle databases. ORDS supports all database versions (from 11gR2 to 21c) in multitenant or classic architecture and can be deployed to one or more Oracle databases. 

Autonomous JSON Database
Autonomous JSON Database (AJD) is aimed at developers who want to create JSON-based applications with a simple API (e.g. with SODA) and do not want to give up the possibilities of SQL. AJD is cheaper than ATP (25% of the price of ATP) – with no functional difference to ATP. However, there is a 20 GB limit for non JSON data. If you want, you can also upgrade the Autonomous JSON Database to an Autonomous Transaction Processing Database if you want to process a lot of relational data and JSON together.

Autonomous JSON Database website

Database API for MongoDB
Database API for MongoDB (also known as MongoAPI) provides a direct connection from MongoDB drivers to the Oracle database. MongoDB developers can continue to use their usual tools and drivers to perform collection operations. Migrations from MongoDB workloads to Oracle have become easy, since usually no or only minor changes to applications are required except for the connection string. A big advantage is that JSON Collections can be used in combination with SQL and thus the functionality of the whole Oracle platform is available. With the latest 22.3.0 release of Oracle REST Data Services (ORDS), the Database API for MongoDB is now available for unmanaged Oracle Database on-premises as well as in the cloud.
It can be installed against

  • Any Oracle database starting with version 21c 
  • Any Autonomous Database, including ADB-Shared, ADB-Dedicated and ADB on Exadata Cloud@Customer, which all run Oracle Database 19c.

More information about the database API can be found in the documentation and in the posting Installing Database API for MongoDB for any Oracle Database from Roger Ford.

SQL
SQL with the extensions of SQL/JSON functions – by the way also standardized in ISO/IEC 9075 -1:2016 – the specialized indexes supporting JSON data structures highly efficient, the JSON data type and special packages operation on JSON Structures open a wide field of additional possibilities to work with JSON documents. Not only the extraction of fragments becomes possible but also the fast performant access via indexes. Also updating JSON documents or generating JSON from relational data is possible. And not to forget JSON operations always work in combination with all other functions of the Oracle database.     

And here is the list of the 5 screen recordings – each with a short explanation and the list of used technologies.

1) Create and List Collections in Oracle and Use MongoDB Tools    
Collections are created and listed in Autonomous JSON Database (AJD). Tools used are on the one hand the Oracle tool Database Actions, but also MongoDB tools like MongoDB Shell and MongoDB Compass. The prerequisite for working with the latter is the use of Oracle Database API for MongoDB. We used Oracle Autonomous Database Shared as a data store. Then the required connection string can be found in Database Actions on the Oracle Database API for MongoDB page. 
Database Actions runs in Oracle REST Data Services and access to it is via schema-based authentication. Database Actions is available out-of-the-box in Autonomous Database Shared and is already enabled for the user ADMIN. 
Note 1: Database Actions is a component of Oracle Rest Data Services (ORDS) and can also be used in on-premises installation.
Note 2: If you intend to use the Oracle Database API in on-premises environment follow the instruction in the ORDS installation and configuration guide.

Technologies used: Autonomous JSON Database, Database Actions, MongoDB Shell. 


2) Upload JSON data and simple QBE operations
In the second part another MongoDB compatible collection is loaded via Database Actions. After that more JSON documents are loaded via MongoDB Compass. Then simple QBE queries will be executed – in MongoDB Shell, MongoDB Compass and Oracle Database Actions.

Technologies used: Autonomous JSON Database, Database Actions, MongoDB Shell, MongoDB Compass 


3) SODA for REST
SODA provides drivers for Python, PL/SQL, C, REST etc. This makes it possible to manage documents in the database in a NoSQL way. With SODA you can create collections, insert documents into collections, update and perform query by example (QBE). SODA for REST uses REST calls to implement Simple Oracle Document Access. 

The previously created collections will now be accessed and managed through REST endpoints. This is done either by using a browser or by running cURL commands on the operating system. 

Technologies used: Autonomous JSON Database, Database Actions, Browser, cURL.


 


4) JSON and SQL
Now the collection will be processed from the SQL point of view. First, the storage of a previously created collection in the database is examined. Then JSON data is queries via SQL using simple dot notation and UPDATEs are performed using a special SQL function. Finally, it is demonstrated how easy it is to extract and transform nested JSON arrays in the SQL context.

Technologies used: Autonomous JSON Database, Database Actions

 

 


5) Query Result as Collection
In the last part we will show how to extend the previously (in part 4) achieved query result using a relational view in such a way that the data can be made available via a new collection. The new collection is then created in the database using SODA for PL/SQL and can be queried using Oracle tools and APIs as well as MongoDB utilities.

Technologie used: Autonomous JSON Database, Database Actions, Browser, MongoDB Shell.


Further reading