CODEWITHSUNDEEP
node.jsmongodbmongoose
ritmatter
ritmatter

Reputation: 3498

MongoDB use position in sorted query result to compute field

I have a Mongoose Model for users. Each user has a certain amount of points. I'd like to create a field that is the users rank where:

rank = user position sorted by rank / total users

Let's suppose the user model looks like this:

{
    'name': 'bob',
    'points': 15,
    'rank': 9/15,
}

(I realize that the fraction would really be a decimal when stored).

Is there a way that I can update all of these users by:

1) Sorting them by points

2) Get a user's index in this sorted list

3) Divide that index by the total number of items in the list

I'm not sure what kind of mongo operators are out there for finding a doc's position in query results and for finding the total size of the query results.

Upvotes: 3

Views: 1403

Answers (2)

Ilarion Halushka
Ilarion Halushka

Reputation: 2343

Using the previous answer is not a good idea. It requires recalculating rank after each update of points values.

Mongo version 5.0+ introduced $rank aggregation:

db.users.aggregate([
   {
      $setWindowFields: {
         sortBy: { points: 1 },
         output: {
            rank: {
               $rank: {}
            }
         }
      }
   }
])

will output

{ "points": 140, "rank": 1 },
{ "points": 160, "rank": 2 },
{ "points": 170, "rank": 3 },
{ "points": 180, "rank": 4 },
{ "points": 220, "rank": 5 }

Upvotes: 2

Juan Carlos Farah
Juan Carlos Farah

Reputation: 3879

You can do this using a couple of queries and a bit of JavaScript. Expanding on the steps you outlined, what you need to do is:

  1. Find all of the user documents, sort them by points in descending order and assign the results to a cursor. You might want to ensure that you have an index on this field to make this query run faster.
  2. Get the count for the number of documents returned.
  3. Keep track of the position of the document within the results using an index.
  4. Iterate through the documents, calculating the rank using the count and the index, and updating the corresponding user's rank with the result of that calculation.

In the mongo shell, the code would look something like the following.

var c = db.user.find().sort({ "points": -1 });
var count = c.count();
var i = 1;
while (c.hasNext()) {
    var rank = i / count;
    var user = c.next();
    db.user.update(
        { "_id": user._id },
        { "$set": { "rank": rank } }
    );
    i++;
}

So if you had the following three users in your collection:

{
    "_id" : ObjectId("54f0af63cfb269d664de0b4e"),
    "name" : "bob",
    "points" : 15,
    "rank" : 0
}
{
    "_id" : ObjectId("54f0af7fcfb269d664de0b4f"),
    "name" : "arnold",
    "points" : 20,
    "rank" : 0
}
{
    "_id" : ObjectId("54f0af95cfb269d664de0b50"),
    "name" : "claus",
    "points" : 10,
    "rank" : 0
}

After the update their documents would look like this:

{
    "_id" : ObjectId("54f0af63cfb269d664de0b4e"),
    "name" : "bob",
    "points" : 15,
    "rank" : 0.6666666666666666
}
{
    "_id" : ObjectId("54f0af7fcfb269d664de0b4f"),
    "name" : "arnold",
    "points" : 20,
    "rank" : 0.3333333333333333
}
{
    "_id" : ObjectId("54f0af95cfb269d664de0b50"),
    "name" : "claus",
    "points" : 10,
    "rank" : 1
}

Upvotes: 0

Related Questions