How to determine how many points a user has collected using aggregate

Question

I have this Schema structure

const QuizQuestionSchema = new Schema({
  course: { type: String, required: true, trim: true },
  point: { type: Number, required: true, trim: true, min: 1, max: 20 },
  question: { type: String, required: true, trim: true },
  codeBoard: { type: String, default: null, nullable: true },
  answers: { type: [String], required: true, trim: true },
  correctAnswer: { type: String, required: true, trim: true }
});

from client I get array id of documents and selected answer [{id, answer}...]. I need to find out how many points the user has collected, comparing correctAnswer with answer if match add score. how can this be done with an aggregate ?

example

client

[
  { id: "61bc09994da5e9ffe47fccb9", answer: "1 2 4 3 5" },
  { id: "61bc0af14da5e9ffe47fccbb", answer: "1 4 3 2" },
  ...
];

server documents

  {
    _id: new ObjectId("61bc09994da5e9ffe47fccb9"),
    course: 'JavaScript',
    point: 10,
    question: 'What will we see in the console ?',
    codeBoard: '...',
    answers: [ '1 2 4 3 5', '1 5 4 3 2', '2 1 4 3 5', '1 5 3 4 2' ],
    correctAnswer: '2 1 4 3 5',
    __v: 0
  },
  {
    _id: new ObjectId("61bc0af14da5e9ffe47fccbb"),
    course: 'JavaScript',
    point: 10,
    question: 'What will we see in the console ?',
    codeBoard: '...',
    answers: [ '1 4 3 2', '1 2 4 3', '1 2 3 4', '1 4 2 3' ],
    correctAnswer: '1 4 3 2',
    __v: 0
  }

How is your user schema? could you provide a complete example of your data? — Rubén Vega, Dec 27 '21 at 10:22
So, you don't have a "user", the client app just send you their answer with the server document Id right? If so, you could use something like [this](https://mongoplayground.net/p/3pw7Y8zwuB5). Whoever I still dont understand what is that `answers` array field for... — Rubén Vega, Dec 27 '21 at 16:38
Oh, I just realize that the `answers` array field may be the posible choices xD. The playground that i shared should work. — Rubén Vega, Dec 27 '21 at 16:48
@RubénVega in principle, everything works, add your answer.but I have 2 more questions.Is it possible to make it return not an array, but the number of points ?.this structure doesn't work in mongoose, how should I write All this for mongoose ? — Edgar, Dec 28 '21 at 15:34
IDK if you can return just an object. but you could access it with `[0]` because using that group will only return a single document. As for the mongosse query you should be able to use that exact same code calling the aggregation methos of your model. [docs](https://mongoosejs.com/docs/api/aggregate.html) — Rubén Vega, Dec 28 '21 at 15:52
see the string identifier does not work with this structure, it must necessarily be an object `new Types.ObjectId('61ba9d03971cf7a7a5de90a6')`.[Mongoose's find method with $or condition does not work properly](https://stackoverflow.com/questions/7382207/mongooses-find-method-with-or-condition-does-not-work-properly) — Edgar, Dec 28 '21 at 16:04
Oh, true! I didn't understand you. But yes f you want to find by ObjectId you should create those objects as that questions shows. — Rubén Vega, Dec 28 '21 at 16:09

score 1 · Answer 1 · answered Dec 27 '21 at 10:46

You can achieve this with a pretty straight forward pipeline, using $lookup, like so:

db.users.aggregate([
  {
    $match: {
      user_id: 1
    }
  },
  {
    "$lookup": {
      "from": "quiz",
      let: {
        quizId: "$id",
        userAnswer: "$answer"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$quizId",
                    "$_id"
                  ]
                },
                {
                  $eq: [
                    "$$userAnswer",
                    "$correctAnswer"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "matched"
    }
  },
  {
    $group: {
      _id: null,
      total: {
        $sum: 1
      },
      correct: {
        $sum: {
          $size: "$matched"
        }
      }
    }
  }
])

Mongo Playground

You didn't give exact input and output required so this will require some minor changes.

Rubén Vega · Accepted Answer · 2021-12-28T16:16:03.973

If the client side is sending each question id in String with the chosen answer you should map that array to:

  {
    id: new ObjectId("61bc09994da5e9ffe47fccb9"),
    correctAnswer: "2 1 4 3 5"
  }

Remenber to define ObjectId with:

const ObjectId = require('mongoose').Types.ObjectId;

Then add the full array inside an $or operator and finally group every match adding the points.

db.collection.aggregate({
  "$match": {
    $or: [
      {
        id: new ObjectId("61bc09994da5e9ffe47fccb9"),
        correctAnswer: "2 1 4 3 5"
      },
      {
        id: new ObjectId("61bc0af14da5e9ffe47fccbb"),
        correctAnswer: "1 4 3 2"
      },
      . . .
    ]
  }
},
{
  "$group": {
    "_id": null, // This means that every document is grouped, because there is no condition to group by
    "points": { 
      $sum: "$point"
    }
  }
})

How to determine how many points a user has collected using aggregate

2 Answers2