I want to get top two employees based on salary from a department,

Department Schema ::

const departmentSchema = mongoose.Schema({
    name: {
        type: String
    },
    employee_id: [{ type: mongoose.Schema.Types.ObjectId, ref: 'employee' }]
})

Employee Schema ::

const employeeSchema = mongoose.Schema({
    name: {
        type: String,
    },
    salary : {
       type: Number
    }
})

Let's say I have document in department schema as ::

{
    "_id":ObjectId("615851c162a012f82cc5bdf6"),
    "name":"abc",
    "employee_id":[ 
        ObjectId("615852d062a012f82cc5d54d"), 
        ObjectId("6158530462a012f82cc5d9c8"),
        ObjectID("6158530462a012f82cc8e1b9")
    ]
}

And document in employee as ::

{
    "_id":ObjectId("615852d062a012f82cc5d54d"),
    "name":"john",
    "salary":12345
}

{
    "_id":ObjectId("6158530462a012f82cc5d9c8"),
    "name":"smith",
    "salary":999
}

{
    "_id":ObjectId("6158530462a012f82cc8e1b9"),
    "name":"Alex",
    "salary":99999
}

Based on this I want to get details of top two employees based on their salary.

For this I tried as:

   await department.aggregate([
        { $match : { name : 'abc' } },
    ]).populated('employee_id')

But it throws error populate is not a function. How can I work for the same as I am very much in MySQL but not familiar with mongodb? If anyone needs any further information please do let me know.

Expected response as ::

[
    {
        "_id":ObjectId("6158530462a012f82cc8e1b9"),
        "name":"Alex",
        "salary":99999,
         department: abc
     },
     {
        "_id":ObjectId("615852d062a012f82cc5d54d"),
        "name":"john",
        "salary":12345,
        department: abc
      }
]

CodePudding user response：

You can do the followings in an aggregation pipeline:

1. $match to select the expected department document
2. $lookup from employee collection with a subpipeline
3. In the subpipeline of step 2, use $match to filter out expected employee records
4. In the same subpipeline , $sort by salary and $limit to get top 2 employee
5. use $addFields to add the department name to the employee object
6. $unwind the subpipeline result
7. $replaceRoot to get the employee documents

Here is the Mongo playground for your reference.