The 5-Minute Guide to Working with ESG Data on MongoDB
Rate this tutorial
MongoDB makes it incredibly easy to work with environmental, social, and corporate governance (ESG) data from multiple providers, analyze that data, and then visualize it.
In this quick guide, we will show you how MongoDB can:
- Move ESG data from different data sources to the document model.
- Easily incorporate new ESG source feeds to the document data model.
- Run advanced, aggregated queries on ESG data.
- Visualize ESG data.
- Manage different data types in a single document.
- Integrate geospatial data.
NOTE: An MSCI account and login is required to download the datasets linked to in this article. Dataset availability is dependent on MSCI product availability.
Figure 1. Importing the data using MongoDB’s Compass GUI
With the MSCI data imported into MongoDB, we can start discovering, querying, and visualizing it.
From MSCI - “ESG AGR uses a quantitative approach to identify risks in the financial reporting practices and accounting governance of publicly listed companies. Metrics contributing to the score include traditional fundamental ratios used to evaluate corporate strength and profitability, as well as forensic ratios.”
- The AGR (Accounting & Governance Risk) Rating consists of four groupings based on the AGR Percentile: Very Aggressive (1-10), Aggressive (11-35), Average (36-85), Conservative (86-100).
- The AGR (Accounting & Governance Risk) Percentile ranges from 1-100, with lower values representing greater risks.
The aggregation pipeline presents a powerful abstraction for working with and analyzing data stored in the MongoDB database. The composability of the aggregation pipeline is one of the keys to its power. The design was actually modeled on the Unix pipeline, which allows developers to string together a series of processes that work together. This helps to simplify their application code by reducing logic, and when applied appropriately, a single aggregation pipeline can replace many queries and their associated network round trip times.
What aggregation stages will we use?
- The $match operator in MongoDB works as a filter. It filters the documents to pass only the documents that match the specified condition(s).
- The $group stage separates documents into groups according to a "group key," which, in this case, is the value of Agr_Rating.
- Additionally, at this stage, we can summarize the total count of those entities.
Combining the first two aggregation stages, we can filter the Issuer_Cntry_Domicile field to be equal to Japan — i.e., ”JP” — and group the AGR ratings.
As a final step, we will also sort the output of the total_count in descending order (hence the -1) and merge the results into another collection in the database of our choice, with the $merge operator.
The result and output collection
'jp_agr_risk_ratings'can be seen below.
Figure 2. Distribution of AGR rating in Japan
Let’s go a step further and group the results for multiple countries. We can add more countries — for instance, Japan and Hong Kong — and then $group and $count the results for them in Figure 3.
Figure 3. $match stage run in MongoDB Compass
Moving back to Charts, we can easily display the results comparing governance risks for Hong Kong and Japan, as shown in Figure 4.
Figure 4. Compared distribution of AGR ratings - Japan vs Hong Kong
From MSCI - “GeoQuant's Country Fundamental Risk Indicators fuses political and computer science to measure and predict political risk. GeoQuant's machine-learning software scrapes the web for large volumes of reputable data, news, and social media content. “
- Health (Health Risk) - Quality of/access to health care, resilience to disease
- IR (International Relations Risk) - Prevalence/likelihood of diplomatic, military, and economic conflict with other countries
- PolViol (Political Violence Risk) - Prevalence/likelihood of civil war, insurgency, terrorism
With MongoDB’s document data model, we can nest documents within a parent document. In addition, we are able to perform query operations over those nested fields.
Imagine a scenario where we have two separate collections of ESG data, and we want to combine information from one collection into another, fetch that data into the result array, and further filter and transform the data.
We can do this using an aggregation pipeline.
Let’s say we want more detailed results for companies located in a particular country — for instance, by combining data from
focus_risk_scoreswith our primary collection:
Figure 5. accounting_governance_risk_agr_ratings collection in MongoDB Compass
Figure 6. focus_risk_scores collection in MongoDB Compass
In order to do that, we use the $lookup stage, which adds a new array field to each input document. It contains the matching documents from the "joined" collection. This is similar to the joins used in relational databases. You may ask, "What is $lookup syntax?"
In our case, we want to join and match the value of Issuer_Cntry_Domicile from the collection accounting_governance_risk_agr_ratings with the value of Country field from the collection focus_risk_scores, as shown in Figure 7.
Figure 7. $lookup stage run in MongoDB Compass
After performing the $lookup operation, we receive the data into the ‘result’ array field.
Imagine that at this point, we decide only to display Issuer_Name and Issuer_Cntry_Domicle from the first collection. We can do so with the $project operator and define the fields that we want to be visible for us in Figure 8.
Figure 8. $project stage run in MongoDB Compass
Additionally, we remove the result_.id field that comes from the original document from the other collection as we do not need it at this stage. Here comes the handy $unset stage.
Figure 9. $unset stage run in MongoDB Compass
With our data now cleaned up and viewable in one collection, we can go further and edit the data set with new custom fields and categories.
Let’s say we would like to set up new fields that categorize Health, IR, and PolViol lists separately.
$filter has the following syntax:
input — An expression that resolves to an array.
as — A name for the variable that represents each individual element of the input array.
cond — An expression that resolves to a boolean value used to determine if an element should be included in the output array. The expression references each element of the input array individually with the variable name specified in as.
In our case, we perform the $filter stage where the input we specify as “$result” array.
Why dollar sign and field name?
This prefixed field name with a dollar sign $ is used in aggregation expressions to access fields in the input documents (the ones from the previous stage and its result field).
Further, we name every individual element from that $result field as “metric”.
To resolve the boolean we define conditional expression, in our case, we want to run an equality match for a particular metric "$$metric.Risk" (following the "$$." syntax that accesses a specific field in the metric object).
And define and filter those elements to the appropriate value (“Health”, “PolViol”, “IR”).
The full query can be seen below in Figure 10.
Figure 10. $set stage and $filter operator run in MongoDB Compass
After we consolidate the fields that are interesting for us, we can remove redundant result array and use $unset operator once again to remove result field.
Figure 11. $unset stage run in MongoDB Compass
The next step is to calculate the average risk of every category (Health, International Relations, Political Violence) between country of origin where Company resides (“Country” field) and other countries (“Primary_Countries” field) with $avg operator within $set stage (as seen in Figure 12).
Figure 12. $set stage run in MongoDB Compass
And display only the companies whose average values are greater than 0, with a simple $match operation Figure 13.
Figure 13. $match stage run in MongoDB Compass
Save the data (merge into) and display the results in the chart.
Once again, we can use the $merge operator to save the result of the aggregation and then visualize it using MongoDB Charts Figure 14.
Figure 14. $merge stage run in MongoDB Compass
Let’s take our data set and create a chart of the Average Political Risk for each company, as displayed in Figure 15.
Figure 15. Average Political Risk per Company in MongoDB Atlas Charts
We can also create Risk Charts per category of risk, as seen in Figure 16.
Figure 16. average international risk per company in MongoDB Atlas Charts
Figure 17. average health risk per company in MongoDB Atlas Charts
Below is a snippet with all the aggregation operators mentioned in Scenario 2:
From MSCI - “Elevate’s Supply Chain ESG Risk Ratings aggregates data from its verified audit database to the country level. The country risk assessment includes an overall score as well as 38 sub-scores organized under labor, health and safety, environment, business ethics, and management systems.”
ESG data processing requires the handling of a variety of structured and unstructured data consisting of financial, non-financial, and even climate-related geographical data. In this final scenario, we will combine data related to environmental scoring — especially wastewater, air, environmental indexes, and geo-locations data — and present them in a geo-spatial format to help business users quickly identify the risks.
Firstly, let’s filter and group the data. Using $match and $group operators, we can filter and group the country per country and province, as shown in Figure 15 and Figure 16.
Figure 18. $match stage run in MongoDB Compass
Figure 19. $group stage run in MongoDB Compass
Now that we have the data broken out by region and country, in this case Vietnam, let’s display the information on a map.
Using the $set operator, we can apply the logic for all regions of the data, as shown in Figure 20.
Figure 20. $set stage and $switch operator run in MongoDB Compass
Figure 21. heatmaps of Environment, Air Emission, Water Waste Indexes in Vietnam in MongoDB Atlas Charts
Below is a snippet with all the aggregation operators mentioned in Scenario 3:
As we can see from the scenarios above, MongoDB’s out-of-the box tools and capabilities — including a powerful aggregation pipeline framework for simple or complex data processing, Charts for data visualization, geospatial data management, and native drivers — can easily and quickly combine different ESG-related resources and produce actionable insights.
MongoDB has a distinct advantage over relational databases when it comes to handling ESG data, negating the need to produce the ORM mapping for each data set.
Import any type of ESG data, model the data to fit your specific use case, and perform tests and analytics on that data with only a few commands.