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?
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...