MongoDB: Get index of element within collection

Question

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
  }
]

What's the json you are expecting as a result?

Caconde
– Caconde

2019-08-27 18:37:07 +00:00
Commented Aug 27, 2019 at 18:37 — Caconde
– Caconde, Commented Aug 27, 2019 at 18:37
@Caconde updated

dx_over_dt
– dx_over_dt

2019-08-27 18:38:23 +00:00
Commented Aug 27, 2019 at 18:38 — dx_over_dt
– dx_over_dt, Commented Aug 27, 2019 at 18:38

dx_over_dt · Accepted Answer · 2019-08-27 19:41:53Z

3

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.

edited Aug 27, 2019 at 19:41

dx_over_dt

14.5k17 gold badges69 silver badges129 bronze badges

answered Aug 27, 2019 at 19:03

Himanshu Sharma

3,0102 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

dx_over_dt Over a year ago

When I run this, the first aggregation stage (match) reduces the collection to 1 item, so every rank is 1.

Himanshu Sharma Over a year ago

No. The first aggregation stage gets us the record which has the searched user name. From that record, we also get a score. That score is then used to lookup in the same collection (2nd aggregation stage).

dx_over_dt Over a year ago

I see. I'm still getting rank 1 no matter which username I type in. When I add foo: '$selfLookup.above' to my $project, it doesn't appear to exist.

Himanshu Sharma Over a year ago

Can you please add some sample data?

Himanshu Sharma Over a year ago

You are changing the collection name in lookup right? in from field?

|

Collectives™ on Stack Overflow

MongoDB: Get index of element within collection

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related