How to get ranking position of a mongoDB collection?

Question

I have a mongoDB collection that looks like this:

{
    "_id": 1,
    "name": "John Doe",
    "company": "Acme",
    "email": "[email protected]",
    "matches": [171844, 169729, 173168, 174310, 168752, 174972, 172959, 169546]
}
{
    "_id": 2,
    "name": "Bruce Wayne",
    "company": "Wayne Enterprises",
    "email": "[email protected]",
    "matches": [171844, 232333, 233312, 123456]
}
{
    "_id": 3,
    "name": "Tony Stark",
    "company": "Stark Industries",
    "email": "[email protected]",
    "matches": [173844, 155729, 133168, 199310, 132752, 139972]
}
{
    "_id": 4,
    "name": "Clark Kent",
    "company": "Daily Planet",
    "email": "[email protected]",
    "matches": [169729, 174310, 168752]
}
{
    "_id": 5,
    "name": "Lois Lane",
    "company": "Daily Planet",
    "email": "[email protected]",
    "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: '[email protected]',
    points: 3,     // <=
    globalRank: 4, // <=
    companyRank: 1 // <=
},
{
    _id: 5,
    name: 'Lois Lane',
    company: 'Daily Planet',
    email: '[email protected]',
    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?

Answer 1

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" : "[email protected]",
    "points" : 4,
    "globalRank" : NumberLong(2)
}

/* 2 */
{
    "companyRank" : NumberLong(0),
    "name" : "Tony Stark",
    "company" : "Stark Industries",
    "email" : "[email protected]",
    "points" : 6,
    "globalRank" : NumberLong(1)
}

/* 3 */
{
    "companyRank" : NumberLong(0),
    "name" : "Clark Kent",
    "company" : "Daily Planet",
    "email" : "[email protected]",
    "points" : 3,
    "globalRank" : NumberLong(3)
}

/* 4 */
{
    "companyRank" : NumberLong(1),
    "name" : "Lois Lane",
    "company" : "Daily Planet",
    "email" : "[email protected]",
    "points" : 2,
    "globalRank" : NumberLong(4)
}

/* 5 */
{
    "companyRank" : NumberLong(0),
    "name" : "John Doe",
    "company" : "Acme",
    "email" : "[email protected]",
    "points" : 8,
    "globalRank" : NumberLong(0)
}

Ranks are 0 based here.