Flattening JSON Data from MongoDB Using Bold ETL for Dashboard Creation in Bold BI

Overview

This article guides you through flattening JSON data from MongoDB using Bold ETL and creating dashboards in Bold BI. Learn how to connect MongoDB to Bold ETL, transform nested JSON data into usable tables, and perform table joins for deeper insights. The processed data can then be used to design dashboards with powerful visualizations. A sample Student Details Dashboard is showcased to illustrate the process.

Prerequisites

• Bold BI version 7.2 or above.
• MongoDB collection with nested objects.
  
  
  
  Steps to Flatten JSON Data

1. Access Bold ETL: Start by opening Bold ETL and creating a new project.
   

   

2. Include MongoDB Connector: Add the MongoDB connector template to your project.

3. Establish Connection: Fill in the required details to connect to your MongoDB server.
   

   

4. Execute Project: Go to the schedules, select your project, and run it.
   

   

5. Generate Data Source: After the project runs successfully, a Bold ETL data source will be created in Bold BI under Data sources page.
   

   

6. Design Dashboards: If your MongoDB JSON data contains arrays, they will be split into separate tables by default.
   After executing the ETL project, three tables are generated:

• Table 1 Contains only root ID, parent ID, dlt ID etc.
• Table 2 Contains intake and course information.
• Table 3 Contains student details.

To combine these tables for comprehensive analysis, you can perform a join operation. Table 2 and Table 3 can be joined on the common field IntakeID, allowing you to link course and student details based on their associated intake.

After joining the tables, the combined data can be fetched.

Dashboard Creation

We used the attached JSON data file, which contains sample data from MongoDB, to flatten the data through Bold ETL and generate the necessary tables. After processing and joining the data, we created a sample Student Details Dashboard in Bold BI.

Student Details Dashboards - Bold BI

Conclusion

Flattening JSON data in MongoDB for use in Bold BI dashboards is straightforward with the Bold ETL connector. By following the steps outlined above, you can transform your JSON data into a format suitable for visualization and analysis.

Please find the attached Json data file which we used to flatten the data in Bold ETL MongoDB

Additional References

• Bold BI Documentation
• Bold ETL Overview
• MongoDB Documentation