I have a collection in mongo with the following data structure:
[{
"_id": {
"$oid": "623316302a97d27acbf20a4b"
},
"region": "East Asia",
"process": "Reppe process with acetylene",
"country": "China",
"site": "Changji",
"company": "Xinjiang Guotai",
"footprint": 12.625501409701098,
"commodity": "methanol",
"productItemCode": "PX",
"productionVolume": -0.0429143833020949
},{
"_id": {
"$oid": "623316392a97d27acbf20a4d"
},
"region": "East Asia",
"process": "acetylene (from coal)",
"country": "China",
"site": "Changji",
"company": "Xinjiang Guotai",
"footprint": 12.625501409701098,
"commodity": "methanol",
"productItemCode": "PX",
"productionVolume": -0.0429143833020949
},{
"_id": {
"$oid": "623443434072b7131ea525d8"
},
"region": "South America",
"process": "steam methane",
"country": "Venezuela",
"site": "Jose",
"company": "metanol",
"footprint": 11.625501409701098,
"commodity": "methanol",
"productItemCode": "PX",
"productionVolume": -0.0534143833020949
},{
"_id": {
"$oid": "6234435c4072b7131ea525da"
},
"region": "South America",
"process": "steam methane",
"country": "Venezuela",
"site": "Jose",
"company": "Metor",
"footprint": 10.625501409701098,
"commodity": "methanol",
"productItemCode": "PX",
"productionVolume": -0.03454143833020949
}]
I am struggling to create an aggregation for the above collection that returns the result in the following form:
{
"commodity": "methanol",
"regions": [
{
"name": "South America",
"processes": [
"steam methane"
]
},
{
"name": "East Asia",
"processes": [
"Reppe process with acetylene",
"acetylene (from coal)"
]
}
],
"productItemCode": "PX"
}
How can I achieve this?
Thanks in advance!