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!

CodePudding user response：

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.

CodePudding user response：

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.