CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Eyal Roth
Eyal Roth

Reputation: 4019

MongoDB aggregation: How to query a limited amount of "top" documents per group?

Say I have a MongoDB collection with documents about employees:

{
  name : "John Doe",
  department : "Finance",
  salary : 100
}

How can I query the X employees with the highest salaries per department?

Edit

Just to make myself a bit more clear, this is how I thought to achieve the result:

db.collection.aggregate(
    {$sort : {salary : -1}},
    {$group : {
        _id : "$department"
        employees : {$addToSet : "$name"}
    },
    {$project : {employees : {$slice : X}}}
)

But this won't work for two reasons:
1. $addToSet doesn't guarantee any ordering of the output set (at least according to the documentation).
2. $slice doesn't work in the aggregation framework.

Upvotes: 2

Views: 1817

Answers (2)

Dave
Dave

Reputation: 355

While it's not the best performance, you can achieve this returning the collection and then sorting / taking in code. If you want it by the command line, something like

db.collection.find({ Department : 'Sales' }).sort({Salary:-1}).limit(50)

should work.

Upvotes: 1

Parvin Gasimzade
Parvin Gasimzade

Reputation: 26032

Maybe following query should help you to solve your problem.

By using aggregate query, you can first sort data, then by using limit you limit the number of users with highest salary you want. At the end, you can group users by their department.

db.test.aggregate(
  {$sort : {salary : -1}},
  {$limit : 5},
  {$group : {_id:"$department", employees : {$addToSet : "$name"}}}
)

Upvotes: 0

Related Questions