Mongo query used to sort within a group

Question

 db.employees.aggregate([
    { $match:{ gender:'male'}}, 
    { $group:{ _id:{ deptName:'$department.name'}, totalEmployees: { $sum:1} } },
    { $sort:{ deptName:1}}
])

The above mentioned is the query I used. Using this query the following output has been generated:

[
  { _id: { deptName: 'Finance' }, totalEmployees: 2 },
  { _id: { deptName: 'HR' }, totalEmployees: 1 },
  { _id: { deptName: 'Marketing' }, totalEmployees: 2 }
]

Here I would like to have the first name of the person with "least salary within the group" from each group in the output. What changes are to be made in the query?

The sample data used to run this query is mentioned below:

db.employees.insertMany([
    { 
        _id:1,
        firstName: "John",
        lastName: "King",
        gender:'male',
        email: "john.king@abc.com",
        salary: 5000,
        department: {"name":"HR"}
    },
    { 
        _id:2,
        firstName: "Sachin",
        lastName: "T",
        gender:'male',
        email: "sachin.t@abc.com",
        salary: 8000,
        department: {"name":"Finance"}
    },
    { 
        _id:3,
        firstName: "James",
        lastName: "Bond",
        gender:'male',
        email: "jamesb@abc.com",
        salary: 7500,
        department: {"name":"Marketing"}
    },
    { 
        _id:4,
        firstName: "Rosy",
        lastName: "Brown",
        gender:'female',
        email: "rosyb@abc.com",
        salary: 5000, 
        department: {"name":"HR"}
    },
    { 
        _id:5,
        firstName: "Kapil",
        lastName: "D",
        gender:'male',
        email: "kapil.d@abc.com",
        salary: 4500,
        department: {"name":"Finance"}
    },
    { 
        _id:6,
        firstName: "Amitabh",
        lastName: "B",
        gender:'male',
        email: "amitabh.b@abc.com",
        salary: 7000,
        department: {"name":"Marketing"}
    }
])

user_18482310 · Answer

Add a '$sort` before the $group:

db.collection.aggregate([
  {$match: {gender: "male"}},
  {$sort: {salary: 1}},
  {$group: {
      _id: {deptName: "$department.name"},
      totalEmployees: {$sum: 1},
      leastSalary: {$first: "$firstName"}
    }
  },
  {$sort: {deptName: 1}}
])

See how it works on the playground example

Mongo query used to sort within a group

1 Answers