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

The Bassman 0
Viewed 26

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!

ranking mongoose mongodb
2 Answers

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.

Charchit Kapoor0

Here's another way to do it. There's only one "$lookup" with one embedded "$group" so it should be fairly efficient. The "$project" seems a bit contrived, but it gives the output in the format you want.

db.users.aggregate([
  {
    "$match": {
      "username": "robyama"
    }
  },
  {
    "$lookup": {
      "from": "points",
      "as": "sortedPoints",
      "pipeline": [
        {
          "$group": {
            "_id": "$userId",
            "count": {"$sum": "$points"}
          }
        },
        {"$sort": {"count": -1}}
      ]
    }
  },
  {
    "$unwind": {
      "path": "$sortedPoints",
      "includeArrayIndex": "idx"
    }
  },
  {
    "$match": {
      "$expr": {
        "$eq": ["$_id", "$sortedPoints._id"]
      }
    }
  },
  {
    "$project": {
      "_id": {
        "name": "$name",
        "username": "$username",
        "email": "$email"
      },
      "count": "$sortedPoints.count",
      "rank": {
        "$add": ["$idx", 1]
      }
    }
  }
])

Try it on mongoplayground.net.

rickhg12hs0
Related