Flattening MongoDB Objects in Bold Reports Using Bold ETL
When working with various data sources in Bold Reports, you may encounter objects that need to be flattened to split the data values and create meaningful visualizations. However, flattening multiple fields can make the process more challenging. Bold Reports provides a feature through its ETL (Extract, Transform, Load) process to handle such scenarios. This article outlines the steps to flatten JSON data from MongoDB using Bold Reports ETL MongoDB connector.
Prerequisites
- Bold Reports version 6.1.34 or above.
- MongoDB collection with nested objects.
Steps to Flatten JSON Data
-
Access Bold ETL: Start by opening Bold ETL and creating a new project.
-
Include MongoDB Connector: Add the MongoDB connector template to your project.
-
Establish Connection: Fill in the required details to connect to your MongoDB server.
-
Execute Project: Go to the schedules, select your project, and run it.
-
Generate Data Source: After the project runs successfully, a Bold ETL data source will be created in Bold Reports under the Data Sources page.
-
Design Reports: If your MongoDB JSON data contains arrays, they will be split into separate tables by default. To combine these tables for comprehensive analysis, you can perform a join operation.
- Identify common fields such as dlt_root_id and dlt_parent_id in both tables.
- Use these fields to execute an inner join, allowing you to access data from both tables simultaneously.
Use this created Bold ETL data source to build your reports in Bold Reports.
Conclusion
Flattening JSON data in MongoDB for use in Bold Reports 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.
