Count by multiple fields with MongoDB aggregation

To count by multiple fields, use $facet in MongoDB. The $facet processes multiple aggregation pipelines within a single stage on the same set of input documents. Let us create a collection with documents −

> db.demo721.insertOne(
...    {
...
...       "details1": {
...          "id":101
...
...       },
...       "details2": {
...          "id":101
...       },
...       "details3": {
...          "id":101
...       }
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5eaaebdd43417811278f5887")
}
>
>
> db.demo721.insertOne(
...    {
...
...       "details1": {
...          "id":101
...
...       },
...       "details2": {
...          "id":102
...       },
...       "details3": {
...          "id":102
...       }
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5eaaebe943417811278f5888")
}

Display all documents from a collection with the help of find() method −

> db.demo721.find();

This will produce the following output −

{ "_id" : ObjectId("5eaaebdd43417811278f5887"), "details1" : { "id" : 101 }, "details2" : { "id" : 101 }, "details3" : { "id" : 101 } }
{ "_id" : ObjectId("5eaaebe943417811278f5888"), "details1" : { "id" : 101 }, "details2" : { "id" : 102 }, "details3" : { "id" : 102 } }

Following is the query to count by multiple fields −

> db.demo721.aggregate([
...    {$facet:{
...       ids:[
...          {$group:{ _id:null,
...             d3:{$addToSet: "$details3.id"},
...             d2:{$addToSet:"$details2.id"},
...             d1:{$addToSet:"$details1.id"}}},
...          {$project:{ _id:0,
...             listofall:{$setUnion:["$d1","$d2","$d3"]}}}],
...       d:[{$project:{ _id:0,
...          a1:"$details1.id",
...          a2:"$details2.id",
...          a3:"$details3.id"}}]}},
... {$unwind:"$d"},
... {$unwind:"$ids"},
... {$unwind:"$ids.listofall"},
... {$group:{ _id:"$ids.listofall",
...    details1id:{$sum:{$cond:[{$eq:["$d.a1","$ids.listofall"]},1,0]}},
...    details2id:{$sum:{$cond:[{$eq:["$d.a2","$ids.listofall"]},1,0]}},
...    details3id:{$sum:{$cond:[{$eq:["$d.a3","$ids.listofall"]},1,0]}}}}])

This will produce the following output −

{ "_id" : 102, "details1id" : 0, "details2id" : 1, "details3id" : 1 }
{ "_id" : 101, "details1id" : 2, "details2id" : 1, "details3id" : 1 }