Find the index of an element in a MongoDB collection

Question

Find the index of an element in a MongoDB collection

I have a set of files in the following format:

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

My goal is to determine a user's position within the set.

collection.aggregate([
    { $sort: { points: -1 } }, // arrange by points descending
    { 
        $project: {
            points: '$points'
            position: {
                $indexOfArray: [ 
                    '$', // I'm unsure what value should go here
                    { 'user.username': 'myUser' }
                ]
            }
        }
    }
]);

How can I handle the set as an array?

The desired output is:

[
  {
    "points": 123,
    "position": 1
  }
]

javascript mongodb

Answer 1

Answer №1

One way to accomplish this task is by conducting a self lookup within the data. The query below demonstrates how we can obtain the desired result:

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: This operation involves counting the number of users whose scores surpass that of the specified user (in this case, 'B'). If no matching records are found, the rank will be 1; otherwise, it will be the count plus 1.

Answer 2

One way to accomplish this task is by conducting a self lookup within the data. The query below demonstrates how we can obtain the desired result:

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: This operation involves counting the number of users whose scores surpass that of the specified user (in this case, 'B'). If no matching records are found, the rank will be 1; otherwise, it will be the count plus 1.

Find the index of an element in a MongoDB collection

Answer №1

Similar questions

Tips on updating all object elements in mongodb

Processing message received from Node JS Server

Make sure to wait for the VueX value to be fully loaded before loading the component

Unable to access properties of an unknown item (reading 'remove')

MongooseError: The operation `tweets.insertOne()` exceeded the buffering timeout of 10000ms

Locate the largest variance among elements within an embedded object array

The d3.js Time Scale Chart is facing issues when rendering with Array object values, unlike static values which render the chart perfectly

What is the process for updating IDs and names for duplicated elements?

Unique MongoDB aggregate returns tailored for your needs

Unexpected behavior: jQuery AJAX request isn't triggering the alert function as expected

Do Firefox and Chrome yield different results when using navigator.geolocation.getCurrentPosition()?

I set up a website where every page had a consistent header using the HTML tag <head>  </head>, but out of nowhere, the Bootstrap functionality

Proper way to link another class in typegoose (mongoose) and store only the reference (ObjectId)?

JQuery - Triggering mouseenter/hover event only on top-level menu items, excluding the nested submenu items (list items that are not within nested lists)

Does running npm install automatically compile the library code as well?

Tips for using the Enter key to shift focus to the next input field

Submit an image to Digital Ocean Space with Expo

Step back one iteration within the Array.prototype.map method

Utilizing JavaScript to implement an interactive :hover pseudo-class dynamic effect

Alter the navigation background when moving between sections on a one-page website