MongoDB Setup

While MongoDB is useful in production, using it for complex analytical purposes is a challenge (For example, JOIN operation using $lookup is a disaster and the limitation of BSON is 16 MB).

To help our customers make use of both the power of MongoDB and SQL databases, Holistics have a built-in ETL tool to pull data from MongoDB to a SQL data warehouse. After that, users can easily query and create dashboards/reports easily using SQL.

Here is a quick 3-step process to query MongoDB data in Holistics. MongoDB reporting in Holistics requires a connection to your MongoDB database, as well as to 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.

If your database is backed with firewall, please check our guide to setup Reverse SSH Tunnels

Step 2: Extract - Transform MongoDB data into SQL Database#

Use Holistics' Data Imports feature to configure and schedule the extract - transform - load (ETL) process from your MongoDB to any of your connected SQL databases

You can use MongoDB's dot notation to pull out specific values you want, from a column that has data stored as JSON, for example.

Get value for column where values are arrays of key-value pairs like below:#

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

[
   {
      "id": 1,
      "status": "ACTIVE",
      "currency": "EUR",
   }
]

And you want to extract only the value of status key, the syntax is: column_name.0.status and the value returned is ACTIVE.

Step 3: SQL Query and Create Reports/Dashboards from MongoDB#

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

Due to the NoSQL nature of MongoDB, here are a few things to take note.

For nested object, you can use MongoDB dot notation to navigate to the wanted value.
When specifying where_clause, you need to specify a JSON object similar to find() method of MongoDB.

For more detailed information on incremental loads for MongoDB, please have a look at our supporting documentation on Incremental Imports for loading data from MongoDB