MongoDB query to aggregate documents by a specific property
Yesterday a colleague had a problem on finding a query. His documents looked like the following:
{ id : 1, category : "aaa", answers : [ { gender : "male"} ] }
{ id : 2, category : "bbb" , answers : [ { gender : "female"} ] },
{ id : 3, category : "aaa", answers : [ { gender : "male"} ] }
He needed to query them and get an output similar to the following (format did not matter):
{ "category" : "aaa", "male" : 2 },
{ "category" : "aaa", "female" : 0 },
{ "category" : "bbb", "female" : 1 },
{ "category" : "bbb", "male" : 0 }
His requirments was to fetch the data using only one query using MongoDB aggregation. So after some thought I concluded to the following query:
db.getCollection('question1034')
.aggregate([
{ $unwind : "$answers" },
{ $group: {
_id : {"gender" : "$answers.gender", "category" : "$category"},
count: { $sum: 1 }}
},
{ $project: {
_id :0 ,
"gender": "$_id.gender",
"category": "$_id.category",
count: 1
}}
])
The query returns this result:
{
"count" : 1.0,
"gender" : "female",
"category" : "bbb"
},
{
"count" : 2.0,
"gender" : "male",
"category" : "aaa"
}
I hope this helped you if you stumble upon an issue like this. Greetings from sunny and hot Greece (inside an airconditioned environment)!
Comments