Question 116718 in Mongodb Question posted in
The official documentation can be found here.

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

So I got the following data:

Users collection

{
  _id: ObjectId("62a2a0422ec90fea68390aaa"),
  name: 'Robert Yamashita',
  username: 'robyama',
  email: '[email protected]',
},
{
  _id: ObjectId("62a2a0452ec90fea68390aad"),
  name: 'Charles X',
  username: 'cvx',
  email: '[email protected]',
}

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": "[email protected]",
    "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": "[email protected]",
    "username": "robyama",
  },
  "count": 20
  "rank": 1
}

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search