I am using a sample data set in MongoDB I want to know the average exam score for class 149. There are multiple values in this dataset with class 149 so I need the average of just the "exam" scores for all values that have class_id=149. Scores is an array with multiple objects but I only care about type:"exam". How can this be achieved using aggregation or another method? enter image description here

CodePudding user response：

first deconstruct scores array using $unwind then match documents with class_id and score.type then using $group get the average

db.collection.aggregate([
  {
    $unwind: "$scores"
  },
  {
    $match: { 
        'scores.type': 'exam', 
        class_id: 149 
    }
  },
  {
    $group: {
      _id: {
        class_id: '$class_id',
        score_type: '$scores.type'
      },
      "avg": { $avg: '$scores.score' }
    }
  }
])

CodePudding user response：

You can use these operators to calculate the average score of exam.

1. $filter: Filter the documents in the score array with "exam" type.
2. $map: Generate the array field with score from the result (1).
3. $avg: Average the result (2).

db.collection.aggregate([
  {
    $match: {
      class_id: 149
    }
  },
  {
    $project: {
      avg_exam_score: {
        $avg: {
          "$map": {
            "input": {
              $filter: {
                input: "$scores",
                cond: {
                  $eq: [
                    "$$this.type",
                    "exam"
                  ]
                }
              }
            },
            "in": "$$this.score"
          }
        }
      }
    }
  }
])

Sample Mongo Playground