CODEWITHSUNDEEP
javascriptmongodb
dx_over_dt
dx_over_dt

Reputation: 14318

MongoDB: Get index of element within collection

I have a collection of documents of the format:

{
  "user": { "username": "string" }
  "score": 123
}

I want to find a user's rank within the collection.

collection.aggregate([
    { $sort: { score: -1 } }, // sort by score descending
    { 
        $project: {
            score: '$score'
            rank: {
                $indexOfArray: [ 
                    '$', // I don't know what to place here
                    { 'user.username': 'myUser' }
                ]
            }
        }
    }
]);

How do I treat the collection as an array?

I'm looking for this result:

[
  {
    "score": 123,
    "rank": 1
  }
]

Upvotes: 2

Views: 145

Answers (1)

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

We can achieve this by performing a self lookup. The following query can get us the expected output:

db.collection.aggregate([
    {
        $match:{
            "user.username":"B"
        }
    },
    {
        $lookup:{
            "from":"my_collection_name",
            "let":{
                "score":"$score"
            },
            "pipeline":[
                {
                    $match:{
                        $expr:{
                            $gt:["$score","$$score"]
                        }
                    }
                },
                {
                    $group:{
                        "_id":null,
                        "above":{
                            $sum:1
                        }
                    }
                }
            ],
            "as":"selfLookup"
        }
    },
    {
        $unwind:{
            "path":"$selfLookup",
            "preserveNullAndEmptyArrays":true
        }
    },
    {
        $project:{
            "_id":0,
            "score":1,
            "rank":{
                $cond:[
                    {
                        $eq:["$selfLookup.above",null]
                    },
                    1,
                    {
                        $sum:["$selfLookup.above",1]
                    }
                ]
            }
        }
    }
]).pretty()

Data set:

{
    "_id" : ObjectId("5d657d2d7d0ab652c42315f3"),
    "user" : {
        "username" : "A"
    },
    "score" : 123
}
{
    "_id" : ObjectId("5d657d2d7d0ab652c42315f4"),
    "user" : {
        "username" : "B"
    },
    "score" : 122
}
{
    "_id" : ObjectId("5d657d2d7d0ab652c42315f5"),
    "user" : {
        "username" : "C"
    },
    "score" : 121
}

Output:

{ "score" : 122, "rank" : 2 }

Explanation: We are calculating the count of users which has scores more than the score of the searched user('B' in this case). If no records are found, rank is 1, else the rank would be count + 1.

Upvotes: 3

Related Questions