How to flatten nested JSON elements in a MongoDB collection

Flattening nested JSON elements in a MongoDB collection can be achieved using code view and expressions. This article will guide you through the process of flattening nested JSON elements using SQL Server and PostgreSQL queries.

{"employees":[  

    {"name":"ClientA", "email":"test@gmail.com"},  

    {"name":"ClientB", "email":"test2@gmail.com"},  

    {"name":"ClientC", "email":"test3@gmail.com"}  

]}  

Steps to Read JSON Data From MongoDB

For SQL Server

1. Create the MongoDB data source.

2. Drag and Drop the table in the data source designer page.

3. Switch to Code View mode using the toggle button

   

4. Now added the query with the required columns

   SELECT [Required field]
   FROM OPENJSON((SELECT [Json data] FROM [Table]), '$.[Json field]')
   WITH (
   [Required Field] NVARCHAR(255) '$.[Required field]'
   )
   

5. Replace the Required columns and table name from Code view mode

6. Now click the save button to save the changes

By following these steps, you can successfully flatten nested JSON elements in a MongoDB collection using MSSQL database queries.

For PostgreSQL

For the PostgreSQL database, You can achieve the same using expressions.

1. Create the MongoDB data source.
2. Drag and Drop the table in the data source designer page.
3. Click the expression button.
   
   
4. Now add the query with the required columns.

   jsonb_array_elements([Json data]::jsonb->'[Json field]')->>'[Required field]'
   

5. Replace the Required columns and JSON field name.
6. Save the expression and save the data source.

By following these steps, you can successfully flatten nested JSON elements in a MongoDB collection using PostgreSQL database queries.

Additional References

• Bold BI - MongoDB connector
• MongoDB Documentation
• SQL Server JSON Functions
• PostgreSQL JSON Functions