How to get ranking position of a mongoDB collection?

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams "company": "Acme", "email": "john.doe@acme.com", "matches": [171844, 169729, 173168, 174310, 168752, 174972, 172959, 169546] "_id": 2, "name": "Bruce Wayne", "company": "Wayne Enterprises", "email": "bruce@wayne.com", "matches": [171844, 232333, 233312, 123456] "_id": 3, "name": "Tony Stark", "company": "Stark Industries", "email": "tony@stark.com", "matches": [173844, 155729, 133168, 199310, 132752, 139972] "_id": 4, "name": "Clark Kent", "company": "Daily Planet", "email": "clark.kent@planet.com", "matches": [169729, 174310, 168752] "_id": 5, "name": "Lois Lane", "company": "Daily Planet", "email": "lois.lane@planet.com", "matches": [172959, 169546]

I need to get a filtered list of users but with a key that shows the user's "ranking" position based on the number of "matches" records that it has. There should be a global ranking position and a company ranking position.

The desired result should be this (for an example filtering for company='Daily Planet'):

_id: 4, name: 'Clark Kent', company: 'Daily Planet', email: 'clark.kent@planet.com', points: 3, // <= globalRank: 4, // <= companyRank: 1 // <= _id: 5, name: 'Lois Lane', company: 'Daily Planet', email: 'lois.lane@planet.com', points: 2, // <= globalRank: 4, // <= companyRank: 2 // <=

Notice that Clark Kent is ranked 4th on the global ranking since he has 3 matches (John Doe, Bruce Wayne and Tony Stark have more matches than him) and is ranked 1st on the company Ranking, since he has more matches than any Daily Planet user.

However, even after several days of research, I couldn't find a way to do it. (I couldn't even figure out how to do the global ranking or company ranking ONLY).

Any ideas on how to solve this, or on how to approach the problem in a different way?

The basic idea is to first sort the points according to the points as you have done, following up by $push them into an array. This ensures that elements are inserted in the sorted order. We then $unwind using the includeArrayIndex property to generate the index of the element in the sorted array which corresponds to the rank.

The pipeline using the above logic is as below (try to go stage by stage to understand better) :-

aggregate([
        $project: {
            _id: 1,
            name: "$name",
            company: "$company",
            email: "$email",
            points: {
                $size: "$matches"
        $sort: {
            points: -1
        $group: {
            _id: {},
            arr: {
                $push: {
                    name: '$name',
                    company: '$company',
                    email: '$email',
                    points: '$points'
        $unwind: {
            path: '$arr',
            includeArrayIndex: 'globalRank',
        $sort: {
            'arr.company': 1,
            'arr.points': -1
        $group: {
            _id: '$arr.company',
            arr: {
                $push: {
                    name: '$arr.name',
                    company: '$arr.company',
                    email: '$arr.email',
                    points: '$arr.points',
                    globalRank: '$globalRank'
        $unwind: {
            path: '$arr',
            includeArrayIndex: 'companyRank',
        $project: {
            _id: 0,
            name: '$arr.name',
            company: '$arr.company',
            email: '$arr.email',
            points: '$arr.points',
            globalRank: '$arr.globalRank',
            companyRank: '$companyRank'
The output of the query is
/* 1 */
    "companyRank" : NumberLong(0),
    "name" : "Bruce Wayne",
    "company" : "Wayne Enterprises",
    "email" : "bruce@wayne.com",
    "points" : 4,
    "globalRank" : NumberLong(2)
/* 2 */
    "companyRank" : NumberLong(0),
    "name" : "Tony Stark",
    "company" : "Stark Industries",
    "email" : "tony@stark.com",
    "points" : 6,
    "globalRank" : NumberLong(1)
/* 3 */
    "companyRank" : NumberLong(0),
    "name" : "Clark Kent",
    "company" : "Daily Planet",
    "email" : "clark.kent@planet.com",
    "points" : 3,
    "globalRank" : NumberLong(3)
/* 4 */
    "companyRank" : NumberLong(1),
    "name" : "Lois Lane",
    "company" : "Daily Planet",
    "email" : "lois.lane@planet.com",
    "points" : 2,
    "globalRank" : NumberLong(4)
/* 5 */
    "companyRank" : NumberLong(0),
    "name" : "John Doe",
    "company" : "Acme",
    "email" : "john.doe@acme.com",
    "points" : 8,
    "globalRank" : NumberLong(0)
Ranks are 0 based here.
Starting in Mongo 5, it's a perfect use case for the new $setWindowFields  aggregation operator:
// { name: "John Doe",    firm: "Acme",              matches: [171844, 169729, 173168, 174310, 168752, 174972, 172959, 169546] }
// { name: "Bruce Wayne", firm: "Wayne Enterprises", matches: [171844, 232333, 233312, 123456] }
// { name: "Tony Stark",  firm: "Stark Industries",  matches: [173844, 155729, 133168, 199310, 132752, 139972] }
// { name: "Clark Kent",  firm: "Daily Planet",      matches: [169729, 174310, 168752] }
// { name: "Lois Lane",   firm: "Daily Planet",      matches: [172959, 169546] }
db.collection.aggregate([
  { $set: { pts: { $size: "$matches" } } },
  { $setWindowFields: {
    sortBy: { pts: -1 },
    output: { globRnk: { $rank: {} } }
  { $setWindowFields: {
    partitionBy: "$firm",
    sortBy: { pts: -1 },
    output: { firmRnk: { $rank: {} } }
// { name: "John Doe",   firm: "Acme",             pts: 8, globRnk: 1, firmRnk: 1, matches: [171844, 169729, 173168, 174310, 168752, 174972, 172959, 169546] }
// { name: "Tony Stark", firm: "Stark Industries", pts: 6, globRnk: 2, firmRnk: 1, matches: [173844, 155729, 133168, 199310, 132752, 139972] }
// { name: "Bruce Wayne",firm: "Wayne Enterprises",pts: 4, globRnk: 3, firmRnk: 1, matches: [171844, 232333, 233312, 123456] }
// { name: "Clark Kent", firm: "Daily Planet",     pts: 3, globRnk: 4, firmRnk: 1, matches: [169729, 174310, 168752] }
// { name: "Lois Lane",  firm: "Daily Planet",     pts: 2, globRnk: 5, firmRnk: 2, matches: [172959, 169546 ] }
The first $setWindowFields stage adds the global rank by:
sorting documents by decreasing order of points: sortBy: { points: -1 }
and adding the globRnk field in each document (output: { globRnk: { $rank: {} } })
which is the rank of the document amongst all documents based on the sorting field points: globRnk: { $rank: {} }.
The second $setWindowFields stage is very similar to the first one, excepts for the rank that's now calculated within each partitions defined by the firm: partitionBy: "$firm".
Are you expecting this result? . result_array will hold the end result.
var my_array = db.testCol.aggregate([{ $project: { _id:1, name:1, company:1, email:1, "points" : {$size: "$matches"}, "globalRank":{$literal: 0}, companyRank:{$literal: 0} } },
{$sort: {points : -1 } },
]).toArray()
var result_array = [];
var companyCount = {};
for (i = 0; i < my_array.length; i++) {
    var company_name = my_array[i].company
    if (companyCount[company_name] == null ){
        companyCount[company_name] = 1;
    else{
        companyCount[company_name] = companyCount[company_name] + 1
    result_array.push({ "_id" : my_array[i]._id, "name": my_array[i].name, "company" : my_array[i].company, "email" : my_array[i].email, "points" : my_array[i].points, "globalRank":i+1 , "companyRank" : companyCount[company_name]})
result_array
and the Output is:
                "_id" : 1,
                "name" : "John Doe",
                "company" : "Acme",
                "email" : "john.doe@acme.com",
                "points" : 8,
                "globalRank" : 1,
                "companyRank" : 1
                "_id" : 3,
                "name" : "Tony Stark",
                "company" : "Stark Industries",
                "email" : "tony@stark.com",
                "points" : 6,
                "globalRank" : 2,
                "companyRank" : 1
                "_id" : 2,
                "name" : "Bruce Wayne",
                "company" : "Wayne Enterprises",
                "email" : "bruce@wayne.com",
                "points" : 4,
                "globalRank" : 3,
                "companyRank" : 1
                "_id" : 4,
                "name" : "Clark Kent",
                "company" : "Daily Planet",
                "email" : "clark.kent@planet.com",
                "points" : 3,
                "globalRank" : 4,
                "companyRank" : 1
                "_id" : 5,
                "name" : "Lois Lane",
                "company" : "Daily Planet",
                "email" : "lois.lane@planet.com",
                "points" : 2,
                "globalRank" : 5,
                "companyRank" : 2
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.