MongoDB : how to find max sequenced documents in mongo

Question

I have the below MongoDB documents in a collection called 'Student'. This document contains details about Student and the revision information. I basically wanted to get the maximum sequence out of it based on some filter condition.

{
    
    "student": {
        name : "Rahul",
        age  : 18,
        department : "CSE",
        section : "B"
    },
    
    "sequence": 0,
    "student_ref": "AUTCSE024",
    "college" : "AUT"
}

{
    
    "student": {
        name : "Rahul",
        age  : 18,
        department : "CSE",
        section : "A"
    },
    
    "sequence": 1,
    "student_ref": "AUTCSE024",
    "college" : "AUT"
}

{
    
    "student": {
        name : "Kumar",
        age  : 18,
        department : "IT",
        section : "A"
    },
    
    "sequence": 0,
    "student_ref": "AUTITE011",
    "college" : "AUT"
}

{
    
    "student": {
        name : "Kumar",
        age  : 18,
        department : "IT",
        section : "C"
    },
    
    "sequence": 1,
    "student_ref": "AUTITE011",
    "college" : "AUT"
}

{
    
    "student": {
        name : "Praveen",
        age  : 18,
        department : "IT",
        section : "C"
    },
    
    "sequence": 0,
    "student_ref": "AUTITE016",
    "college" : "AUT"
}

{
    
    "student": {
        name : "Praveen",
        age  : 18,
        department : "IT",
        section : "C"
    },
    
    "sequence": 1,
    "student_ref": "AUTITE016",
    "college" : "AUT"
}

So from the above documents, I wanted the output based on max(sequence) and "student.department" : "IT". The expected result should be

{
    
    "student": {
        name : "Kumar",
        age  : 18,
        department : "IT",
        section : "C"
    },
    
    "sequence": 1,
    "student_ref": "AUTITE011",
    "college" : "AUT"
}
{
    
    "student": {
        name : "Praveen",
        age  : 18,
        department : "IT",
        section : "C"
    },
    
    "sequence": 1,
    "student_ref": "AUTITE016",
    "college" : "AUT"
}

Please let me know how to achieve this.

Answer 1

You can use sub-pipeline in $lookup to aggregate the max sequence number. Then filter the student results by matching the sequence field with the aggregated max sequence number.

db.collection.aggregate([
  {
    "$match": {
      "student.department": "IT"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        "student_ref": "$student_ref"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$student_ref",
                    "$student_ref"
                  ]
                },
                // put your filtering condition here too
                {$eq: [
                  "$student.department",
                  "IT"
                ]
              }
            ]
          }
        }
      },
      {
        "$group": {
          "_id": "$student_ref",
          "maxSeqNum": {
            "$max": "$sequence"
          }
        }
      }
    ],
    "as": "maxSeqNumLookup"
  }
},
{
  "$unwind": "$maxSeqNumLookup"
},
{
  $match: {
    // get only the student record with max sequence number
    $expr: {
      $eq: [
        "$maxSeqNumLookup.maxSeqNum",
        "$sequence"
      ]
    }
  }
},
{
  "$project": {
    // formatting the final output
    "student": 1,
    "sequence": "$maxSeqNumLookup.maxSeqNum",
    "student_ref": 1,
    "college": 1
  }
}
])

Here is the Mongo playground for your reference.