MongoDB aggregations group by and count with a join-CodePudding

I have MongoDB model called candidates

 appliedJobs: [
      {
        job: { type: Schema.ObjectId, ref: "JobPost" },
        date:Date
      },
    ],

candidate may have multiple records in appliedJobs array. There I refer to the jobPost. jobPost has the companyName, property.

companyName: String,

What I want is to get the company names with send job applications counts. For an example |Company|Applications| |--------|---------------| |Facebook|10 applications| |Google|5 applications|

I created this query

 Candidate.aggregate([
      {
        $match: {
          appliedJobs: { $exists: true },
        },
      },
      { $group: { _id: '$companyName', count: { $sum: 1 } } },
    ])

The problem here is I can't access the companyName like this. Because it's on another collection. How do I solve this?

Anyhelp!

Thanks in advanced.

CodePudding user response：

In order to get data from another collection you can use $lookup (nore efficient) or populate (mongoose - considered more organized), so one option is:

db.candidate.aggregate([
  {$match: {appliedJobs: {$exists: true}}},
  {$unwind: "$appliedJobs"},
  {$lookup: {
      from: "JobPost",
      localField: "appliedJobs.job",
      foreignField: "_id",
      as: "appliedJobs"
    }
  },
  {$project: {companyName: {$first: "$appliedJobs.companyName"}}},
  {$group: {_id: {candidate: "$_id", company: "$companyName"}, count: {$sum: 1}}},
  {$group: {
    _id: "$_id.candidate", 
    appliedJobs: {$push: {k: "$_id.company", v: "$count"}}
  }},
  {$project: {appliedJobs: {$arrayToObject: "$appliedJobs"}}}
])

See how it works on the playground example

CodePudding user response：

Simply $unwind the appliedJobs array. Perform $lookup to get the companyName. Then, $group to get count of applications by company.

db.Candidate.aggregate([
  {
    $match: {
      appliedJobs: {
        $exists: true
      }
    }
  },
  {
    $unwind: "$appliedJobs"
  },
  {
    "$lookup": {
      "from": "JobPost",
      "localField": "appliedJobs._id",
      "foreignField": "_id",
      "as": "JobPostLookup"
    }
  },
  {
    $unwind: "$JobPostLookup"
  },
  {
    "$group": {
      "_id": "$JobPostLookup.companyName",
      "Applications": {
        "$sum": 1
      }
    }
  }
])

Here is the Mongo Playground for your reference.