Mongodb aggregation, group and then count

Question

I have jobs and candidates have applied on each jobs. Here is sample of just one job.

  {
     "_id": ObjectId("5a9e625c748a563a80e5d86a"),
     "job_title": "Software developer",
     "companyId": ObjectId("5a9e625c748a563a80e5889ds")
     "applications" : [ 
            {
                "applied" : true,
                "shortlisted" : false,
                "interviewed" : false,
                "offered" : false,
                "hired" : false,
                "rejected" : false
            },
            {
                "applied" : true,
                "shortlisted" : true,
                "interviewed" : false,
                "offered" : false,
                "hired" : false,
                "rejected" : false
            },
            {
                "applied" : true,
                "shortlisted" : true,
                "interviewed" : true,
                "offered" : false,
                "hired" : false,
                "rejected" : false
            },
            {
                "applied" : true,
                "shortlisted" : true,
                "interviewed" : true,
                "offered" : false,
                "hired" : false,
                "rejected" : false
            },
            {
                "applied" : true,
                "shortlisted" : true,
                "interviewed" : false,
                "offered" : false,
                "hired" : false,
                "rejected" : true
            },

        ]

    }

A candidate can go through stages like Applied, Shortlisted, interviewed .....

So What I want to get is total number of applicants are in each stages.

Result something like this :

{
  applied: 1,
  shortlisted: 1,
  interviewed: 2,
  offered: 0,
  hired: 0,
  rejected: 1
}

Here is how i tried to write query

  Job.aggregate([
    {
      $match: {
        companyId : mongoose.Types.ObjectId(req.params.id),
        active: true
      }
    },
    {
        $unwind: "$applications"
    },
    {
      $replaceRoot : {
        newRoot : "$applications"
      }
    },
    {
      $group: {
          _id : {
            applied : "$applied",
            shortlisted : "$shortlisted",
            interviewed : "$interviewed",
            offered : "$offered",
            hired : "$hired",
            rejected : "$rejected"
          },
          applied: {$sum: "_id"},
          // shortlisted: {$sum: "shortlisted"},
          // interviewed: {$sum: "interviewed"},
          // offered: {$sum: "offered"},
          // hired: {$sum: "hired"},
          // rejected: {$sum: "rejected"},
      }
    }
  ])

This is for total counts irrespective of jobs. I also would want to know count of applicants in each stages against of a job.

Edited :

{
            "applied" : true,
            "shortlisted" : false,
            "interviewed" : false,
            "offered" : false,
            "hired" : false,
            "rejected" : false,
            "rejectedComment" : "",
            "notes" : [],
            "documents" : [],
            "assessment" : [],
            "interviewSchedule" : [],
            "references" : [],
            "_id" : ObjectId("5a9fc2b6e80e10405ce612d6"),
            "talentId" : ObjectId("5a8e7da63925a10b68ea08dd"),
            "createdAt" : ISODate("2018-03-07T10:45:10.675Z"),
            "updatedAt" : ISODate("2018-03-07T10:45:10.675Z")
        }

updated

{
      $match: {
        companyId : mongoose.Types.ObjectId(req.params.id),
        active: true
      }
    },
    {
        $unwind: "$applications"
    },
    {
      $replaceRoot : {
        newRoot : "$applications"
      }
    },
    {
      $group: {
          _id : '',
          // "applied":{"$sum":{"$cond":{$and:[{$eq:["$applications", {applied: true, shortlisted: false , interviewed: false, offered: false, hired: false, rejected: false}]},1,0]}}},
          "applied": {"$sum": {"$cond": {$and: [{$eq:["$applications", {applied: true, shortlisted: false , interviewed: false, offered: false, hired: false, rejected: false}]},1,0]}]}}
          //   shortlisted : {"$applied.shortlisted" : false},
          //   interviewed : {"$applied.interviewed" : false},
          //   offered : {"$applied.offered" : false},
          //   hired : {"$applied.hired" : false},
          // "rejected":{"$sum":{"$cond":[{$eq:["$applications", {rejected: true}]},1,0]}},
          // },
          // "applied": {
          //   "$sum": 1
          // }
        // }
      }
    }

Answer 1

You can try $cond operator inside the $sum to count the application in each stage.

Something like

Jobs.aggegate([
  {"$match":{"companyId":mongoose.Types.ObjectId(req.params.id),"active":true}},
  {"$unwind":"$applications"},
  {"$group":{
    "_id": some group key,
    "applied":{"$sum":{"$cond":["$applied",1,0]}}
    ...other stages
  }
])

Answer 2

you can try without $unwind and $group using $reduce with $conditional $sum

db.jobs.aggregate([
    //{$match : {}},
    {$project : {
        data : {
            $reduce : {
                input : "$applications",
                initialValue : {
                    "applied" : 0,
                    "shortlisted" : 0,
                    "interviewed" : 0,
                    "offered" : 0,
                    "hired" : 0,
                    "rejected" : 0
                },
                in : {
                    "applied" : {$sum : ["$$value.applied", {$cond : ["$$this.applied", 1, 0]}]},
                    "shortlisted" : {$sum : ["$$value.shortlisted", {$cond : ["$$this.shortlisted", 1, 0]}]},
                    "interviewed" : {$sum : ["$$value.interviewed", {$cond : ["$$this.interviewed", 1, 0]}]},
                    "offered" : {$sum : ["$$value.offered", {$cond : ["$$this.offered", 1, 0]}]},
                    "hired" : {$sum : ["$$value.hired", {$cond : ["$$this.hired", 1, 0]}]},
                    "rejected" : {$sum : ["$$value.rejected", {$cond : ["$$this.rejected", 1, 0]}]}
                }
            }
        }
    }},
    {$replaceRoot : {newRoot : "$data"}}
]).pretty()

result

{
    "applied" : 5,
    "shortlisted" : 4,
    "interviewed" : 2,
    "offered" : 0,
    "hired" : 0,
    "rejected" : 1
}
>