I have a data structure like this. Each student will have multiple entries based on when they enter the classrooms. The query needs to get the latest record of each student based on a list of student ids and department name. It also should show the teacher id and last timestmap

[
  {
    "studentid": "stu-1234",
    "dept": "geog",
    "teacher_id": 1,
    "LastSwipeTimestamp": "2021-11-25T10:50:00.5230694Z"
  },
  {
    "studentid": "stu-1234",
    "dept": "geog",
    "teacher_id": 2,
    "LastSwipeTimestamp": "2021-11-25T11:50:00.5230694Z"
  },
  {
    "studentid": "stu-abc",
    "dept": "geog",
    "teacher_id": 11,
    "LastSwipeTimestamp": "2021-11-25T09:15:00.5230694Z"
  },
  {
    "studentid": "stu-abc",
    "dept": "geog",
    "teacher_id": 21,
    "LastSwipeTimestamp": "2021-11-25T11:30:00.5230694Z"
  }
]

Here is what I have, but it doesn't show teacher id or the last swipe timestamp. What do I need to change or add?

CodePudding user response：

Maybe you need something like this

db.collection.aggregate([
  {
    $match: {
  "studentid": {
    "$in": [
      "stu-abc",
      "stu-1234"
    ]
     },
  "dept": "geog"
    }
  },
  {
    $sort: {
      "LastSwipeTimestamp": -1
    }
  },
  {
    $group: {
      "_id": {
        "studentid": "$studentid",
        "dept": "$dept"
      },
      "teacher_id": {
        $first: "$teacher_id"
      },
      "LastSwipeTimestamp": {
        $first: "$LastSwipeTimestamp"
      }
    }
  },
  {
    $project: {
      _id: 0,
      "studentid": "$_id.studentid",
      "dept": "$_id.dept",
      "teacher_id": "$teacher_id",
     "LastSwipeTimestamp": "$LastSwipeTimestamp"
    }
  }
 ])

explained: You need to consider the not grouped fields in the $group stage so they are also available to the next $project stage...