Query MongoDB Data Using SQL With Holistics

Here is a quick 3-step process for querying MongoDB data in Holistics. MongoDB reporting in Holistics requires you to connect your MongoDB database, as well as a reporting SQL database.

If you prefer a more detailed guide, please check the video tutorial below.

Step 1: Connect Holistics to MongoDB to begin building reports. Please ensure that you have also connected a SQL reporting database, for Holistics to import your MongoDB data into. Please have a look at our docs on how to connect your database to Holistics.

Step 2: Configure and schedule the extract, transfrom and load (ETL) of your MongoDB data using the Holistics Data Imports feature, as a table to any of your connected SQL databases. You can use MongoDB dot notation to pull out specific values you want for your table, from a column that has data stored as JSON, for example.

Get value for column whose type is array#

If you have a column whose type is an array like this:

"id": 1,
"status": "ACTIVE",
"currency": "EUR",

And you want to extract only one key-value from it, like status, you can use the syntax: column_name.0.status. What you get for the value above is ACTIVE.

Step 3: Visualize MongoDB data with SQL queries and run joins with other data tables, to build automated reports/charts/metrics/dashboards as required!

For more detailed information, please have a look at our supporting documentation on loading data from MongoDB.

Video Tutorial#

If the instructions above do not help, please refer to this video for the thorough process on how to query data from MongoDB.

One challenge faced when trying to query MongoDB data together with data from other sources is the problem of running SQL on MongoDB, as MongoDB operates as a document store using formats such as JSON. In this video, we’ll show you how easy it is to do that, by connecting Holistics to your MongoDB, automating ETL of MongoDB data to any of your SQL reporting databases, and finally querying and joining your MongoDB data with tables in your reporting database, to build reports and dashboards for your business.