Get count on several fields using mongoose aggregation

Question

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

total employees who were not deleted.
total employees who have security access and are not deleted.
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

turivishal · Accepted Answer · 2021-07-06 16:38:05Z

1

$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

edited Jul 6, 2021 at 16:38

answered Jul 6, 2021 at 14:41

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NicoleZ Over a year ago

@turvishal i made one mistake here in my EmployeeData isActive is a string not boolean.its either active or inActive. i change the query like this $cond: [{ isActive:['$eq','Active']}, 1, 0] but it didn't work.update my question sorry for the inconvenience.

Collectives™ on Stack Overflow

Get count on several fields using mongoose aggregation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related