JavaScript

So this is how my mongoose schema looks like:

const QuizModel = new mongoose.Schema({ 
  quizId: { type: String, required: true, trim: true },  
  userId: { type: String, required: true, trim: true },  
  result: { 
    score: { type: Number, required: true},
    time: { type: Number, required: true },   
  }, 
});

This is a quiz application so there are multiple documents with same userId. I am trying to make a query to build leaderboard and I came up with this.

const topUsers = await QuizModel.find({})
      .sort({ "result.score": "desc", "result.time": "asc" })
      .limit(10);

Now because I want to show only top 10 users, I have added .limit(10). Now in these 10 documents, there is chance that all the 10 docs are of same user i.e. same userId.

How do I prevent this and still get 10 documents with all unique userId? I don’t want a single user to occupy all 10 slots in leaderboard.

Example: User1 has 5 docs with above schema with scores 100, 95, 92, 90, 60 User2 has 5 docs with above schema with scores 95, 92, 90, 80, 60

Expected Output is:

[
  {
    quizId: "....", 
    userId: "user1",
    result: {
      score: 100,
      time: "some_time"
    }
  },
  {
    quizId: "....", 
    userId: "user2",
    result: {
      score: 95,
      time: "some_time"
    }
  }
]

Answer

You need $group along with $max in order to aggregate the scores per user, try:

const topUsers = await QuizModel.aggregate([
    {
        $group: {
            _id: "$userId",
            score: { $max: "$result.score" }
        }
    },
    {
        $sort: { score: 1 }
    },
    {
        $limit: 10
    }
])

Mongoose – get documents with best score but only one of each user

Advertisement

Answer