Using Oracle Database 23ai as a Document Store

JSON is one of the most popular data formats, and unsurprisingly, modern, multi-model databases like Oracle offer JSON support. Ideally, the database engine supports both a noSQL-like document API and the relational model for flexibility.

The SQL standard was enhanced in 2016 and 2023 to accommodate SQL/JSON processing. At the risk of sounding like Captain Obvious, it goes without saying that the document API has supported document databases from day one.

Previous articles detailed how to access JSON in Oracle Database; this one concerns some of the new schema objects introduced in release 23ai.

JSON schema objects

Oracle Database 21c introduced a native JSON data type (if you are interested in JSON implementations across many database engines, have a look at this video for an in-depth review). Based on the foundation of this data type, other schema objects can be created. Let’s look at some of these, how they differ, and their purpose.

regular, relational tables and JSON column(s)
JSON Relational Duality Views
JSON Collection Table
JSON Collection View

Apart from the first item on the list, all three other items are new and exclusive to Oracle Database 23ai.

Tables

Creating a table with a JSON column is straightforward. The following example has been taken from the JSON Developer’s Guide:

create table j_purchaseorder (
       id          varchar2(32) not null constraint pk_purchaseorder primary key,
       date_loaded timestamp(6) with time zone,
       po_document json
    );

The table is precisely “just like any other table”. There really isn’t anything special you need to do. You can validate the contents using JSON schema, and any other processing you like to perform. JavaScript, as provided by Multilingual Engine (MLE), is a great option to work with JSON.

JSON Relational Duality Views

Duality Views mark the first radically new schema object in Oracle Database 23ai from the above list. They allow you to combine the efficiency of the relational data model with the ease of use of a document API. You can read more about the use of JSON Relational case and benefits of Duality Views in its Developer’s Guide.

Either create JSON Relational Duality Views using a DDL command (see below) or use Oracle SQL Developer Extension for VSCode for a graphical user interface to design the view.

Here is an example of a JSON Relational Duality view based on the HR sample schema. It lists all departments, along with their employees. Unlike typical document stores that always expose all fields in a collection, it was decided to expose only what was relevant for this hypothetical example:

create or replace json relational duality view emp_dept as
    hr.departments {
        _id:    department_id
        name:   department_name
        employees: hr.employees @insert @update @delete [
            {
                id:         employee_id
                firstname:  first_name
                lastname:   last_name
                email
            }
        ]
    };

A JSON-relational duality view is directly updatable, meaning you can directly insert, update, and delete documents and querying them (the above example requires certain annotations to be writable). Documents supported by a duality view always have a document-identifier field, _id. You must provide it when creating the Duality View. The underlying tables can, of course, be updated, too – changes, either way – are immediately reflected in all clients, relational or NoSQL.

Looking at the Duality View’s definition, you’ll notice that you get the view’s DDL back, but from a database point of view, it contains a single column, DATA, of type JSON.

SQL> ddl emp_dept
    
      CREATE OR REPLACE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW "EMILY"."EMP_DEPT"  AS 
      hr.departments {
        _id:    department_id
        name:   department_name
        employees: hr.employees @insert @update @delete [
            {
                id:         employee_id
                firstName:  first_name
                lastName:   last_name
                email
            }
        ]
    };
    SQL> desc emp_dept
    
    Name    Null?    Type    
    _______ ________ _______ 
    DATA             JSON

You access the JSON documents in the view just like you would any other JSON in Oracle. Use SQL/JSON functions, the simple dot notation, or a NoSQL API – whatever suits your needs best. Here is an example featuring SQL:

select
       -- pretty print the output using json_serialize( ... pretty)
       json_serialize(dv.data pretty) data
    from
        emp_dept dv 
    where
        dv.data."_id" = 10;
    
    DATA                                                
    ___________________________________________________ 
    {
      "_id" : 10,
      "_metadata" :
      {
        "etag" : "6DF460935D50048D6DE64A71D015EBFD",
        "asof" : "00000000003324C8"
      },
      "name" : "Administration",
      "employees" :
      [
        {
          "id" : 200,
          "firstName" : "Jennifer",
          "lastName" : "Whalen",
          "email" : "JWHALEN"
        }
      ]
    }

You can get further details about the JSON Relational Duality View from the dictionary:

select
      collection_name,
      collection_type
    from
      user_json_collections;
    
    COLLECTION_NAME    COLLECTION_TYPE    
    __________________ __________________   
    EMP_DEPT           DUALITY VIEW

Additional dictionary views include:

USER_JSON_DUALITY_VIEWS
USER_JSON_DUALITY_VIEW_LINKS
USER_JSON_DUALITY_VIEW_TABS
USER_JSON_DUALITY_VIEW_TAB_COLS

There is, however, a lot more to say about Duality Views, such as the new Flex Column feature and others. Please head over to the JSON Developer’s Guide and the JSON-Relational Duality Developer’s Guide for details.

JSON Collection Tables

A JSON Collection Table is a regular table with a single column, DATA, of type JSON. It is most likely created by a noSQL API such as the Simple Oracle Document Access (SODA) or the Mongo DB API. There’s a DDL command, too, if you need to use it.

You will most likely see JSON Collection Tables created as a result of this code:

import { MongoClient } from "mongodb";
    
    /**
     * simple function using the MongoDB API under the covers to create a new
     * collection and insert a document into it.
     */
    export async function demo() {
    
       // use the Mongo DB URI, as per the ADB-S "Tool Configuration",
       // or the output of `ords serve...`. Store the DB_URI in an .env
       // file, and pass this to node using --env-file. Make sure _not_
       // to store the .env file in git!
       const client = new MongoClient(
          process.env.DB_URI, {
             tlsAllowInvalidCertificates: true,
          },
       );
       await client.connect();
    
       console.log('successfully connected');
    
       // define which Mongo Database to use
       const db = client.db("EMILY");
    
       // open/create the (new) collection
       const collection = db.collection("myCollection");
    
       // this document is based on the Administration department
       // retrieved earlier from the Duality View
       const adminDepartment = {
          name: "Administration",
          employees: [
             {
                id: 200,
                firstName: "Jennifer",
                lastName: "Whalen",
                email: "JWHALEN",
             },
          ],
       };
    
       // insert the new document
       await collection.insertOne(adminDepartment);
    
       // close the connection
       await client.close();
    }

Provided the MongoDB API has been configured and your schema is ORDS (Oracle REST Data Services) enabled, you can run this JavaScript snippet against Oracle without changes (apart from the database URI in case you used a different document store).

Note that tlsAllowInvalidCertificates is only required in cases when you host your own ORDS instance with self-signed certificates (including in the cloud, and on-premises). Production setups will have a proper certificate chain.

At the end of the function’s execution, a JSON Collection table is created:

select
      collection_name,
      collection_type
    from
      user_json_collections;
    
    COLLECTION_NAME    COLLECTION_TYPE    
    __________________ __________________ 
    myCollection       TABLE              
    EMP_DEPT           DUALITY VIEW

The fact that a table underpins myCollection is irrelevant from the Mongo DB API’s point of view. The Document API deals with documents, not tables; the JSON Collection table is a mere vehicle.

Because it’s ultimately “just a table”, you can use a JSON collection table in most of the ways that you use a regular table (c.f. JSON Developer’s Guide). You can create expression columns (for table partitioning), constraints on data, and many other things. All the good things that happen to tables (replication, backup, etc) also apply to JSON Collection Tables.

Non-Duality JSON Collection Views

Think of a non-Duality Collection View as a regular view accessible to a Document API, and a pragmatic approach to externalising data to a document API. You can define it based on any SQL/JSON function so long as it returns a single JSON object. A non-Duality JSON Collection View is not directly updatable. Using a document API or SQL, you can only query its documents. Any required data changes must be targeted at the view’s underlying tables.

You typically define these views manually, for example:

create json collection view empview as
    select
       json {
          '_id'         : employee_id,
          last_name,             
          'contactinfo' : {email, phone_number},
          hire_date,
          salary
       }
    from
       hr.employees;

You end up with a non-Duality JSON Collection View in the current schema:

COLLECTION_NAME    COLLECTION_TYPE    
    __________________ __________________ 
    myCollection       TABLE              
    EMP_DEPT           DUALITY VIEW       
    EMPVIEW            VIEW

Such a view can be useful if you want to externalise specific data from your schema or a subset of fields from a collection without granting read-write access.

Summary

Oracle Database 23ai offers several new schema objects, including (but not limited to) JSON Relational Duality Views, non-duality JSON Collection views, and JSON Collection tables.

Tables have been part of relational databases since the very beginning. Engines are continuously enhanced, and new data types, such as JSON, are added regularly.

Duality Views go beyond easing frontend/backend integration or supporting migrations from document stores. They deliver a powerful model where relational integrity meets JSON flexibility. Whether your job is to build APIs, or enable low-code platforms like APEX, Duality Views offer a unified, transactional, and performant approach. Anyone migrating from single-purpose document stores to Oracle might also want to consider Duality Views as part of the migration strategy. There is an entire chapter in the JSON-Relational Duality Developer’s Guide dedicated to migrations.

JSON Collection Tables are frequently created by noSQL-style APIs connecting to Oracle, such as the MongoDB API. If you like developing using SODA, its API will also create JSON Collection tables.

Finally, you can pragmatically expose a subset of your schema to a document API using non-Duality JSON Collection Views.

References

All documents mentioned below are part of Oracle Database 23ai documentation.

Using Oracle Database 23ai as a Document Store

JSON schema objects

Tables

JSON Relational Duality Views

JSON Collection Tables

Non-Duality JSON Collection Views

Summary

References

Martin Bach

Real-Time NetSuite Data Synchronization: Enabling Event- Driven Integrations

How to Put Guardrails Around Containerized LLMs on Kubernetes

Using Oracle Database 23ai as a Document Store

JSON schema objects

Tables

JSON Relational Duality Views

JSON Collection Tables

Non-Duality JSON Collection Views

Summary

References

Authors

Martin Bach

Real-Time NetSuite Data Synchronization: Enabling Event- Driven Integrations

How to Put Guardrails Around Containerized LLMs on Kubernetes