Find one user then get their ranking based on their total points using MongoDB

Question

So I got the following data:

Users collection

{
  _id: ObjectId("62a2a0422ec90fea68390aaa"),
  name: 'Robert Yamashita',
  username: 'robyama',
  email: 'robert.yamashita@rocketmail.com',
},
{
  _id: ObjectId("62a2a0452ec90fea68390aad"),
  name: 'Charles X',
  username: 'cvx',
  email: 'charles.xxx@rocketmail.com',
}

Points collection

{
  userId: ObjectId("62a2a0422ec90fea68390aaa"),
  action: 'Liked a post',
  points: 10,
}
{
  userId: ObjectId("62a2a0422ec90fea68390aaa"),
  action: 'Liked a post',
  points: 10,
}
{
  userId: ObjectId("62a2a0452ec90fea68390aad"),
  action: 'Liked a comment',
  points: 5,
}

I created a pipeline to get the total points of username robyama using the following query:

db.users.aggregate([
  { $match: { username: 'robyama' } },
  {
    $lookup: {
      from: 'points',
      localField: '_id',
      foreignField: 'user',
      as: 'userPoints'
    }
  },
  {
    $unwind: '$userPoints'
  },
  {
    $group: {
      _id: {
        name: '$name',
        email: '$email',
        username: '$username',
      },
      count: { $sum: '$userPoints.points' }
    }
  }
]);

I got the following result:

{
  "_id": {
    "name": "Robert Yamashita",
    "email": "robert.yamashita@rocketmail.com",
    "username": "robyama",
  },
  "count": 20
}

This is exactly what I needed but I wanted to add a ranking field to the returned query since Robert has 20 points and Charles only has 5. So ideally I want the result to be this:

{
  "_id": {
    "name": "Robert Yamashita",
    "email": "robert.yamashita@rocketmail.com",
    "username": "robyama",
  },
  "count": 20
  "rank": 1
}

What should I add to my pipeline to get the above output? Any help would be greatly appreciated!

Answer 1

Well, this is one way of doing it.

1. Perform join using $lookup and calculate counts for each user.

2. Sort the elements by counts in desc order.

3. Group documents by _id as NULL and push them all in an array.

4. Unwind the array, along with getting row numbers.

5. Find your required document and calculate the rank using row number.

   db.users.aggregate([
   {
   $lookup: {
     from: "points",
     localField: "_id",
     foreignField: "userId",
     as: "userPoints"
   }
   },
   {
   $unwind: "$userPoints"
   },
   {
   $group: {
     _id: {
       name: "$name",
       email: "$email",
       username: "$username",
   
     },
     count: {
       $sum: "$userPoints.points"
     }
   }
   },
   {
   "$sort": {
     count: -1
   }
   },
   {
   "$group": {
     "_id": null,
     "docs": {
       "$push": "$$ROOT",
   
     }
   }
   },
   {
   "$unwind": {
     path: "$docs",
     includeArrayIndex: "rownum"
   }
   },
   {
   "$match": {
     "docs._id.username": "robyama"
   }
   },
   {
   "$addFields": {
     "docs.rank": {
       "$add": [
         "$rownum",
         1
       ]
     }
   }
   },
   {
   "$replaceRoot": {
     "newRoot": "$docs"
   }
   }
   ])
   

This is the playground link.