How to Query to achieve ranking documents with heighest points

user_2560858 0
Viewed 82

In MongoDB document is saved in this format. I want to pull data in such a way that userId has a maximum total sum of points with the highest Rank. I'm a new guy in MongoDB. Please help me how to use query .

This data is saved in MongoDB.

  [{
    _id:"33fa7277-d882-4add-9985-e92c3e48c15b",
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06141",
    points_total:280
},
{
    _id:"33fa7277-d882-4add-9985-e92c3e48c25b",
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06142",
    points_total:200
},
{
    _id:"33fa7277-d882-4add-9985-e92c3e48c35b",
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06141",
    points_total:20
},
{
    _id:"33fa7277-d882-4add-9985-e92c3e48c45b",
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06144",
    points_total:150
}]

I tried this query but not working.

db.users.aggregate([
    { "$sort": {"points_total" : -1}},
    { "$group": {
      "_id": "$user_id",
      "points_total":{"$sum": "$points_total"},
      "items": { "$push": "$$ROOT" }
    }},
    { "$unwind": { "path": "$items", "includeArrayIndex": "items.rank" } },
    { "$replaceRoot": { "newRoot": "$items" } },
    { "$sort": { "points_total" : -1} }
  ])

Expected Output

[{
   
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06141",
    points_total:300,
    rank:1
},
{
  
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06142",
    points_total:200,
    rank:2
},
{
   
    user_id:"a86302a0-2b5b-4392-95c6-6b41a4d06144",
    points_total:150,
    rank:3
}]
mongoose aggregation-framework mongodb-query node.js mongodb
1 Answers

You need one more $group in order to get all users in one array before you apply $unwind:

db.users.aggregate([
    {
        $group: {
            _id: "$user_id",
            points_total: { $sum: "$points_total" }
        }
    },
    {
        $sort: { points_total: -1 }
    },
    {
        $group: {
            _id: null,
            users: { $push: { user_id: "$_id", points_total: "$points_total" } }
        }
    },
    { "$unwind": { "path": "$users", "includeArrayIndex": "users.rank" } },
    {
        $replaceRoot: {
            newRoot: "$users"
        }
    },
    {
        $addFields: {
            rank:  { $add: [ "$rank", 1 ] }
        }
    }
])

Mongo Playground

user_62389770
Related