Oracle Database API for MongoDB - Part III

Oracle multi-workload support is almost as old as Oracle database, it was started with relation database when Oracle supported Transactional and Decision support system (DSS) workloads, many things have happened since then, including rebranding from multi-workload support to Converged database, which is a much better reflection of deep integration in the Kernel, Over the decades the support for many datatypes and workloads has been added.

 

JSON is one of the workloads and Oracle database 23ai has already brought groundbreaking new functionality in this space with JSON Relational Duality views and the more exciting feature is JSON Collection tables, introduced with Oracle database 23ai (Release 23.5) to support storing MongoDB-compatible collections natively in the database & this significantly strengths Oracle’s capability for persisting JSON documents and collections. 

JSON collection tables are purpose-built Oracle tables to store your MongoDB-compatible collections in the Oracle database. They are fully integrated into the Oracle database, inheriting, and benefiting from all of Oracle’s enterprise mission-critical capabilities: RAC, DataGuard, Security, data pump export/import, you name it. With native SQL support for DDL and DML operations, you can use JSON collection tables in SQL just like regular heap tables, including, for example, Partitioning. Let us walk together through this new and exciting functionality. 

Lets create a couple of collections from Mongo Shell (connecting to Oracle Database 23ai) using Mongo command and SQL statements.

 

mdb_test2> db.getCollectionNames();

[]

mdb_test2> // Create the Employees collections from Mongo

 

mdb_test2> db.createCollection('employees');

{ ok: 1 }

mdb_test2> db.getCollectionNames();

[ 'employees' ]

mdb_test2>

 

mdb_test2> // Create the Departments collections Using SQL statements from Mongo Shell

 

mdb_test2> db.aggregate([{$sql:`create json collection table departments`}]);

[ { result: 0 } ]

mdb_test2> db.getCollectionNames();

[ 'DEPARTMENTS', 'employees' ]

mdb_test2>

Now let’s connect to the Oracle database and verify these JSON Collection tables. 

mdb-test2@FREEPDB1> select * from user_json_collections;

 

OWNER        COLLECTION_NAME    COLLECTION_TYPE

____________ __________________ __________________

MDB_TEST2    DEPARTMENTS        TABLE

MDB_TEST2    employees          TABLE

 

mdb-test2@FREEPDB1>

mdb-test2@FREEPDB1> select * from user_json_collection_tables;

 

OWNER        COLLECTION_NAME    WITH_ETAG

____________ __________________ ____________

MDB_TEST2    DEPARTMENTS        NO

MDB_TEST2    employees          NO

 

mdb-test2@FREEPDB1>

 

those collections created above from Mongo shell was available from the MongoDB Compass also.

 

 The above collections have created a table with one column named DATA of type JSON that’s relevant to you and contains your documents. It’s equally easy to insert documents in such a collection. All you need to do is insert proper JSON documents.

mdb_test2> // Inserts using MongoDB commands 

mdb_test2> db.employees.insertMany(

...     [

...             {"name":"Tom" , "salary":1000, "deptno":1, "title" : "developer"}

...             , {"name":"Kyte" , "salary":2000, "deptno":2, "title" : "Sr.developer"}

...             , {"name":"Hello" , "salary":3000, "deptno":2, "title" : "Architect"}

...     ] );

{

  acknowledged: true,

  insertedIds: {

    '0': ObjectId('673042bcbd9017c3be86b01e'),

    '1': ObjectId('673042bcbd9017c3be86b01f'),

    '2': ObjectId('673042bcbd9017c3be86b020')

  }

}

mdb_test2> // insert using SQL Commands from Mongo-shell.

 

mdb_test2> db.aggregate([{$sql:" insert into departments values ( json{'deptno':1,'department':'Engineering'} )"}]);

[ { result: 1 } ]

mdb_test2> db.aggregate([{$sql:" insert into departments values ( json{'deptno':2,'department':'Technology'} )"}]);

[ { result: 1 } ]

mdb_test2>

 

we can now retrieve the data from database using either SQL commands from Oracle database. 

mdb-test2@FREEPDB1> /* Select query from Oracle Database */

mdb-test2@FREEPDB1> select data from employees;
 
DATA
___________________________________________________________________________________________________
{"name":"Tom","salary":1000,"deptno":1,"title":"developer","_id":"673042bcbd9017c3be86b01e"}
{"name":"Kyte","salary":2000,"deptno":2,"title":"Sr.developer","_id":"673042bcbd9017c3be86b01f"}
{"name":"Hello","salary":3000,"deptno":2,"title":"Architect","_id":"673042bcbd9017c3be86b020"}
 
mdb-test2@FREEPDB1> select data from departments;
 
DATA
___________________________________________________________________________
{"deptno":1,"department":"Engineering","_id":"673042da7ea9d92938600b74"}
{"deptno":2,"department":"Technology","_id":"673042dc7ea9d92938600b77"}
 
mdb-test2@FREEPDB1>

 

or using SQL command or MongoDB commands from Mongo-shell 

mdb_test2> // Data retrieval using SQL Commands from MongoShell

mdb_test2> db.aggregate([{$sql:" select * from departments "}]);
[
  {
    _id: ObjectId('673042da7ea9d92938600b74'),
    deptno: 1,
    department: 'Engineering'
  },
  {
    _id: ObjectId('673042dc7ea9d92938600b77'),
    deptno: 2,
    department: 'Technology'
  }
]
 
mdb_test2> db.aggregate([{$sql:" select * from employees "}]);
[
  {
    _id: ObjectId('673042bcbd9017c3be86b01e'),
    name: 'Tom',
    salary: 1000,
    deptno: 1,
    title: 'developer'
  },
  {
    _id: ObjectId('673042bcbd9017c3be86b01f'),
    name: 'Kyte',
    salary: 2000,
    deptno: 2,
    title: 'Sr.developer'
  },
  {
    _id: ObjectId('673042bcbd9017c3be86b020'),
    name: 'Hello',
    salary: 3000,
    deptno: 2,
    title: 'Architect'
  }
] 

 

mdb_test2> // Data retrieval using Mongo Commands from MongoShell

mdb_test2> db.departments.find();
[
  {
    _id: ObjectId('673042da7ea9d92938600b74'),
    deptno: 1,
    department: 'Engineering'
  },
  {
    _id: ObjectId('673042dc7ea9d92938600b77'),
    deptno: 2,
    department: 'Technology'
  }
]
mdb_test2> db.employees.find();
[
  {
    _id: ObjectId('673042bcbd9017c3be86b01e'),
    name: 'Tom',
    salary: 1000,
    deptno: 1,
    title: 'developer'
  },
  {
    _id: ObjectId('673042bcbd9017c3be86b01f'),
    name: 'Kyte',
    salary: 2000,
    deptno: 2,
    title: 'Sr.developer'
  },
  {
    _id: ObjectId('673042bcbd9017c3be86b020'),
    name: 'Hello',
    salary: 3000,
    deptno: 2,
    title: 'Architect'
  }
]

 

or from MongoDB Compass UI shows this

 

 

 

In the same way we can also update or delete documents and join JSON collection tables with other tables, JSON collection tables, or relational ones. All the SQL/JSON operators work as they do with a “hybrid table” — a relational table with a mix of relational and JSON columns.

 

mdb_test2> // update records in the document using Mongo Commands 

mdb_test2> db.departments.find({deptno:2});
[
  {
    _id: ObjectId('673042dc7ea9d92938600b77'),
    deptno: 2,
    department: 'Technology'
  }
]
mdb_test2> db.departments.updateOne({deptno:2}, {$set: {contact:'hello@gmail.com'}});
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
mdb_test2> db.departments.find({deptno:2});
[
  {
    _id: ObjectId('673042dc7ea9d92938600b77'),
    deptno: 2,
    department: 'Technology',
    contact: 'hello@gmail.com'
  }
]
mdb_test2> 

mdb_test2> // update records in the document using SQL commands from Mongo Shell 

mdb_test2> db.employees.find({name:'Tom'});
[
  {
    _id: ObjectId('673042bcbd9017c3be86b01e'),
    name: 'Tom',
    salary: 1000,
    deptno: 1,
    title: 'developer'
  }
]

mdb_test2> db.aggregate([{$sql:` update employees e
...     set e.data = json_transform( e.data,
...                     set '$.salary' = path '@.salary+1')
...     where e.data.name ='Tom' `}]);
[ { result: 1 } ]

 

mdb_test2> db.employees.find({name:'Tom'});
[
  {
    _id: ObjectId('673042bcbd9017c3be86b01e'),
    name: 'Tom',
    salary: 1001,
    deptno: 1,
    title: 'developer'
  }
]
mdb_test2> 

There are several things to note here.

•  JSON Collections are just collections of documents - We did not specify any column and could still insert into and select from the JSON collection table. From a user experience perspective, it’s just a collection with only documents that happen to live in a column named DATA of type JSON.
• Oracle does the Automatic Key management - The database automatically added an “_id” field to our JSON document at insertion time. It does this all the time when such a field is not contained in a JSON document. This is to preserve full compatibility with both MongoDB and JSON Duality Views. Note also that you can never change an “_id” field. Think Mongo here.
• Case-Sensitive Collection naming – SQL preserves the normal Oracle behavior, meaning that the database capitalizes object names that are not double-quoted at creation time.  That’s standard Oracle for everybody who’s using SQL.

  

In the next blogpost we will see more about the performance from a JSON Collections...