Calculating selected users' different rankings in one mongodb query

I have a collection of users each having daily, monthly and yearly scores. Given that the scores change in real-time I don't want to precalculate this stuff and just want to calculate the ranks for certain users at a time. Is there a way (faceted query or ...) where I can get these rankings for users in one call?

Below is a sample user document:

{
    "_id": "5e0a361d1ca215003e79f388",
    "score_day": 20,
    "score_week": 203,
    "score_month": 850
}

Expected result given an array of user ids:

[
    {
        "_id": "???",
        "rank_day": 42,
        "rank_month": 84,
        "rank_year": 65
    },
    {
        "_id": "???",
        "rank_day": 12,
        "rank_month": 8,
        "rank_year": 68
    },
    ...
]

Mongo doesn't have an easy way of getting the ranking of a document after a sort, it is possible using the aggregation framework, however, this is not a good idea for big collections as the data needs to be sorted in memory for each field every time this is run.

You may want to look into sorted database implementations such as Redis' sorted sets if this turns out to be too slow. Mongo wasn't built for this use-case.

Also please make sure the score fields are indexed so sorting can utilize those indexes.

var targetIds = ["5e0a361d1ca215003e79f388", "5e0a361d1ca215003e79f389", ...];

db.collection.aggregate([
    { // handle multiple pipelines at once on the same documents
        $facet: {
            rank_day: [
                { // we only need the `_id` and `score_XXX` field, this will make the query use less memory
                    $project: {
                        _id: 1,
                        score_day: 1
                    }
                },
                { // sort on the score
                    $sort: {
                        score_day: -1
                    }
                },
                { // push all documents, sorted, into an array
                    $group: {
                        _id: "",
                        rankings: {
                            $push: "$$ROOT"
                        }
                    }
                },
                { // unwind the formed array back into separate documents, but pass the index
                    $unwind: {
                        path: "$rankings",
                        includeArrayIndex: "rank"
                    }
                },
                { // find the documents we need
                    $match: {
                        "rankings._id": { "$in": targetIds }
                    }
                },
                { // we only need the _id and the rank, score is useless now
                    $project: {
                        _id: "$rankings._id",
                        rank: 1
                    }
                }
            ],
            // handle the 2 other rankings in same way
            rank_week: [
                {
                    $project: {
                        _id: 1,
                        score_week: 1
                    }
                },
                {
                    $sort: {
                        score_week: -1
                    }
                },
                {
                    $group: {
                        _id: "",
                        rankings: {
                            $push: "$$ROOT"
                        }
                    }
                },
                {
                    $unwind: {
                        path: "$rankings",
                        includeArrayIndex: "rank"
                    }
                },
                {
                    $match: {
                        "rankings._id": { "$in": targetIds }
                    }
                },
                {
                    $project: {
                        _id: "$rankings._id",
                        rank: 1
                    }
                }
            ],
            rank_month: [
                {
                    $project: {
                        _id: 1,
                        score_month: 1
                    }
                },
                {
                    $sort: {
                        score_month: -1
                    }
                },
                {
                    $group: {
                        _id: "",
                        rankings: {
                            $push: "$$ROOT"
                        }
                    }
                },
                {
                    $unwind: {
                        path: "$rankings",
                        includeArrayIndex: "rank"
                    }
                },
                {
                    $match: {
                        "rankings._id": { "$in": targetIds }
                    }
                },
                {
                    $project: {
                        _id: "$rankings._id",
                        rank: 1
                    }
                }
            ]
        }
    },


    // cleanup result to expected output
    { // add the searched _ids and unwind them so we can project and filter the arrays in each document
        $addFields: {
            _id: targetIds
        }
    },
    {
        $unwind: {
            path: "$_id"
        }
    },
    { // filter the rankings based on the _id
        $project: {
            _id: 1,
            rank_day: { $filter: { input: "$rank_day", as: "rank", cond: { $eq: ["$$rank._id", "$_id"] } } },
            rank_week: { $filter: { input: "$rank_week", as: "rank", cond: { $eq: ["$$rank._id", "$_id"] } } },
            rank_month: { $filter: { input: "$rank_month", as: "rank", cond: { $eq: ["$$rank._id", "$_id"] } } }
        }
    },
    { // cleanup the array result to the internal "rank" value
        $project: {
            _id: 1,
            rank_day: { $arrayElemAt: ["$rank_day.rank", 0] },
            rank_week: { $arrayElemAt: ["$rank_week.rank", 0] },
            rank_month: { $arrayElemAt: ["$rank_month.rank", 0] }
        }
    }
]);

As I have understood from your question you want to give rank on the basis of scores. There is not any direct operator provided for getting the rank for documents by MongoDB yet. But there is a workaround that will work fine for those collections where the value in the field on which Rank is needed to be calculated is not the same for more than 2 documents or if it is okay to have different ranks for the same value of multiple documents.

[ 
{ $sort: { score_day: -1 } }, 
{ $group: { _id: null, data: { $push: "$$ROOT" } } }, 
{ $unwind: { path: "$data", includeArrayIndex: "rank_day" } }, 
{ $addFields: { "data.rank_day": { $add: [ "$rank_day", 1 ] } } }, 
{ $replaceRoot: { newRoot: "$data" } }, 
{ $sort: { score_month: -1 } }, 
{ $group: { _id: null, data: { $push: "$$ROOT" } } }, 
{ $unwind: { path: "$data", includeArrayIndex: "rank_month" } }, 
{ $addFields: { "data.rank_month": { $add: [ "$rank_month", 1 ] } } }, 
{ $replaceRoot: { newRoot: "$data" } }, 
{ $sort: { score_year: -1 } }, 
{ $group: { _id: null, data: { $push: "$$ROOT" } } }, 
{ $unwind: { path: "$data", includeArrayIndex: "rank_year" } }, 
{ $addFields: { "data.rank_year": { $add: [ "$rank_year", 1 ] } } }, 
{ $replaceRoot: { newRoot: "$data" } } 
]

The above pipeline will give you rank on the basis of the score but if there is more than 1 document with the same score value then the rank will be assigned according to there order of occurrence in the pipeline.

To avoid it to an extent what you can do is add all the scores and get an overall score and then you can sort it on the basis of day/month/year score along with overall score which will help you to get a little bit more relevant ranks. It will not gonna help if the day/week/year score is the same along with the overall score for more than 1 document.
If you agree with the above-given scenario then you can use the below-given pipeline.

[ 
{ $addFields: { overAllScore: { $add: [ "$score_day", "$score_month", "$score_year" ] } } }, 
{ $sort: { score_day: -1, overAllScore: -1 } }, 
{ $group: { _id: null, data: { $push: "$$ROOT" } } }, 
{ $unwind: { path: "$data", includeArrayIndex: "rank_day" } }, 
{ $addFields: { "data.rank_day": { $add: [ "$rank_day", 1 ] } } }, 
{ $replaceRoot: { newRoot: "$data" } }, 
{ $sort: { score_month: -1, overAllScore: -1 } }, 
{ $group: { _id: null, data: { $push: "$$ROOT" } } }, 
{ $unwind: { path: "$data", includeArrayIndex: "rank_month" } }, 
{ $addFields: { "data.rank_month": { $add: [ "$rank_month", 1 ] } } }, 
{ $replaceRoot: { newRoot: "$data" } }, 
{ $sort: { score_year: -1, overAllScore: -1 } }, 
{ $group: { _id: null, data: { $push: "$$ROOT" } } }, 
{ $unwind: { path: "$data", includeArrayIndex: "rank_year" } }, 
{ $addFields: { "data.rank_year": { $add: [ "$rank_year", 1 ] } } }, 
{ $replaceRoot: { newRoot: "$data" } }
]

At last, you can add a project stage to exclude score fields to show only the required data.