Oracle Database API for MongoDB - Part IV

JSON collection tables are, well, collections of documents stored in Oracle’s optimized binary format OSON (OSON is optimized for query and update efficiency and tends to be smaller than BSON). Just like MongoDB stores documents in BSON. The documents are probably all completely self-contained and self-described, including data redundancy within your documents. So JSON collection tables are probably the most natural choice whenever you are considering to migrate from MongoDB to Oracle platform, move your application as-is, including all your documents, and voilà, you are on a modern platform with so much more functionality than only a document store. Enhance and improve your application with whatever is needed, from transparent data partitioning to adding in-depth analytics with SQL. It’s just the beginning.
 
There’s obviously way more I could talk about, but in this blogpost let’s focus on creating Index and verify the explain plan to see if those indexes were used against the SQL queries and MongoDB command.
 
mdb_test2> // Create index using SQL Commands from MongoShell
 
mdb_test2> db.aggregate([{$sql:" create unique index emp_idx on employees e ( json_value(e.data,'$.name.stringOnly()' error on error)) "}]);
[ { result: 0 } ]
 
mdb_test2> // Create index using MongoDB commands from MongoShell
 
mdb_test2> db.departments.createIndex({deptno:1});
[ { result: 0 } ]
 
Let’s verify the execution plan(s) to see if the Index is being picked up for execution.
 
mdb_test2> // Verify index being used using SQL queries from MongoShell
 
mdb_test2> db.aggregate([{$sql:" explain plan for select * from employees e where e.data.name ='Test' "}]);
[ { result: 0 } ]
mdb_test2> db.aggregate([{$sql:" select plan_table_output from table(dbms_xplan.display) "}]);
[
  { PLAN_TABLE_OUTPUT: 'Plan hash value: 1915469587' },
  { PLAN_TABLE_OUTPUT: ' ' },
  {
    PLAN_TABLE_OUTPUT: '-----------------------------------------------------------------------------------------'
  },
  {
    PLAN_TABLE_OUTPUT: '| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |'
  },
  {
    PLAN_TABLE_OUTPUT: '-----------------------------------------------------------------------------------------'
  },
  {
    PLAN_TABLE_OUTPUT: '|   0 | SELECT STATEMENT            |           |     1 |   174 |     2   (0)| 00:00:01 |'
  },
  {
    PLAN_TABLE_OUTPUT: '|*  1 |  TABLE ACCESS BY INDEX ROWID| employees |     1 |   174 |     2   (0)| 00:00:01 |'
  },
  {
    PLAN_TABLE_OUTPUT: '|*  2 |   INDEX UNIQUE SCAN         | EMP_IDX   |     1 |       |     0   (0)| 00:00:01 |'
  },
  {
    PLAN_TABLE_OUTPUT: '-----------------------------------------------------------------------------------------'
  },
  { PLAN_TABLE_OUTPUT: ' ' },
  {
    PLAN_TABLE_OUTPUT: 'Predicate Information (identified by operation id):'
  },
  {
    PLAN_TABLE_OUTPUT: '---------------------------------------------------'
  },
  { PLAN_TABLE_OUTPUT: ' ' },
  {
    PLAN_TABLE_OUTPUT: `   1 - filter(JSON_VALUE("E"."DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.name' `
  },
  {
    PLAN_TABLE_OUTPUT: "              RETURNING VARCHAR2(4000) NULL ON ERROR TYPE(STRICT) )='Test')"
  },
  {
    PLAN_TABLE_OUTPUT: '   2 - access(JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '
  },
  {
    PLAN_TABLE_OUTPUT: "              '$.name.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR TYPE(LAX) )='Test')"
  },
  { PLAN_TABLE_OUTPUT: ' ' },
  { PLAN_TABLE_OUTPUT: 'Note' },
  { PLAN_TABLE_OUTPUT: '-----' },
  {
    PLAN_TABLE_OUTPUT: '   - dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))'
  }
]
 
mdb_test2>
 
 
mdb_test2> // Verify index being used using MongoDB commands from MongoShell
 
mdb_test2> db.employees.find({name:'Test'}).explain();
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mdb_test2.employees',
    indexFilterSet: false,
    parsedQuery: { name: { '$stringOnly': 'Test' } },
    rewrittenQuery: { name: { '$stringOnly': 'Test' } },
    winningPlan: {
      stage: 'SELECT STATEMENT',
      inputStage: {
        stage: 'TABLE ACCESS',
        options: 'BY INDEX ROWID',
        source: 'employees',
        columns: `"DATA" /*+ LOB_BY_VALUE */ [JSON,8200], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$._id' RETURNING ANY ORA_RAWCOMPARE(2000) NO ARRAY ERROR ON ERROR TYPE(LAX) )[RAW,2000], "ETAG"[RAW,16]`,
        filterType: 'filter',
        filter: `JSON_EXISTS2("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$?(@.name.stringOnly() == $B0)' PASSING :1 AS "B0" FALSE ON ERROR TYPE(STRICT) )=1`,
        path: "$._id'",
        query: "$?(@.name.stringOnly() == $B0)'",
        inputStage: {
          stage: 'INDEX',
          options: 'UNIQUE SCAN',
          source: 'EMP_IDX',
          columns: `"employees".ROWID[ROWID,10], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.name.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR TYPE(LAX) )[VARCHAR2,4000]`,
          filterType: 'access',
          filter: `JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.name.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR TYPE(LAX) )=:1`,
          path: "$.name.stringOnly()'"
        }
      }
    },
    rejectPlans: []
  },
  serverInfo: { host: 'localhost', port: 27017, version: '4.2.14' },
  ok: 1
}
mdb_test2>// Verify index being used using MongoDB commands from MongoShell
 
mdb_test2> db.departments.find({deptno:1}).explain();
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mdb_test2.departments',
    indexFilterSet: false,
    parsedQuery: { deptno: { '$numberOnly': 1 } },
    rewrittenQuery: { deptno: { '$numberOnly': 1 } },
    winningPlan: {
      stage: 'SELECT STATEMENT',
      inputStage: {
        stage: 'TABLE ACCESS',
        options: 'BY INDEX ROWID',
        source: 'DEPARTMENTS',
        columns: `"DATA" /*+ LOB_BY_VALUE */ [JSON,8200], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$._id' RETURNING ANY ORA_RAWCOMPARE(2000) NO ARRAY ERROR ON ERROR TYPE(LAX) )[RAW,2000], "ETAG"[RAW,16]`,
        filterType: 'filter',
        filter: `JSON_EXISTS2("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$?(@.deptno.numberOnly() == $B0)' PASSING :1 AS "B0" FALSE ON ERROR TYPE(STRICT) )=1`,
        path: "$._id'",
        query: "$?(@.deptno.numberOnly() == $B0)'",
        inputStage: {
          stage: 'INDEX',
          options: 'UNIQUE SCAN',
          source: 'DEPT_IDX',
          columns: `"DEPARTMENTS".ROWID[ROWID,10], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.deptno.number()' RETURNING NUMBER ERROR ON ERROR TYPE(LAX) )[NUMBER,22]`,
          filterType: 'access',
          filter: `JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.deptno.number()' RETURNING NUMBER ERROR ON ERROR TYPE(LAX) )=:1`,
          path: "$.deptno.number()'"
        }
      }
    },
    rejectPlans: []
  },
  serverInfo: { host: 'localhost', port: 27017, version: '4.2.14' },
  ok: 1
}
mdb_test2>
 
This confirms that index created either using SQL commands and MongoDB commands over JSON Collection tables are interoperable.
 
In the next blogpost we will see about migrating these collections to Relational tables and the flexibility associated with it.

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...

Oracle Database API for MongoDB - Part II

In this blogpost, we will see about how to configure Oracle Database API for mongo DB using Oracle database 23ai. We will be using Oracle Virtual box image, that comes with ORDS with Oracle Database API for mongo DB enabled by default. The Oracle database version and ORDS version for this demo is listed below.
 
demo@FREEPDB1> select banner_full from v$version;
 
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
 
 
demo@FREEPDB1> select ords.installed_version ;
 
INSTALLED_VERSION
---------------------
24.3.0.r2620924
 
 
let’s create a database user account with minimal privileges required to perform the work and REST enable it (REST enabling schema is needed, for the database to accept the connections from Mongo Shell)
 
sys@FREEPDB1> grant DB_DEVELOPER_ROLE,unlimited tablespace
  2     to mdb_test2 identified by mdb_test2 ;
 
Grant succeeded.
 
sys@FREEPDB1> conn mdb_test2/mdb_test2@freepdb1
Connected.
mdb-test2@FREEPDB1> declare
  2    pragma autonomous_transaction;
  3  begin
  4      ords.enable_schema(p_enabled => TRUE,
  5                         p_schema => 'MDB_TEST2',
  6                         p_url_mapping_type => 'BASE_PATH',
  7                         p_url_mapping_pattern => 'mdb_test2',
  8                         p_auto_rest_auth => FALSE);
  9      commit;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
mdb-test2@FREEPDB1>
 
to connect using the Mongo Shell from command prompt we can use the following URI
 
C:\Users\Rajeshwaran>mongosh --tls --tlsAllowInvalidCertificates "mongodb://mdb_test2:mdb_test2@localhost:27017/mdb_test2?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true"
Current Mongosh Log ID: 672f715fbd9017c3be86b01c
Connecting to:          mongodb://<credentials>@localhost:27017/mdb_test2?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&tls=true&tlsAllowInvalidCertificates=true&appName=mongosh+2.3.2
Using MongoDB:          4.2.14
Using Mongosh:          2.3.2
 
For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/
 
mdb_test2>
 
mdb_test2> show collections;
 
to connect using the MongoDB Compass we use the following URI

 
After the connection got established, we can see something like this.

 
  

After this we can insert data and retrieve information (either from the MongoDB or SQL commands) from the Collection that will be created on MongoDB. In the next blogpost, we will see about creating Json Collections / documents through Oracle database API for Mongo DB and not only access your data using NoSQL approach, but you can also use SQL based approach on the same JSON collections. 

In the next blogpost we will see how to manipulate these collections using SQL and MongoDB commands.

Oracle Database API for MongoDB - Part I

Today modern application uses lot of different types of data, structured, unstructured, geospatial data, or different representation (like graphs) to speed up analytical queries, they use sometimes either use relational or schemeless approach and so on.
 
Very often we have to choose between different products to reach what we really want to get with our applications and fit business needs. Use different vendor product means a bit more effort to full the gaps of all different technologies and the biggest problem arrives when we need to Join the data that are distributed on heterogeneous products and engine, because it is not possible to transform different models of data into a specific model while the application is up and running, or we don’t want to duplicate or move the data.
 
To address these requirements, we need a data platform that can manage all types of data in the same engine, and bring out the value of data when needed, and able to support new types of data when application/business evolves, so to use the same engine which can handle all types of data it is possible with Oracle database today. The Oracle database is an example of a database can manage all types of data with the same engine, and so it makes easier to bring out the value of your data when you need.
 

 

We can use for Instance Oracle database for spatial or graph database, or have another Oracle database for NoSQL based approach with collections and JSON documents, or another database for relational data and exploit it for analytics columnar queries at the same time performs well for transactional system, have an Oracle database on-premise,  cloud@customer, on Oracle cloud Infrastructure (OCI) and have the best PaaS service(Autonomous database) or in a generic hyperscale (like AWS, Azure), with Oracle database you can also have  the data where they reside, for example you can map data (as Avro, ORC, Parquet,  Json, Excel, CSV or any delimited files ) that are on file system or on different Object storage buckets by External tables and realize a modern LakeHouse or a Datamesh solution.
 
To achieve all these we can use one Oracle database for each type of semantics or use different Oracle database for each of them, the decision is upto the Implementation team, because it is not necessary to activate any parameters or licenses, the Oracle database is already ready to manage all this type of data. And if we decide to use Oracle database for each model of data, Oracle implemented a multi-Tenant architecture to facilitate the operations, allowing you to manage many Oracle database like One database (extremely useful when using on-prem or not In PassS model). Also Oracle supports vertical and horizontal scaling and not only using RAC but also using Sharding configuration, and you can also decide to deploy Oracle database on Kubernetes using Oracle operator.
 
In conclusion, with Oracle it is extremely simple to obtain a complete DATA platform for any Type(s) of data.
 
But if you have already developed many applications on other competitor products, such as MongoDB – because at first time, my application only needed to store JSON documents, but now if you need to use ML algorithms on the same data to predict my customers behaviours, (or) I need to speed up the performance for analytics reports and dashboard using a columnar format over JSON keys, (or) if the application need to use JSON, spatial and relational data all together, then how can I solve this ? do we need to rewrite all the application code and re-test it ?
 
Not at all, there is a simpler solution, Oracle rolled out Oracle database API for MongoDB (using Oracle database and ORDS )  that let you run your applications built with MongoDB API on Oracle database and after that, you will be free to use all the potential capabilities that  Oracle database can offer about managing all kind of semantics, performance, availability, scaling and security.  
 
Oracle offers these features not only on Oracle Cloud, but wherever you want and talking about no lock-in products, Oracle also has a great tool called Oracle Golden Gate to feed or synchronize data between different Oracle databases or to other competitors products.
 
In the next blogpost, I’ll show you how to configure and use Oracle Database API for MongoDB.