MongoDB特定查询实现：筛选无学业拖欠的学生（考虑补考成绩更新）

Approach #

To solve this problem, we need to identify students whose latest grades across all subjects fall into the allowed categories (excellent, good, satisfactory, passed). Here's the step-by-step breakdown of the solution:

1. Convert date strings to proper Date objects: This ensures we can accurately sort grades by their submission date.
2. Unwind the student-grade array: Isolate each student's grade entry from the control array to process them individually.
3. Retrieve the latest grade per student-subject: Sort entries by date (descending) and keep only the most recent grade for each student-subject pair.
4. Lookup related data: Fetch readable subject names from the subject collection and grade letters from the grade collection.
5. Validate grades: Mark whether each latest grade is in the allowed set.
6. Filter eligible students: Keep only students where all their latest grades are allowed.

Full Aggregation Query #

db.statement.aggregate([
  // Convert date strings to Date objects for chronological sorting
  {
    $addFields: {
      parsedDate: { $dateFromString: { dateString: "$date", format: "%d.%m.%Y" } }
    }
  },
  // Unwind the control array to process each student-grade entry separately
  { $unwind: "$control" },
  // Sort to ensure the latest grade appears first for each student-subject pair
  {
    $sort: { "control.name": 1, "subject.$id": 1, "parsedDate": -1 }
  },
  // Group by student and subject to retain only the latest grade
  {
    $group: {
      _id: { student: "$control.name", subject: "$subject.$id" },
      latestGradeId: { $first: "$control.grade.$id" },
      latestDate: { $first: "$parsedDate" }
    }
  },
  // Lookup subject details to get readable subject names
  {
    $lookup: {
      from: "subject",
      localField: "_id.subject",
      foreignField: "_id",
      as: "subjectInfo"
    }
  },
  { $unwind: "$subjectInfo" },
  // Lookup grade details to get the grade letter
  {
    $lookup: {
      from: "grade",
      localField: "latestGradeId",
      foreignField: "_id",
      as: "gradeInfo"
    }
  },
  { $unwind: "$gradeInfo" },
  // Mark if the grade falls into the allowed categories
  {
    $addFields: {
      isAllowed: {
        $in: ["$gradeInfo.letter", ["excellent", "good", "satisfactory", "passed"]]
      }
    }
  },
  // Group by student to collect all grade validity statuses
  {
    $group: {
      _id: "$_id.student",
      isAllowedList: { $push: "$isAllowed" },
      latestGrades: {
        $push: {
          subjectName: "$subjectInfo.name",
          gradeLetter: "$gradeInfo.letter",
          date: "$latestDate"
        }
      }
    }
  },
  // Check if all of the student's latest grades are allowed
  {
    $addFields: {
      allGradesAllowed: { $allElementsTrue: "$isAllowedList" }
    }
  },
  // Filter students with no academic debt
  { $match: { allGradesAllowed: true } },
  // Format output for readability
  {
    $project: {
      _id: 0,
      studentName: "$_id",
      latestGrades: 1
    }
  }
])

Key Notes #

• Date Parsing: The date field is stored as a string in dd.mm.yyyy format, so we use $dateFromString to convert it to a Date object for accurate sorting.
• DBRef Handling: We access the underlying ObjectId from DBRefs using the $id property (e.g., $subject.$id for the subject ID).
• Latest Grade Selection: Sorting entries in descending order of date and using $first in the group stage ensures we only keep the most recent grade for each student-subject pair.
• Grade Validation: The $in operator checks if a grade letter is allowed, and $allElementsTrue confirms that all of a student's latest grades meet the criteria.

内容的提问来源于stack exchange，提问作者Oleg Romanov