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:

1
2
3


{ 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):

1
2
3
4


{ "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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


{
    "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)!