The 5-Minute Guide to Working with ESG Data on MongoDB

1
[{
2
    $match: {
3
        Issuer_Cntry_Domicile: 'JP'
4
    }
5
}, {
6
    $group: {
7
        _id: '$Agr_Rating',
8
        total_count: {
9
            $sum: 1
10
        },
11
        country: {
12
            $first: '$Issuer_Cntry_Domicile'
13
        }
14
    }
15
}, {
16
    $sort: {
17
        total_count: -1
18
    }
19
}, {
20
    $merge: {
21
        into: {
22
            db: 'JP_DB',
23
            coll: 'jp_agr_risk_ratings'
24
        },
25
        on: '_id',
26
        whenMatched: 'merge',
27
        whenNotMatched: 'insert'
28
    }
29
}]

1
{
2
  $lookup:
3
    {
4
      from: <collection to join>,
5
      localField: <field from the input documents>,
6
      foreignField: <field from the documents of the "from" collection>,
7
      as: <output array field>
8
    }
9
}

1
{
2
   $filter:
3
      {
4
         input: <array>,
5
         as: <string>,
6
         cond: <expression>
7
      }
8
}

1
 cond: {
2
            $eq: ["$$metric.Risk", "Health"],
3
          }

1
[
2
  {
3
    $lookup: {
4
      from: "focus_risk_scores",
5
      localField: "Issuer_Cntry_Domicile",
6
      foreignField: "Country",
7
      as: "result",
8
    },
9
  },
10
  {
11
    $project: {
12
      _id: 1,
13
      Issuer_Cntry_Domicile: 1,
14
      result: 1,
15
      Issuer_Name: 1,
16
    },
17
  },
18
  {
19
    $unset: "result._id",
20
  },
21
  {
22
    $set: {
23
      health_risk: {
24
        $filter: {
25
          input: "$result",
26
          as: "metric",
27
          cond: {
28
            $eq: ["$$metric.Risk", "Health"],
29
          },
30
        },
31
      },
32
      political_violence_risk: {
33
        $filter: {
34
          input: "$result",
35
          as: "metric",
36
          cond: {
37
            $eq: ["$$metric.Risk", "PolViol"],
38
          },
39
        },
40
      },
41
      international_relations_risk: {
42
        $filter: {
43
          input: "$result",
44
          as: "metric",
45
          cond: {
46
            $eq: ["$$metric.Risk", "IR"],
47
          },
48
        },
49
      },
50
    },
51
  },
52
  {
53
    $unset: "result",
54
  },
55
  {
56
    $set: {
57
      health_risk_avg: {
58
        $avg: "$health_risk.risk_values",
59
      },
60
      political_risk_avg: {
61
        $avg: "$political_violence_risk.risk_values",
62
      },
63
      international_risk_avg: {
64
        $avg: "$international_relations_risk.risk_values",
65
      },
66
    },
67
  },
68
  {
69
    $match: {
70
      health_risk_avg: {
71
        $gt: 0,
72
      },
73
      political_risk_avg: {
74
        $gt: 0,
75
      },
76
      international_risk_avg: {
77
        $gt: 0,
78
      },
79
    },
80
  },
81
  {
82
    $merge: {
83
      into: {
84
        db: "testDB",
85
        coll: "agr_avg_risks",
86
      },
87
      on: "_id",
88
    },
89
  },
90
]

1
[{
2
 $match: {
3
  Country: {
4
   $ne: 'null'
5
  },
6
  Province: {
7
   $ne: 'All'
8
  }
9
 }
10
}, {
11
 $group: {
12
  _id: {
13
   country: '$Country',
14
   province: '$Province'
15
  },
16
  environment_management: {
17
   $max: '$Environment_Management_Index_Elevate'
18
  },
19
  air_emssion_index: {
20
   $max: '$Air_Emissions_Index_Elevate'
21
  },
22
  water_waste_index: {
23
   $max: '$Waste_Management_Index_Elevate'
24
  }
25
 }
26
}, {
27
 $project: {
28
  country: '$_id.country',
29
  province: '$_id.province',
30
  environment_management: 1,
31
  air_emssion_index: 1,
32
  water_waste_index: 1,
33
  _id: 0
34
 }
35
}, {
36
 $set: {
37
  loc: {
38
   $switch: {
39
    branches: [
40
     {
41
      'case': {
42
       $eq: [
43
        '$province',
44
        'Southeast'
45
       ]
46
      },
47
      then: {
48
       type: 'Point',
49
       coordinates: [
50
        105.8,
51
        21.02
52
       ]
53
      }
54
     },
55
     {
56
      'case': {
57
       $eq: [
58
        '$province',
59
        'North Central Coast'
60
       ]
61
      },
62
      then: {
63
       type: 'Point',
64
       coordinates: [
65
        105.54,
66
        18.2
67
       ]
68
      }
69
     },
70
     {
71
      'case': {
72
       $eq: [
73
        '$province',
74
        'Northeast'
75
       ]
76
      },
77
      then: {
78
       type: 'Point',
79
       coordinates: [
80
        105.51,
81
        21.01
82
       ]
83
      }
84
     },
85
     {
86
      'case': {
87
       $eq: [
88
        '$province',
89
        'Mekong Delta'
90
       ]
91
      },
92
      then: {
93
       type: 'Point',
94
       coordinates: [
95
        105.47,
96
        10.02
97
       ]
98
      }
99
     },
100
     {
101
      'case': {
102
       $eq: [
103
        '$province',
104
        'Central Highlands'
105
       ]
106
      },
107
      then: {
108
       type: 'Point',
109
       coordinates: [
110
        108.3,
111
        12.4
112
       ]
113
      }
114
     },
115
     {
116
      'case': {
117
       $eq: [
118
        '$province',
119
        'Northwest'
120
       ]
121
      },
122
      then: {
123
       type: 'Point',
124
       coordinates: [
125
        103.1,
126
        21.23
127
       ]
128
      }
129
     },
130
     {
131
      'case': {
132
       $eq: [
133
        '$province',
134
        'South Central Coast'
135
       ]
136
      },
137
      then: {
138
       type: 'Point',
139
       coordinates: [
140
        109.14,
141
        13.46
142
       ]
143
      }
144
     },
145
     {
146
      'case': {
147
       $eq: [
148
        '$province',
149
        'Red River Delta'
150
       ]
151
      },
152
      then: {
153
       type: 'Point',
154
       coordinates: [
155
        106.3,
156
        21.11
157
       ]
158
      }
159
     }
160
    ],
161
    'default': null
162
   }
163
  }
164
 }
165
}]