Oracle Database API for MongoDB - Part VII

One of many nifty enhancement introduced in Oracle 23ai(23.6) is JSON Collection views.
 
A JSON Collection view simply maps JSON documents to underlying relational data – there are two flavours.
 

• Duality View – can be directly updatable, which mean we can directly insert, update, or delete the documents, which in-turn modify the data in the underlying relation table.

• JSON collection view – that is not directly updatable, equivalent to Duality view in a read only format. Using a document API, we can only query its contents, however we can update the supported documents indirectly by modify the data in the underlying relation tables.

 
We will explain with a simple test case.
 
demo@ATP23ai> CREATE OR REPLACE JSON relational duality VIEW EMPVIEW_JDVW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary}
  7  FROM employees with update;
 
View created.
 
demo@ATP23ai>
demo@ATP23ai> CREATE OR REPLACE JSON COLLECTION VIEW EMPVIEW_JCVW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary} data
  7  FROM employees;
 
View created.
 
So we created two views – one Duality view and one JSON Collection view.
 
Updates to the duality view are possible.
 
demo@ATP23ai> update EMPVIEW_JDVW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
 
1 row updated.
 
But not on the JSON Collection view.
 
demo@ATP23ai> update EMPVIEW_JCVW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
where v.data."_id".number() = 100
      *
ERROR at line 13:
ORA-01733: virtual column not allowed here
Help: https://docs.oracle.com/error-help/db/ora-01733/
 
This JSON Collection view sounds like a traditional view.
 
demo@ATP23ai> CREATE OR REPLACE VIEW EMPVIEW_VW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary} data
  7  FROM employees;
 
View created.
 
demo@ATP23ai> update EMPVIEW_VW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
where v.data."_id".number() = 100
      *
ERROR at line 13:
ORA-01733: virtual column not allowed here
Help: https://docs.oracle.com/error-help/db/ora-01733/
 
However, the key difference between JSON collection view and the traditional view was, both JSON collection view and Duality view are accessible from either Mongo-DB commands or SQL command where are traditional view is accessible only from SQL commands.
 
Connection to the database from Mongoshell list the JSON Collection view and Duality view but not the traditional views.
 
C:\Users\Rajeshwaran>mongosh --tls --tlsAllowInvalidCertificates "mongodb://demo@G26BE7C92912CDB-ATP23AI.adb.us-ashburn-1.oraclecloudapps.com:27017/demo?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true"
Enter password: *************
Current Mongosh Log ID: 675702044d99615e2886b01c
Connecting to:          mongodb://<credentials>@G26BE7C92912CDB-ATP23AI.adb.us-ashburn-1.oraclecloudapps.com:27017/demo?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&tls=true&tlsAllowInvalidCertificates=true&appName=mongosh+2.3.2
Using MongoDB:          4.2.14
Using Mongosh:          2.3.2
mongosh 2.3.4 is available for download: https://www.mongodb.com/try/download/shell
 
For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/
 
demo>
 
demo> show collections
EMP_DV
EMP2
EMPLOYEE
EMPVIEW_JCVW
EMPVIEW_JDVW
PERSON_DV
RAW_SRCH_VRTL_QUERY_MOD_TEST
SCHEDULE_DAY_EXTENDED_DV
STUDENTS_DV
TEST_DV1
X
XBASE
 
Also document API access possible on JSON Collection view and Duality view
 
demo> db.EMPVIEW_JCVW.find({"_id":100});
[
  {
    _id: 100,
    last_name: 'King',
    contactInfo: { email: 'SKING', phone_number: '1.515.555.0100' },
    hire_date: ISODate('2013-06-17T00:00:00.000Z'),
    salary: 24000
  }
]
demo>
 
demo> db.EMPVIEW_JDVW.find({"_id":100});
[
  {
    _id: 100,
    last_name: 'King',
    contactInfo: { email: 'SKING', phone_number: '1.515.555.0100' },
    hire_date: ISODate('2013-06-17T00:00:00.000Z'),
    salary: 24000,
    _metadata: {
      etag: Binary.createFromBase64('h/wZRrIKVHohCC7v+umKvg==', 0),
      asof: Binary.createFromBase64('AAAmA4rj1q4=', 0)
    }
  }
]
 
But not on traditional views…
 
demo> db.EMPVIEW_VW.find({"_id":100});
 
demo> db.EMPVIEW_VW.find();