Pymongo aggregate pipeline

Admin 1
Viewed 115

I would like to use an aggregate pipeline to get the most common value given another value.

How can I use an aggregate pipeline to find what the most common StudentId is for TeacherId 212?

Have been attempting code below, but not getting desired outcome.

pl= [
        '$project': {
            '_id': 1,
            'StudentId': 1,
            "TeacherID: 1,
            "$group": {
            "__id":  'TeacherID',
            "__id": {
              "$first": "StudentID",
        }
    }
}
]

db.collection.aggregate(pl)
aggregation-framework mongodb-query mongodb python
1 Answers

Demo - https://mongoplayground.net/p/ksay82IaGHs

Group by TeacherID and TeacherID and get occurrence of the combination, $sort by occurrence in descending order.

db.collection.aggregate([
  { $group: { _id: { TeacherID: "$TeacherID", StudentID: "$StudentID" }, occurrence: { $sum: 1 } } },
  { $sort: { "occurrence": -1 } }
]);
Output 
[
  {
    "_id": {
      "StudentID": 2,
      "TeacherID": 212
    },
    "occurrence": 3
  },
  {
    "_id": {
      "StudentID": 4,
      "TeacherID": 223
    },
    "occurrence": 1
  }, .....
 ]

If you want the top record

Demo - https://mongoplayground.net/p/zBsGdAOdYwy

  {
    "$limit": 1
  }

Demo - https://mongoplayground.net/p/G2KIVcjtYII

If you want to check for specific TeacherID use $match

user_22242650
Related