From MongoDB to Oracle: Seamlessly manage your collections with JSON Collection Tables

Published in

Oracle Developers

6 min readJul 30, 2024

Oracle’s multi-workload support is almost as old as the Oracle database. It started with a relational database where Oracle supported transactional and DSS (Decision Support System) workloads long ago. Many things have happened since then, including a re-branding from multi-workload support to a converged database, which is admittedly a much better reflection of the deep integration in the kernel. Over the decades, support for many new data types and workloads has been added.

JSON is one of these workloads, and Oracle Database 23ai has already brought groundbreaking new functionality to this space with JSON Relational Duality. We did not stop and are excited to add JSON collection tables — a new first-class table type for storing MongoDB-compatible collections natively in the database — with Oracle Database 23ai, Release 23.5.

This significantly strengthens Oracle’s capabilities for persistent JSON documents and collections. We will compare and contrast these two techniques later in this blog; for now, just take away that Oracle has you covered with whatever you need.

Your first JSON collection table

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

First, the creation of our first JSON collection table: There is nothing really to show or say other than that we have explicit new keywords for the table creation. Really, it’s as easy as 1–2–3:

SQL> create json collection table Movies;

Table created.

SQL> select collection_name from user_json_collection_tables;
COLLECTION_NAME
--------------------------------------
MOVIES

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

SQL> insert into Movies values ('{"title": "Despicable Me 4", "year": 2024, "comment": "I need to see that one."}');

1 row inserted.

In the same way you just inserted a document, you 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.

Getting JSON documents back, for example, is equally easy as the insert before, both in SQL …

SQL> select * from MOVIES;

DATA
--------------------------------------------------------------------------------------------------------------
{"title":"Despicable Me 4","year":2024,"comment":"I need to see that one.","_id":"668c882d588bc0461cb71439"}


SQL> select m.data.title from MOVIES m where m.data.year.number() = 2024;

TITLE
-----------------
"Despicable Me 4"

.. and in MongoDB API. That was ultimately our goal: creating a MongoDB-compatible collection store in Oracle. And we even dare to update our data:

admin> db.Movies.find()
[
  {
   _id: ObjectId('66a02be6585330e75cd756fd'),
   title: 'Despicable Me 4',
    year: 2024,
    comment: 'I need to see that one.'
  }
]

admin> db.Movies.aggregate([{ $match: { "year": 2024 } } , { $project: { "title": 1 , "_id": 0} }])
[ { title: 'Despicable Me 4' } ]

admin> db.Movies.updateOne({_id: ObjectId('66a02be6585330e75cd756fd')}, { $set: { "comment": "Saw it, was great!"}})
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}

admin> db.Movies.find()
[
  {
    _id: ObjectId('66a02be6585330e75cd756fd'),
    title: 'Despicable Me 4',
    year: 2024,
    comment: 'Saw it, was great!'
  }
]

There are several note-worthy things:

It’s just a collection 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 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. You might have spotted me using different variants of the string ‘Movies’ without quoting, and all worked. That’s standard Oracle for everybody who’s using SQL. However, we also preserve the original case-sensitive input name as-is as a user synonym for the users that inadvertently were using SQL for the collection creation, but are used to MongoDB’s default behavior of case-sensitive names. Depending on the world you’re using and are used to, we got you covered.

There’s obviously way more I could talk about, e.g., creating multi-value any-type indexes using the Mongo API, applying Partitioning for security, etc., but I fear that I am getting close to the average attention span for blog reading, so let me postpone other topics like using advanced database techniques underneath JSON collection tables and briefly touch on JSON collection tables versus Duality Views.

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 migrating from MongoDB to Oracle: 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.

Some applications might stop here. Not all MongoDB applications are large or complex in nature or can benefit from more comprehensive functionality, but some are.

JSON Relational Duality Views allow you to continue working with your JSON documents as before, but your data is stored in relational tables in a normalized way. Decoupling the JSON document representation for processing from the persistent storage in relational form allows you to overcome the limitations of the inherent hierarchical document model. Whether it’s the pain of embedded redundancy of mutable objects in your documents or embedded durable entities that rectify its own hierarchical document view for some parts of the business, this is all very easily and efficiently doable with a relational model — and, therefore, with Duality Views.

Duality Views help you overcome limitations and deficiencies in your hierarchical model without needing a document redesign. It’s an incredibly powerful capability that I encourage you to learn more about. Check out Rajan’s blog, which dives into more detail about JSON Duality View’s key benefits. And of course, you can check out the JSON to Duality Migrator that helps you evolve from JSON collections to Duality Views with underlying relational storage.

I don’t want to get into more details about “when to use what” at this point; that’s for later on this screen. Bottom line: You will ultimately need both relational and JSON data representations at some point: in the same or different applications, to migrate or enhance existing applications, or for net-new development where you don’t want to be forced into one view of the world. This is where Duality Views will shine.

Why not take it for a spin? You can try it for free with Autonomous Database Always Free service. And, check out a couple of LiveLabs that give you hands-on experience with Oracle’s JSON capabilities in the database:

Stay tuned for more, and let us know what you think.

From MongoDB to Oracle: Seamlessly manage your collections with JSON Collection Tables

Your first JSON collection table

Written by Hermann Bär