How to calculate rank from total points in aggregate pipeline for gaming application

Question

I am working on gaming application will have thousands of users.

My current aggregate pipeline output

[
    {
        "USERID": "U0004",
        "Total_Points": 10
    },
    {
        "USERID": "U0001",
        "Total_Points": 8
    },
    {
        "USERID": "U0006",
        "Total_Points": 8
    },
    {
        "USERID": "U0002",
        "Total_Points": 2
    },
    {
        "USERID": "U0003",
        "Total_Points": 1
    },

    {
        "USERID": "U0005",
        "Total_Points": 1
    }
]

Expected Output

[
    {
        "USERID": "U0004",
        "Total_Points": 10,
        "Rank": 1
    },
    {
        "USERID": "U0001",
        "Total_Points": 8,
        "Rank": 2
    },
    {
        "USERID": "U0006",
        "Total_Points": 8,
        "Rank": 2
    },
    {
        "USERID": "U0002",
        "Total_Points": 2,
        "Rank": 4
    },
    {
        "USERID": "U0003",
        "Total_Points": 1,
        "Rank": 5
    },

    {
        "USERID": "U0005",
        "Total_Points": 1,
        "Rank": 5
    }
]

Which is the best and most optimised way to get the output from mongodb aggregate with the leaderboard rank concept.

I am using mongoose mongodb connection in my project.

I can manipulate data using a for loop but it seems rather inefficent.

Thanks in advance.

Comments are greatly appreciated.

Below is image of example leader board

Well You cannot... For more information check here 1) https://stackoverflow.com/questions/50888582/how-to-add-ranking-to-each-returned-item-ordered-by-specific-field?noredirect=1#comment88783877_50888582 and here 2) https://stackoverflow.com/questions/50870664/concat-field-with-index-in-map-mongodb?noredirect=1#comment88744122_50870664 — Ashh, Jun 22 '18 at 07:18
Possible duplicate of [How to sort in mongoose?](https://stackoverflow.com/questions/4299991/how-to-sort-in-mongoose) — James, Jun 22 '18 at 07:21
@James my question is not regarding sorting, but it is regarding leaderboard concept and rank system — Ratan Uday Kumar, Jun 22 '18 at 07:22
Possible duplicate of [Add some kind of row number to a mongodb aggregate command / pipeline](https://stackoverflow.com/questions/35174554/add-some-kind-of-row-number-to-a-mongodb-aggregate-command-pipeline) — Ashh, Jun 22 '18 at 07:26
@Anthony Winzlet my question not related to row number but the rank, documents can stored at any where — Ratan Uday Kumar, Jun 22 '18 at 07:29
Yes I understood what you want... Please see the two links in my first comment... they are same as you need in the question... — Ashh, Jun 22 '18 at 07:34
second link not working and first link is different, mine is different — Ratan Uday Kumar, Jun 22 '18 at 07:36
@RatanUdayKumar it sounds very much like it's related to sorting to me.... you want to fetch from Mongo a list of records _in order of_ `Total_Points`, correct? You also state in a previous comment *"documents can stored at any where"* so presumably storage order is irrelevant - therefore that says to me you want to retrieve your data _sorted_. If this isn't the case then unfortunately your question is unclear. — James, Jun 22 '18 at 08:04
@James What makes OP question different from the sorting is the "Total_Points"... How sorting would work if there is same "Total_rank" on two `USERID`? So at that time sorting will fail because OP wants the same rank on the `USERID's` which have same "Total_Points"... — Ashh, Jun 22 '18 at 10:16
@AnthonyWinzlet based on the users desired output - sort on `Total_Points` then by `UserID`.... you can only work off the details in the question and, as I've pointed out, the OPs question isn't clear enough to answer this fully. It sounds to me like really what the OP wants is to group & sort but again I can't answer until that's made clear. — James, Jun 22 '18 at 10:30
@James question is clear, i need rank base result like leader board, i have posted image of dream11 — Ratan Uday Kumar, Jun 22 '18 at 11:39

score 1 · Answer 1 · answered Jun 22 '18 at 14:09

Don't think you can do this with the aggregate.

You can do it in one line with javascript. Sort your array then map to get the rank with the index.

  a = [
    {
        "USERID": "U0004",
        "Total_Points": 10
    },
    {
        "USERID": "U0001",
        "Total_Points": 8
    },
    {
        "USERID": "U0006",
        "Total_Points": 8
    },
    {
        "USERID": "U0002",
        "Total_Points": 2
    },
    {
        "USERID": "U0003",
        "Total_Points": 1
    },

    {
        "USERID": "U0005",
        "Total_Points": 1
    }
]
a = a.sort(function(a, b){
    return b.Total_Points - a.Total_Points;
}).map(function(e, i){
  e.Rank = (i + 1);
  return e;
});

{USERID: "U0004", Total_Points: 10, Rank: 1}
{USERID: "U0001", Total_Points: 8, Rank: 2}
{USERID: "U0006", Total_Points: 8, Rank: 3}
{USERID: "U0002", Total_Points: 2, Rank: 4}
{USERID: "U0003", Total_Points: 1, Rank: 5}
{USERID: "U0005", Total_Points: 1, Rank: 6}

i can do javascript, and if number of person with same point then rank must be same like in my question. I want functionality from mongodb itself — Ratan Uday Kumar, Jun 25 '18 at 03:56

score 0 · Answer 2 · answered Jun 22 '18 at 12:41

0

Here's what I came up with.

var users = [
  { "USERID": "U0001", "Total_Points": 8  },
  { "USERID": "U0004", "Total_Points": 10 },
  { "USERID": "U0003", "Total_Points": 1  },
  { "USERID": "U0006", "Total_Points": 8  },
  { "USERID": "U0002", "Total_Points": 2  },
  { "USERID": "U0005", "Total_Points": 1  }
];

users.sort((a, b) => b.Total_Points - a.Total_Points);

for (let i = 0; i < users.length; i++) {
  let totalPoints = users[i].Total_Points;
  let usersWithRank = users.filter(user => user.Total_Points === totalPoints);
  for (let user of usersWithRank) {
    user.Rank = i + 1;
  }
  i += usersWithRank.length - 1;
}

console.log(users);

answered Jun 22 '18 at 12:41

Joseph Webber

2,010
1
19
38

i can do javascript, and if number of person with same point then rank must be same like in my question. I want functionality from mongodb itself – Ratan Uday Kumar Jun 25 '18 at 03:56
You can't insert values when getting data, you can only insert them after you've gotten it. The best you can do is sort your users by `Total_Points` in your query then calculate the rank after you get the data. You said you can use javascript and my answer does what you need, just click "Run code snippet" and check the log. – Joseph Webber Jun 25 '18 at 13:00
i know javascript but i want functionality itself from mongodb, because for thousand of data it will take very much time – Ratan Uday Kumar Jun 26 '18 at 03:52
Computers are capable of doing millions of computations a second nowadays. Looping through 100,000 users would only take like a few seconds or less. If you're really worried about speed then you can limit the number of users returned by your query to 100 or something before calculating their rank, and hide the rest behind a "Show All" button. – Joseph Webber Jun 26 '18 at 12:24

Hardik Shah · Answer 3 · 2020-06-16T07:16:56.657

you can do this with aggregate. you can check my answer on a similiar question (this seems like a duplicate): https://stackoverflow.com/a/62402340/6134150

To summarize, in the aggregate pipeline you can: 1) sort documents by score or points 2) push the players in a new array while preserving the index, this index can act as the rank 3) find the top n players from the newly formed array and/or the player in question, this player/s will have a field depicting the player's rank.

[{
    "$sort": { 
        "wins": -1
    }
},
{
    "$group": {
        "_id": false,
        "players": {
            "$push": {
                "_id": "$_id",
                "playerId": "$playerId",
                "wins": "$wins"
            }
        }
    }
},
{
    "$unwind": {
        "path": "$players",
        "includeArrayIndex": "rank"
    }
},
{
    "$match": {
        "players._id": 1234567890
    }
}]

How to calculate rank from total points in aggregate pipeline for gaming application

3 Answers3

Linked