$expr: compare array elements in same array in a document

[Musa_Gabriel]

Please help. I have a collection with this structure

{
  _id: '56d5f7eb604eb380b0d8d8f4',
  student_id: 3,
  scores: [
    { type: 'exam', score: 3.4282246207776534 },
    { type: 'quiz', score: 47.68229836664158 },
    { type: 'homework', score: 55.679287253068345 },
    { type: 'homework', score: 48.45477512309168 },
  ],
  class_id: 446,
};

I have a query that should return the scores for exams greater than scores for the quiz: point_down:
but it does not work. Please help

This is the query:

{
  $expr: {
   $gt: [
    {
     scores: {
      $elemMatch: {
       type: 'exam'
      }
     }
    },
    {
     scores: {
      $elemMatch: {
       type: 'quiz'
      }
     }
    }
   ]
  }
 }

[Prasad_Saya]

Hello @Musa_Gabriel,

You can use aggregation array operator like $filter or $reduce to compare array elements and return the result. These array operators work like iterators (like iterating in a for-loop on a JavaScript or Java array field). See the examples, for the two operators in the MongoDB Manual.

The $elemMatch cannot be used within the $expr, as $expr takes only the aggregate operators as arguments.

[Musa_Gabriel]

@Prasad_Saya thank you for the feedback. It is really helpful! now I have gone to the documentation and chose the $filter operator and wrote this query:

{
scores: {
$filter:{
input:"$scores",
cond:{ $gt:["$scores.score","$scores.score"]}
}

}
}

However, my question is how do I compare the score for the two types of entries (ie type: “exam” and type: “quiz”) so that I filter only “exam” scores which are greater than “quiz” scores for the same student. Help and feedback is much appreciated

[Prasad_Saya]

@Musa_Gabriel,

Your goal is to get the scores of exam greater then that of the quiz, so first find the quiz score.

quiz_score: {
    $filter:{
        input: "$scores",
        cond: { $eq:[ "$$this.type", "quiz" ] }
    }
}

Verify the output, and the $filter returns an array. And you can get the first element of the array using the $arrayElemAt operator, and use the value in the next stage.

In the next step, get the exam scores greater than the quiz score:

exam_scores: {
    $filter:{
        input: "$scores",
        cond: { 
            $and: [ 
                { $eq:[ "$$this.type", "exam" ] },
                { $gt: [ "$$this.score", "$quiz_score" ] }
            ]
        }
    }
} 

Please format the code you are posting.

[Musa_Gabriel]

@Prasad_Saya , I appreciate the help. After pondering on the idea of using some arrays operators I have finally found the solution in the $slice operator. The query below gave me the results I expected! It is amazing!! Thank you

[

  {

    $project: {

      student_id: 1,

      class_id: 1,

      _id: 1,

      exam: {

        $slice: ['$scores', 0, 1],

      },

      quiz: {

        $slice: ['$scores', 1, 1],

      },

    },

  },

  {

    $match: {

      $expr: {

        $gt: ['$exam.score', '$quiz.score'],

      },

    },

  },

];