Table of contents

Introduction

In this article we would like to illustrate the full support for JSON Relational Duality Views and JSON Collection Views provided by Oracle Database 23ai and ORDS 25.2.

There is a couple of external, non-Oracle sources, which state, that there are some limitations for JSON views in some tools and Oracle Database components, especially for:
•    Oracle API for MongoDB 
•    Simple Oracle Document Access (SODA)
•    Data Dictionary Views
Such information may lead to wrong decisions during design stage and eventually – to build an application with overcomplex architecture, which uses multiple (unneeded) workarounds for non-existing issues. This is especially relevant in times, when the document data model, due to its flexibility, is becoming more and more popular resulting in growing number of document-oriented applications.
However, as we will show in this article, Oracle Database 23ai in all editions, including Autonomous Database and Oracle Database Free and ORDS 25.2 fully supports both types of JSON views, allowing to use them in any document-oriented applications, regardless of which document-oriented APIs such application uses to work on the JSON data.

If you would like to follow the scenario/tutorial provided here, please ensure, that below prerequisites are met: 
•    Oracle Database 23ai  with Oracle API for MongoDB enabled. 
•    SQLcl command line SQL tool
•    mongosh
•    well-known DEPARTMENTS and EMPLOYEES tables, taken from standard demonstration data sets

I. Views Creation

In the first step we will create two views – one JSON-Relational Duality View and one JSON-Collection view:

SQL> create or replace json collection view empview 
  2  as
  3  select json{'_id' : employee_id,
  4              last_name,
  5              hire_date,
  6              salary}
  7* from employees;
JSON collection view EMPVIEW created.

SQL> create or replace json relational duality view deptview as
  2  select json {'_id' : department_id,
  3               'dname'   : department_name,
  4               'location' : location_id}
  5* from departments with insert update delete;
View DEPTVIEW created.

Of course, th Oracle data dictionary contains the information about these views: 

SQL> select *
  2  from user_json_collections
  3* where collection_name in ('DEPTVIEW','EMPVIEW');

OWNER     COLLECTION_NAME    COLLECTION_TYPE    
_________ __________________ __________________ 
ORADEV    DEPTVIEW           DUALITY VIEW       
ORADEV    EMPVIEW            VIEW

II. Simple Oracle Document Access (SODA) support

The views created in the previous step are also supported by SODA.

SQL> soda list
List of collections:
    DEPTVIEW
    …
    EMPVIEW
    …

As EMPVIEW is a read-only structure we cannot modify its content, but we can read it with SODA statements: 

SQL> soda get EMPVIEW
    KEY                        Created On
    03C202                     null
    03C20202                   null
    …
SQL> soda get EMPVIEW -k 03C20202
Key:         03C20202
Content:     {"_id":101,"last_name":"Yang","hire_date":"2015-09-21T00:00:00","salary":17000}
-----------------------------------------
 1 row selected. 

In case of DEPTVIEW, as it is a JSON-Relational Duality view allowing to modify its content, we can also modify its data by executing other SODA CRUD statements:

SQL> soda insert DEPTVIEW {"_id": 800,"dname":"Project Management","location":1400}
JSON document inserted successfully.

SQL> soda remove deptview -f { "_id" : 800 }
Successfully removed 1 document.

III. Oracle API for MongoDB support

Both views are also fully supported by Oracle API for MongoDB, so you can access them from, for example, Mongo Shell command line tool:

oradev> show collections
…
DEPTVIEW        [view]
…
EMPVIEW         [view]

We can also read and modify the data as follows.

oradev> db.DEPTVIEW.insertOne({ _id: 800,
|                         dname:'Project Management',
|                         location:1400})
{ acknowledged: true, insertedId: 800 }

oradev> db.DEPTVIEW.find({ _id : 800 })
[
  {
    _id: 800,
    dname: 'Project Management',
    location: 1400,
    _metadata: {
      etag: Binary.createFromBase64('7bNZ+OzNb6kBNRNPBjGvnw==', 0),
      asof: Binary.createFromBase64('AAApbNsGOMA=', 0)
    }
  }
]

Summary

To sum things up: Oracle 23ai and ORDS 25.2 fully support JSON-Relational Duality and JSON Collection views. That means:
•    It is possible to get information about existing views by querying Data Dictionary views.
•    SODA fully supports all operations on these views (of course – JSON Collection Views are read-only structures, so it is not possible to execute insert/update/delete operations).
•    Oracle API for MongoDB fully supports also JSON-Relational Duality and JSON Collection views.
Also it is always need to remember that only Oracle official documentation ensures accuracy and should be regarded as the exclusive source of truth. All other non- Oracle sources, such as blog postings, videos, etc should always be checked against it.

Additional Resources