Run Powerful SQL over MongoDB using Oracle Autonomous Database

November 18, 2022 | 3 minute read
Javier de la Torre
Principal Data Management Cloud Specialist
Text Size 100%:

Have you ever wondered if you could use all the power of SQL against MongoDB? Yes you can! With Oracle Autonomous Database, you can create database links to other databases. This allows you to run SQL queries combining your transactional data with JSON and avoid ETL.

I’m sure that if you are using MongoDB, you will be facing problems like the one highlighted in the following diagram. You start to build a mobile application to enrich your transactional application, but then realize that the data is stored in different places and is difficult to consume and analyze. 

hard to analyze

Let's take a look at the two data sources. First, I have an Autonomous Database which contains transactional information:

transactional data

In MongoDB, I have all the information from the mobile application - including more details about my customers:

mongodb query customers

Even though the data is stored in two different places, Oracle Autonomous Database makes it very easy to analyze data from MongoDB. To set up the external connection, I need to store the MongoDB user credentials. Here, I will store it as “atlas”. Next, I will create a database link pointing to the MongoDB location:

create connection to mongodb

Let’s test it by running a simple SQL query to see the data from MongoDB:

simple SQL query against MongoDB

Now I can take advantage of all the power of SQL. I can join tables across the relational table in Autonomous Database and the MongoDB JSON collection to see which countries bring more revenue to my company:

Cross database query

Conclusion

Accessing MongoDB from Autonomous Database is seamless. And MongoDB is only one of many external sources that Autonomous Database can analyze. This allows you to have simple, centralized access to all your data and correlate information from multiples sources.

You can try this yourself! Go to our Load and Analyze Your Data with Autonomous Database LiveLabs workshop to see how it's done.

Javier de la Torre

Principal Data Management Cloud Specialist


Previous Post

Configure your application mid-tier alongside your database for cross-region disaster recovery

Nilay Panchal | 4 min read

Next Post


Indexing Text in Object Storage

Roger Ford | 9 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider