CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
NicoleZ
NicoleZ

Reputation: 1780

Get count on several fields using mongoose aggregation

I have two collections in my MongoDB employee and employeeData i need to get some statics information from DB.

  1. total employees who were not deleted.
  2. total employees who have security access and are not deleted.
  3. total employees still active;

this is my Employee collection sample document

{
 _id:'5ec25e74d028af28343f1061'
 isDeleted:false
 securityAccess:true
 details:'60475b7a93ac45d64a5957b0'
}

this is EmployeeData collection document

{
  _id:'60475b7a93ac45d64a5957b0'
  emplyeeId:'5ec25e74d028af28343f1061'
  isActive:'active',
  salary:225543.00,
  department:'sales'
}

I need to get this data from one query using some kind of aggregations but I'm not much familiar with the MongoDB queries.

the expected result looks like this.

Total Employees | Active Employees | Security Access
         10            5                    2

Upvotes: 1

Views: 396

Answers (1)

turivishal
turivishal

Reputation: 36144

  • $match to check isDeleted condition
  • $lookup with EmployeeData
  • $group by null
    • get total employees count,
    • count total security access if securityAccess is true
    • count total active employees if isActive is 'active'
db.Employee.aggregate([
  { $match: { isDeleted: false } },
  {
    $lookup: {
      from: "EmployeeData",
      localField: "_id",
      foreignField: "emplyeeId",
      as: "activeEmployees"
    }
  },
  {
    $group: {
      _id: null,
      totalEmployees: { $sum: 1 },
      securityAccess: {
        $sum: {
          $cond: ["$securityAccess", 1, 0]
        }
      },
      activeEmployees: {
        $sum: {
          $cond: [
            { $eq: [{ $first: "$activeEmployees.isActive" }, "active"] },
            1,
            0
          ]
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions