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
},
{
"_id":ObjectId("615852d062a012f82cc5d54d"),
"name":"john",
"salary":12345
}
]
CodePudding user response:
You can do the followings in an aggregation pipeline:
$match
to select the expecteddepartment
document$lookup
fromemployee
collection with a subpipeline- In the subpipeline of step 2, use
$match
to filter out expected employee records - In the same subpipeline ,
$sort
by salary and$limit
to get top 2 employee $unwind
the subpipeline result$replaceRoot
to get the employee documents
Here is the Mongo playground for your reference.