MongoDB aggregation group by query

Question

I have a mongoDB collection and I want to do an aggregation query.
I am grouping by alert_type field, but I also want the list of those alert_type as a separate field in the output.

Collection looks like this :

db.test.insertMany([ { "output_data": { "alert_type": "UAlert", "overallImpact": { "margin": 0.1, "workingCapital": 3.33 } } }, { "output_data": { "alert_type": "CAlert", "overallImpact": { "margin": 0.1, "workingCapital": 3.33 } } }, { "output_data": { "alert_type": "UAlert", "overallImpact": { "margin": 0.1, "workingCapital": 3.33 } } } ])

Query that I have tried :

db.test.aggregate([ {$group: { "_id": "$output_data.alert_type", "alert_type": { "$first": "$output_data.alert_type" }, "margin": { "$sum": "$output_data.overallImpact.margin" }, "workingCapital": { "$sum": "$output_data.overallImpact.workingCapital" }, "alert_types": { "$addToSet": "$output_data.alert_type" } } }, {$project: {'_id': 0 } } ])

Current output :

{ "alert_type": "UAlert", "margin": 0.2, "workingCapital": 6.66, "alert_types": [ "UAlert" ] } { "alert_type": "CAlert", "margin": 0.1, "workingCapital": 3.33, "alert_types": [ "CAlert" ] }

Required Output :

{ "data": [ { "alert_type": "UAlert", "margin": 0.2, "workingCapital": 6.66, }, { "alert_type": "CAlert", "margin": 0.1, "workingCapital": 3.33, } ], "alert_types": [ "UAlert", "CAlert" ] }

Can anyone help me out with this?

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-05-12 21:09:35Z

You can try below aggregation query :

db.collection.aggregate([ { $group: { "_id": "$output_data.alert_type", alert_type: { $first: "$output_data.alert_type" }, margin: { $sum: "$output_data.overallImpact.margin" }, workingCapital: { $sum: "$output_data.overallImpact.workingCapital" } } }, /** Optional stage - Just to exclude `_id` inside each object of data array from final output */ { $project: { _id: 0 } }, /** Grouping on all docs, For this group stage we will have lesser docs compared to prior Group stage */ { $group: { _id: "", // Group without any condition data: { $push: "$$ROOT" }, // Pushing all docs into an array alert_types: { $addToSet: "$alert_type" } // Adding unique values } }, /** Optional stage - Just to exclude `_id` final output doc */ { $project: { _id: 0 } } ])

Test : mongoplayground

Thank you for this answer @whoami. This is probably, a simpler solution than the accepted one, and this solves my problem too. But I think $facet will provide me with more flexibility in terms of changing the structure of input JSON. Please correct me I'm wrong as I have never used $facet before.
@mukesh.kumar : Only problem with $facet is each stage inside facet will be executed on entire dataset of the collection ! So you need to cautious on that, Which I've found is not needed in your case - I'm not saying it won't work but in terms of performance we're grouping on entire dataset twice, but if it works great then cool :-)
This is a very good point. I was not aware that $facet will be executed for the entire dataset. I will need to check the performance with more documents. Thank you for the information. :)

matthPen · Accepted Answer · 2020-05-12 21:02:13Z

You have to use $facet to achieve this, in a stage you do your grouping stage to get data, and in the other you find all the alert types available.

db.collection.aggregate([ { $facet: { data: [ { $group: { "_id": "$output_data.alert_type", "alert_type": { "$first": "$output_data.alert_type" }, "margin": { "$sum": "$output_data.overallImpact.margin" }, "workingCapital": { "$sum": "$output_data.overallImpact.workingCapital" }, } }, { $project: { "_id": 0 } } ], "alert_types": [ { $group: { _id: null, "names": { "$addToSet": "$output_data.alert_type" } } } ] } }, { $project: { data: 1, alert_types: "$alert_types.names" } } ])

You can test it here

Thank you. I will have to learn more about $facet as this looks a very powerful tool.
Just noticed that this query gives me alert_types as array of array.

Collectives™ on Stack Overflow

MongoDB aggregation group by query

2 Answers 2

3 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Related