📢📈 Level-Up your Productivity with JSON in Oracle database 🚀

Published in

db-one

23 min readJun 28, 2021

TL;DR — The Oracle database supports multiple data types (optimized storage and processing) and provides very impressive data management features. What is less known is that usually all of these data management features are made available to every new data type without forgetting this tremendous ability to combine these data types together!

The net result: simpler integration, better productivity for application developers (being frontend, backend, full-stack, application DBA, data engineers, or data scientists…), standardization across application architectures, and lower TCO as well.

History

JSON (JavaScript Object Notation) data type is used more and more to develop applications. Initially used as a data exchange format between browsers and web servers, it has found its way towards databases, including data warehouse workloads.

In 2014, the Oracle database has officially started to support this data type. At the very same time, the Simple Oracle Document Access (SODA) API was made available to manage collections and JSON documents CRUD operations without knowing even a little bit of SQL. Available for JavaScript/TypeScript, Python, Java… you can also invoke the SODA for REST API using tools or frameworks such as Axios, Requests, Spring, Helidon, Micronaut… to ease the integration of the Oracle database with frontends. Last but not least, SQL queries may be invoked using the REST API provided allowing to mimic GraphQL (see example at the end of this post).

Nevertheless, in 2013, Oracle and IBM driving the SQL/JSON standardization committee, numerous improvements have occurred to achieve the SQL:2016 standard. As such, in order to facilitate the adoption of the JSON data format, it has been decided that JSON documents persistence would be done using text columns (VARCHAR, CLOB or BLOB data types). Moreover, because no query language existed for JSON data, “JSON Path Expressions” have been introduced to navigate throughout JSON document hierarchies along with numerous standard JSON functions (JSON_EXISTS, JSON_VALUE, JSON_QUERY, JSON_TABLE…). Additionally, a predicate to validate document fields structure has been defined: IS JSON.

With the new version of the Oracle database made generally available in 2017, JSON data started to be supported by the Column Store: a memory-only area where data are formatted in compressed columns to dramatically speed up analytic or reporting workloads. A new tool also was born: the JSON Data Guide used to discover JSON documents metadata (list of fields, field data types…).

In 2018, user experience was the main driver for new features: simplification of SQL operators syntax, JSON documents generation from relational data, SODA API for C, C++, and PL/SQL, new spatial data types supported for GeoJSON fields (2D, 3D, solids, surfaces…). Also, in 2018, the new Oracle free database XE (eXpress Edition) was made available.

The year 2019 was really interesting since the 19c version becomes the new LTS version (Long Term Support until 2027). This release offered new features such as materialized views on complex JSON documents (embedded JSON objects, arrays…) with the JSON_TABLE operator and a simpler way to update documents using the JSON_MERGEPATCHoperator.

Last year, the new machine learning managed cloud service named Autonomous JSON Database was rolled out: an Oracle database running on Exadata infrastructures (shared or dedicated) hosted in Oracle Cloud Infrastructure (aka OCI). Offering flexibility, security, high availability, and performance, this is the ideal cloud service to use for application development: also available for free (Always Free Tiers gives you 2 databases with 20 GB of data each), less than 2 minutes to provision, automated maintenance, pay by the second, large set of simple data tools accessible via the browser, new features coming every month, and easily integrated to other OCI cloud services (Kubernetes, Functions, Streaming, Analytics…). With this cloud service, this is also the very first time we can “touch” the terrific benefits of this new binary format used to encode JSON documents: OSON format.

We are finally in 2021 and the 21c version will very soon be available to download: Enterprise, Standard, and eXpress editions! This version contains the new SQL data type JSON using this new optimized binary format OSON. This new data type has been integrated with every tool and features of the Oracle database: drivers, Transactional Event Queues, SQL loader, GoldenGate, XStream API, PL/SQL, Application Express (APEX), SQL Developer, SQLcl… Last but not least, a new index type will also be available with this release: the Multi-Value Index allowing to index JSON fields or scalar values (numbers, booleans, strings, dates…) stored inside arrays; and the new JSON_TRANSFORM SQL operator will improve update performance by truly implementing the famous JSON “partial update”.

Why JSON inside databases?

JSON data storage has been made popular by NoSQL databases such as MongoDB. Today, most relational databases can also manage JSON documents for the following reasons:

Schema flexibility

Changes occurring during application development can be implemented quite easily with this JSON data format. Let’s take this JSON document as an example, it represents a person:

{
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": { "street": "21 2nd Street",
        "city":"New York",
        "state":"NY",
        "postalCode": "10021",
        "isBusiness":false },
    "phoneNumbers": [
        { "type": "home",
          "number":"212 555-1234" },
        { "type": "mobile",
          "number": "646 555-4567" } ],
    "lastUpdated": "2019-05-13T13:03:35+0000"
}

We can add the field “middleName” without any impact on the database. For a pure relational database, a new column would have been added via an ALTER TABLE ADD... command. This kind of operation is very easy to perform but very often, such a modification implies coordination with a DBA.

Also, the JSON format allows modifying the cardinality of a field. For example, a person might have several addresses (office, secondary home…) and the “address” field can then become an array to contain as many addresses as required. With a relational database, a new table would be created without forgetting the implementation of the new CRUD operations…

Hence JSON format allows separating the modifications related to the applications from those related to the database schema. This capability becomes even more useful when starting new projects.

No need for normalization

Developers tend to think in terms of Objects and hierarchies: customers, contracts, orders… Representing hierarchy is native to the JSON format. On the other hand, the relational model implies generally several tables and normalization (3rd Normal Form…) which translates into SQL commands that (younger) developers find complex for example because of the join operations needed to query data.

ORMs (Object-Relational Mapping) such as Hibernate can simplify the mapping between objects and tables but it adds another level of complexity often at the cost of the flexibility: do they allow to run native SQL commands?

Finally, the JSON format can also improve performance when embedded object hierarchies can avoid numerous SQL joins later.

Simplified API

The simplicity of APIs attracts numerous developers: executing CRUD operations directly from the programming language without building SQL statements by concatenating strings… These APIs again coming from the NoSQL world weren’t supporting SQL initially but it has evolved a lot now.

Nevertheless, this flexibility, attractive at first glance, does not come without a number of restrictions that may appear later:

Data schema dispersed inside application code

JSON format flexibility doesn’t mean there is no schema, but data schema is implicitly distributed inside the application code. Changes to the schema are no longer centrally “controlled” by the database; instead, the application code may need to be adapted, which does not avoid maintenance, but only postpones it.

This is how the famous ODMs (Object-Document Mappers) were born to allow the creation of objects by referring to a strongly typed schema. We can cite as an example Mongoose (JavaScript) or Morphia (Java). It should be noted that at present, the schema of JSON documents is not yet standardized but it will happen…

The consequence, however, of this dispersion implies potentially significant work the day the application changes language, framework, or database!

Lack of references

The JSON format does not allow for strengthening data integrity as in a relational database because there is no primary key or foreign key constraints.

This can be worked around, for example by using virtual columns with the Oracle database. In a NoSQL database, you don’t have this solution and you must rely on application code to ensure data integrity.

Lack of basic functionality to process JSON data

NoSQL databases often have additional restrictions: transactions, joins, security, resource management, etc…; and it is once again up to the application to compensate for these limitations.

Combining JSON and Relational Data

One of the biggest limitations remains the inherent complexity of combining multiple data models with each other. Thus, even if the JSON data format may be sufficient on its own, no company can work without relational data, for example simply because of the tools necessary for running the company: ERP, data warehouses, accounting system, etc…

Being able to do this efficiently: without reinventing the wheel (re-implementing the joins in JavaScript inside the browser, in the backend or in Functions, etc...), without duplicating data to different systems, in an efficient and secure manner and without making compromises on the integrity of these sometimes critical data for the company is a real challenge.

Oracle’s approach is not to oppose JSON data as an alternative to relational data, but to allow the advantages of both models to be combined as needed. More precisely, it means:

that JSON documents are stored in collections which are ultimately normal tables, but which can be managed very simply using the SODA API without requiring knowledge of SQL,
that relational columns and JSON document fields can be combined as you wish,
that JSON documents can be converted into a relational model,
that relational data can be converted to JSON documents.

Getting Started with an Autonomous Database

First of all, you will need a database. Several options are available:

a Free Oracle 18c XE (eXpress Edition) database — although the release of version 21c XE is imminent (this year),
an Oracle 19c SE (Standard Edition) or EE (Enterprise Edition) database, if possible with release update 19.11 (April 2021),
an Always Free Autonomous Transaction Processing (ATP) or Autonomous JSON Database (AJD) database; the choice between the 2 will depend upon your willingness to upgrade the database to the paid version later if necessary: more storage and CPU power with AJD for a quarter of the price of ATP

The last possibility is by far the most interesting because Oracle Cloud Infrastructure through its “Always Free Cloud Services” offering provides a set of free cloud resources without expiration:

2 Autonomous databases with 20 GB and all the features,
1 NoSQL database with 3 tables and 25 GB of storage per table (available in Phoenix region only as of the time of writing),
2 virtual machines with 1 GB of RAM, 2 volumes of 200 GB of block storage, 10 GB of object storage, and 10 GB of archive type storage,
1 load balancer,
and announced recently, up to 4 OCPUs of ARM virtual machine powered by Oracle Linux with up to 24 GB of RAM!

These resources could be deployed for example in the region of Frankfurt in Germany (while waiting for the opening of the French Oracle cloud Infrastructure region this year). Click here for more details.

For the developers who prefer to work on their computers, the XE version (which does not yet contain all the features presented below) is available here.

To provision your first autonomous database, you can consult this online tutorial; and do not forget to select the type as Autonomous Transaction Processing.

Of course, other tutorials are available on this LiveLabs portal to help you discover many other Oracle cloud services and technologies. Once all the steps have been completed, you can connect via your browser:

Provisioning your Autonomous Transaction Processing database

The first operation to perform: create a user with limited rights to immediately get used to good development practices in terms of security (don’t use superuser access).

Go to the Database Users management panel and click on the “Create User” button. Then fill in the main fields without forgetting to activate the radio button “REST enable”. This will allow you to connect via SQL Developer Web and you will be able to create REST services on your data with a few clicks:

For the roles to be given, you must at least check CONNECT, RESOURCE (containing the SODA_APP role, do not forget it for the XE version), and DWROLE (reserved for Autonomous databases). Click on the “Create User” button.

Now click on the button “Open in new tab” to log on to SQL Developer Web.

The Autonomous database should be created in less than 2 minutes. Of course, there are other ways for provisioning:

Terraform script,
REST API targeting the cloud platform directly,
SDKs for many languages: Python, JavaScript, Java, .Net, Go, Ruby and PL/SQL,
OCI cli, a command-line tool

Simple Oracle Document Access API

As an essential component for application development, the SODA API is available for all modern languages. We’ll go through several examples to illustrate its simplicity of use.

But first a comparative table of JSON terminologies on the left and relational on the right:

As you’ll see in the examples which follow, the SODA API allows you to handle JSON collections and documents without knowing SQL.

Tools

The new SQLcl tool (sqlplus successor) allows you to launch SODA commands very simply inside a terminal. These features are also available through SQL Developer Web, SQL panel:

Simple SODA command example from SQL Developer Web

The SODA commands allow you to perform all operations on the collections:

Creation of a collection (soda create),
List the existing collections (soda list),
Delete a collection (soda drop)

They also allow you to manage JSON documents:

Create,
Retrieve all documents,
Retrieve a document by its ID (unique identifier),
Retrieve documents filtered by a “Query By Example” (QBE): the values of the fields sought are indicated via a JSON document (many operators exist)

Note that the “Data Loading” tab will allow you to load data in your database.

But the most interesting is yet to come: manage your JSON data with your preferred programming language.

Wallet

To be able to connect to your Autonomous database, you will need to download its wallet. A wallet is a zip file containing the certificate to be able to establish a secure connection between your application and the database. To get it, two possibilities, you can:

Download it from the OCI Web console on your Autonomous database portal (the password is the one used during the provisioning phase)

Download your Autonomous database wallet

Retrieve your wallet via the OCI cli (Oracle Cloud Infrastructure Command Line Interpreter from the cloud shell) command-line tool:

Then you’ll need to unzip it and modify the access path inside the sqlnet.ora file in order to use the environment variable TNS_ADMIN which will contain the full path to the folder where the wallet has been unzipped (in this example, my Linux home directory):

$ oci db autonomous-database generate-wallet \
 --autonomous-database-id <ocid1.autonomousdatabase....> \
 --file wallet.zip --password <your password>
$ unzip wallet.zip
$ pwd
$ export TNS_ADMIN=/home/loic_lefev
$ sed -i 's/?\/network\/admin/$TNS_ADMIN/' sqlnet.ora

Once done, you can even use SQLcl as it is already installed inside the OCI Cloud Shell. The following command will connect to your database using the database service name TP (for Transaction Processing, assuming you’ve created a user named jsonuser and your database name is JSONDB):

sql jsonuser/<your password>@jsondb_tp

A database service name is an access endpoint to your database which allocates resources for you once connected. I’ll present with more details this concept later in the “Autonomous Database” section of this post.

Drivers and Oracle Client

In order to be able to connect to an Autonomous database, you will have to retrieve the SODA driver for your language.

For Java, it can be downloaded from GitHub or more simply via Maven:

<dependency>
    <groupId>com.oracle.database.soda</groupId>
    <artifactId>orajsoda</artifactId>
    <version>1.1.7.1</version>
</dependency>

If you are a Python or JavaScript developer, you will need to read the steps below before you can install the packages or modules. Indeed, these languages require an Oracle client to be installed first:

For an XE database, you can follow the getting started guides:

For an Always Free Tier account, you can use the Cloud Shell which already has all the libraries pre-installed for a 19c version of the Autonomous database:

In the event that new versions are available, you can obtain them as follows.

Oracle Client 21.1 for a 21c database:

$ wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip
 $ unzip instantclient-basic-linux.x64-21.1.0.0.0.zip
 $ export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/loic_lefev/instantclient_21_1

JavaScript/TypeScript/Node.js: you can use the npm tool (in order to install the oracledb 5.1.0 package for instance)

$ npm install oracledb

Python, you will need to use pip3 to install the cx_Oracle module:

$ pip3 install --user cx_Oracle

Last remark regarding the environment for the JavaScript/Typescript languages: different versions of Node.js are available in OCI Cloud Shell. You will need to use the nvm tool to choose the desired version: v14.16.1 (LTS), v15.14.0…

Some examples of command lines:

List the LTS versions available only: nvm ls-remote — lts
Install version 15.14.0 by updating the npm package manager: nvm install 15.14.0 — latest-npm

You are now ready to develop in JavaScript/Typescript, Python or Java…

The following examples are identical, they will consist in:

creating a collection named purchase_orders (note that the example for the JavaScript language uses the metadata field to indicate the type of the column storing the JSON documents),
creating an index on the requestor field,
inserting a JSON document and retrieving the unique key generated,
fetching this JSON document and displaying it

These examples will need some environment variables to be defined first:

#!/bin/sh
export TNS_ADMIN=/home/loic_lefev
export ORACLEDB_USER=jsonuser
export ORACLEDB_PASSWORD=<your password>
export ORACLEDB_CONNECTION_STRING=jsondb_tp
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/loic_lefev/instantclient_21_1

SODA for JavaScript

SODA for Python

SODA for Java (with Text Block: JDK 15+)

Other examples are available on GitHub:

Autonomous Databases

We have just seen some examples to access a database using the SODA API for 3 different languages. I’ve used an Autonomous database without explaining what it was. Now is the time to correct this shortcoming!

The Oracle database is one of those software that defies time and which over the decades adapt to the latest trends with a specific goal: to protect your data.

With more than 40 years of existence, it has evolved:

to face all situations, see the best practices: Maximum Availability Architecture (MAA), Maximum Security Architecture (MSA)
to manage extreme use cases (search for new physical particles, government, smart-cities, banks, social networks, SaaS services, etc…)
to integrate the latest advances in hardware (Persistent Memory or PMEM or Non-Volatile RAM, RDMA over Converged Ethernet or RoCE, etc…)
to adapt to different types of data (JSON, Graph, Spatial, Avro, Parquet, ORC, XML, Object, external on object storage or HDFS, etc…)
to adapt to the latest development practices (microservices, transactional queues, blockchain tables, machine learning models, sharding, full-text indexing, spatial processing, etc...)

But the latest and most significant advance is the level of autonomy delivered through managed cloud services. Autonomous Database is an unmatched new class of cloud services: automation pushed to its limits. In March 2021, a new suite of very easy-to-use tools (from your browser) was made available to the delight of developers!

As for a SaaS cloud service, completely native and automated, the benefits are very tangible and you get used to them very quickly (believe me):

you no longer have to patch the database
the data is secure (on disk, on the network)
the backups are automatic (retention of 60 days included in the cost, restoration to the nearest second)
CPU resources adapt in real time to the changing workloads
the price depends on the CPU and disk resources consumed
off, you no longer pay for CPUs, only storage
high availability is managed (failover, disaster recovery, resumption of transactions after failover: with a little configuration)
tuning is automated: automatically created indexes (disabled by default though), automatic column compression for data warehouses (ADW), automatic parallelism of SQL queries (depending on the service name used), materialized views automatically created and maintained, automatic prioritization of I/O resources…
the underlying infrastructure is Exadata, which is the best for Oracle databases: protection of data against corruption with automatic repair, additional optimizations for all types of workload: storage indexes, execution of SQL functions in the storage… in order to free CPUs resources at the database level…
many tools are made available through your browser: SQL query, modeling, dashboard for JSON data, user account manager, creation of REST services, data loading, metadata catalogs, business models, Application Express dashboard (to develop frontends using a LowCode approach), notebooks to create models (Python, R, SQL)
two modes are available: shared and dedicated infrastructure (more flexible in patch management, etc…)
finally, the ability to process multiple data models avoids data duplication, technology duplication, etc…

This cloud service is provided by Oracle Cloud Infrastructure (OCI) in many regions. It is also available in any data center with the deployment model named “Cloud at Customer” or in case of need (security, latency, regulation…) a whole region can be installed inside your data center (OCI Dedicated Region) and operated by Oracle.

The Autonomous Database family is available in 4 models:

ATP service includes all of AJD’s capabilities (and is not limited to 20 GB of relational data), all of AAS’s capabilities, and all of ADW’s capabilities. ADW is focused on data warehouse, analytics and data-science because it allows you to create and manage the entire life cycle of Machine Learning models (including on JSON data). ADW has some preconfigured behaviors related to data management best practices for analytics such as columnar compressed data…

Small peculiarity very useful to know, the database name services. These are stand-alone database access endpoints for which resources have been pre-configured:

Pre-configured Autonomous database service names

So if we target an OLTP workload with a lot of concurrent transactions, we can use the TP service. If you want to generate a report via an SQL query that will process millions or billions of rows, the MEDIUM or HIGH service will be preferable. The number in the “Resource Shares” column indicates the priority of a service to guarantee its access to resources (CPU, I/O, etc.) when one of them is fully used (the higher the better). Thus, a connection to a TPURGENT service will have priority over a connection to a LOW service. Of course, if no contention exists, then a connection could use more resources. Finally, the “Fast Application Notification” and “Transparent Application Continuity” columns indicate respectively whether the events of the shutdown, restart, etc... type are communicated to the client programs and whether the resumption of current and non-committed transactions (COMMIT) is activated for planned maintenance (switchover) or in the event of a crash (failover).

New JSON Data Type

One of the latest additions to the Oracle database is the new JSON data type. Supported from version 21c (from version 19c with Autonomous databases), it uses a new encoding type: OSON. This type of encoding is an improvement over the BSON binary format made popular by MongoDB itself to perform better than JSONB used by PostgreSQL. Based on a tree representation, you can see an example of a simple JSON document encoded in a byte array in OSON format. We can see 3 pointers allowing to navigate by jumps in the hierarchy.

Benefit #1: query performance

The OSON encoding (a tree structure) supports jump navigation. Thus, if you want to retrieve only the value of the name field of the second JSON object of the items array (evaluation of the JSON path: items[1].name) then the database will jump directly to the value “ PC“ without parsing and without analyzing the text representation of the JSON document. For small JSON documents, the payoff may seem modest, but for typical JSON documents that may have hundreds fields and arrays of nested objects, avoiding parsing of unsought fields is much more efficient than systematically parsing text.

Benefit #2: efficient updates

For many applications, you may want to update only some of the fields in a JSON document; this is then referred to as a partial update. For example, you might want to update the id field from “CDEG4” to “CDEG52” in the previous example. The database performs partial updates to the OSON format whenever possible, changing only a subset of the nodes in the JSON structure, even if the new data takes up more space. Indeed, even for complex update operations, replacing the entire document is not necessary. Partial updates with the OSON format thus further improve performance in proportion to the size of the documents. The reduction of I/Os especially concerning the transaction logs makes the application much more scalable.

Benefit #3: reduce disk space

Often, medium to large JSON documents (several KB to several MB) contain nested objects as well as arrays of objects. For example, the name field can be repeated many times. The OSON format uses a dictionary to store the field name only once and thus reduce the size of the documents. On the other hand, compression algorithms can also be implemented.

Benefit #4: native binary encoding for values

The values of the fields in your JSON documents use the native encoding of the Oracle database: number, date, timestamp, binary float/double, year-month intervals, day-time intervals, binary. Consequently, there is no data conversion during the extraction of the values (during the query in particular).

Benefit #5: identical format between client and database

The OSON encoding format is understood by the database and by the SODA drivers. Encoding and decoding can hence be done at the client level. This makes it possible in particular to reduce the consumption of CPU resources on the database server side and reduce the required network bandwidth because of the compression. In addition, these drivers also know how to use this navigation by jump.

Benefit #6: integration with all other functionalities and other data types

This is undoubtedly the most important benefit that this new type of JSON data brings with this new OSON encoding, an integration with all the other functionalities of the database: function based indexes, index for full-text searches, index on GeoJSON for spatial searches, materialized views with JSON_TABLE(), JSON Data-Guide to analyze the structure (the schema) of a JSON collection, parallel SQL queries, memory accelerator in column mode of JSON data to do analytics in real-time, JSON processing at the storage level of Exadata infrastructures… All the functionalities have been updated to take advantage of the OSON format to accelerate the processing of JSON and relational data… and even to combine them with each other!

For more information, you can consult the VLDB 2020 session online: Native JSON Datatype Support: Maturing SQL and NoSQL convergence in Oracle Database.

Combine your JSON data with your Relational data

This is THE strong point of a converged database: simplifying the management of any data. Thus, the ability to be able to manage a data model via your preferred API while being able to use the SQL language to combine different data models between them is very powerful and the benefits are really there:

Less data duplication (among different systems) and therefore savings in storage
Better security: standardization of policies, no weak point in the architecture, limited attack surface (by the absence of duplication and movement of data)
Simplification of the integration process of JSON and relational data: which could be summarized as a join in SQL (and no joins aren’t slow)
Simplification of operations: well known technology with known and unified operating processes, easy to find skills
Common integration features for the different data types

SQL operators for JSON

To query JSON data in SQL, nothing beats the “dot notation”:

Several JSON operators are available to be used in your SQL queries:

One of the most interesting is undoubtedly JSON_TABLE() allowing to “expand” the nested values from an array of objects, for example for the phones field:

JSON_TABLE() SQL/JSON operator unnesting JSON array

Generate JSON data from your Relational data

There are also operators to create JSON objects from relational data:

JSON_OBJECT()
JSON_ARRAY()
JSON_OBJECTAGG()
JSON_ARRAYAGG()

Let’s go for an example:

JSON Data and Relational Data: Fusion!

We will use a SODA collection containing purchase orders and combine its JSON documents with Relational data, here a table containing VAT rates for a few countries. We will then build a second SODA collection which will be based on a view which will perform a join between the purchase orders (JSON) and the VAT rates (Relational). An application can then either directly query the view or using the SODA API, retrieve the JSON documents containing the calculation of the total price of the order enriched with the price including VAT.

Using the SODA collection:

JSON document retrieved from a read-only SODA collection mapped onto a view joining relational data and JSON data from another SODA collection :)

Web Services and REST APIs — Did you say GraphQL?

Cherry on the cake for people using an Autonomous database: REST-enabled SQL service. As its name suggests, it is the possibility of accessing your data in SQL through an endpoint already configured using a REST API.
Remember when creating the JSONUSER user, I indicated to enable the “REST enable” option. This has the effect of giving permission to connect to SQL Developer Web to that user, but it also gives access to other endpoints. So, if I want to run an SQL query, I just need to target the SQL service endpoint that can be derived from the URL to SQL Developer Web:

SQL Developer Web access endpoint: https://<databaseuniquename>.adb.<region>.oraclecloudapps.com/ords/<REST alias>/_sdw/?Nav=worksheet
SQL service endpoint: https://<databaseuniquename>.adb.<region>.oraclecloudapps.com/ords/<REST alias>/_sql/

Example using curl and jq (for a more human readable output):

The SQL query this time returns the contents of the SODA collection named invoice. The return from the curl call is a JSON document containing the result of the SQL query in the items array. Other information is also returned such as the original request or the type of data.

It is this single point of access and this ability to use the Autonomous database or its on-premises version in SQL or PL/SQL via the Oracle REST Data Services (or ORDS) component that brings new perspectives to developers. Did you say GraphQL? Yes, it is very similar!

Converged database

We have just seen a fairly simple example which illustrates the ability to make these two worlds to coexist (Objects/JSON and Relational) very easily while preserving the choice of API to handle the data: SODA and/or SQL.

The range of features that becomes accessible to JSON data is then tremendous:

Partitioned collections
Immutable collections (based on Blockchain tables)
Analytical functions
Column Store (vector processing in columns)
Materialized views refreshed on demand, on commit or during the execution of SQL queries
Additional compression
Full-text indexing
ACID transactions without any limitation!
Web Services / REST APIs
Spatial processing using GeoJSON
…
but also in combination with Graph data, XML data… whether stored in a database or outside: object storage (Oracle, AWS, GCP, Azure…), HDFS, NFS, Open Data…

One Last New Feature

Before closing, I also wanted to share with you a new feature available with the latest version 21c: a new type of index called Multi-Value Index (MVI). The MVI is intended to index the values of a JSON array or the object fields contained in an array.

Example:

Access time is obviously reduced compared to what’s possible in 19c consisting in creating either:

materialized view ( using the JSON_TABLE() operator) with a b-tree index on the desired column
a Full-Text index which may potentially require indexing the entire document

Simpler, more efficient and requiring less disk space, the MVI is very interesting!

Conclusion

We have just seen through examples what a converged database means by starting by discovering the latest features brought to process JSON data regardless of the type of developer (frontend: REST APIs with single access point; backend: SODA API for JavaScript, Java, Python, PL/SQL, REST…; business intelligence, data science: SQL and other capabilities to create models in R or Python — probably a future post). We also saw the power of being able to combine JSON data with Relational data — and this could have been demonstrated with Graph data, XML data, RDF data... We also looked at the strengths of this new native JSON data type and what was behind the acronym OSON. Finally, we discovered this autonomously managed service, the “SaaS for databases” which offers new perspectives for developers whether for the database life cycle (provisioning, patching, backup, etc...), security, performance with dynamic resources allocation, web tools, etc...

This managed and autonomous cloud service is now available free of charge with no time limit with 2 databases that can contain up to 20 GB of data, (with 2 VMs, etc...). Click here for more details. I wish you great discoveries on Oracle LiveLabs and above all a lot of pleasure in developing your applications with this database, which is bringing even more innovations than ever.