MongoDB Query, sort then take nth document for group

Question

I am trying to compose a MongoDB query...

My aim is to retrieve a list of player records by country sorted by rating and then return only the nth rated player for each country (so the top rated, or the 3rd best rated etc).

I have achieved part I:

db.getCollection('players').find( { event: 'open' }).sort({ country: 1, rating: -1 });

Update

Here is sample of two countries with three players for each, ordered by rating:

Team One:

{
    "id" : 400041,
    "name" : "Adams Michael",
    "rating" : 2727,
    "country" : "England",
    "event" : "open"
},
{
    "id" : 404853,
    "name" : "McShane Luke J",
    "rating" : 2671,
    "country" : "England",
    "event" : "open",
},
{
    "id" : 400025,
    "name" : "Short Nigel D",
    "rating" : 2666,
    "country" : "England",
    "event" : "open"
}

Team Two:

 {
    "id": 4101588,
    "name": "Kramnik Vladimir",
    "rating": 2808,
    "country": "Russia",
    "event": "open"
},
{
    "id": 4126025,
    "name": "Grischuk Alexander",
    "rating": 2784,
    "country": "Russia",
    "event": "open"
},
{
    "id": 14109603,
    "name": "Karjakin Sergey",
    "rating": 2769,
    "country": "Russia",
    "event": "open"
}

I want my query to return the objects for:

• Adams Michael
• Kramnik Vladimir

(as these are the two highest rated)

I want my query to also allow me to query for the second highest rated:

• McShane Luke J
• Grischuk Alexander

Answer 1

This is how you would do it using a 4 stage aggregation pipeline

1. Sort country-wise and rating-wise as you have done already
2. Group by country and push all player details into an array
3. This one is the clincher. Use project with $arrayElemAtto get the ith rated player for each of the respective countries

4. Use project again to give you the object in the desired format

   db.getCollection('players').aggregate(
   {
       $sort: {country: 1, rating: -1}
   },
   {
       $group:   {
           _id: "$country", 
           players: {$push: {name: "$name", rating: "$rating", event: "$event"}}
       }
   },
   {
       $project: {
           player: {$arrayElemAt: ["$players", iTH_RATING]}
       } 
   },
   {
       $project: {
           name: "$player.name",
           rating: "$player.rating",
           event: "$player.event"
           }
   })
   

See screen grab below for 2nd Highest Rated Players. Since these are 0-indexed, the iTH_RATING variable in the query is replaced with 1. To get the highest rated players replace with 0, so on and so forth.

Answer 2

You can achive that using multiple queries, one for each country, using $skip and $limit as Yogesh says in his comments.

You can use map-reduce, than you will get the expected result in 1 'query' (but bear in mind that map-reduce is not suitable for real-time queries, maybe be very slow operation but that strongly depends on your data size).

In the map function, fire documents with the key as country. In the reduce function, sort your players from the same country based on rating, and return only the document (player) from the desired position in the ranking:

var map = function map(){
    emit(this.country, this);

};

var reduce = function(key, values){
    var position_to_return = 1; // change here to 0 to return the first rated player for certain country
    var sorted_values = values.sort(function(first, second) {
        return second.rating - first.rating;
    })
    return key, sorted_values[position_to_return];
};


db.runCommand({"mapReduce":"players", map:map, reduce:reduce, out:{replace:"players2"}, query:{ "event": "open" }})

output, when returning second rated players for each country:

{
    "_id" : "England",
    "value" : {
        "_id" : ObjectId("57bb093c6124c1a9d8be905d"),
        "id" : 404853,
        "name" : "McShane Luke J",
        "rating" : 2671,
        "country" : "England",
        "event" : "open"
    }
}
{
    "_id" : "Russia",
    "value" : {
        "_id" : ObjectId("57bb093c6124c1a9d8be9060"),
        "id" : 4126025,
        "name" : "Grischuk Alexander",
        "rating" : 2784,
        "country" : "Russia",
        "event" : "open"
    }
}