Unable to get result from $match with $and in pymongo

Question

I have mongodb query which I am trying to implement with pymongo.

I have the collection dictionaries as below:

{'_id': 0,
 'name': 'aimee Zank',
 'scores': [{'score': 10.463179736705023, 'type': 'exam'},
  {'score': 11.78273309957772, 'type': 'quiz'},
  {'score': 75.8740349954354, 'type': 'homework'}]}


{'_id': 1,
 'name': 'Tomas Jude',
 'scores': [{'score': 55.9736705023, 'type': 'exam'},
  {'score': 50.78273309957772, 'type': 'quiz'},
  {'score': 45.8740349954354, 'type': 'homework'}]}

I am trying to query for students with score higher than 40 in all three types (exam, quiz and homework). For this, I am using $match with $and in the aggregate. I am unable to get any result, with $or the condition works correctly.

agg_result=Collection.aggregate([
    {"$unwind" : "$scores" },
   {"$match": {"scores.score": {"$gt":40}}},
   { 
       "$match": {
           "$and" : [
                     {"scores.type": "exam"},
                     {"scores.type":"homework"}
                     ]
                  }
    },
   
   {
       "$group": {
           "_id" : "$_id",
           "name": {"$first": "$name"},
          "scores":{"$push" : "$scores"}
       }
   },
   
  {
      "$sort": {
          "_id" : 1
      }
  }

])

With $or, the result shows as,

{'_id': 0,
     'name': 'aimee Zank',
     'scores': [{'score': 75.8740349954354, 'type': 'homework'}]}

{'_id': 1,
     'name': 'Tomas Jude',
     'scores': [{'score': 55.9736705023, 'type': 'exam'},
      {'score': 45.8740349954354, 'type': 'homework'}]}

How to work around for $and?

Answer 1

I may have made a mountain out of a molehill, but it was the first thing that came to mind (which means there's probably a better way). You can use "$map" to cycle through the scores array where we can check the score with the type and build the pass array.

db.collection.aggregate([
  {
    // create array of pass status
    "$set": {
      "pass": {
        "$map": {
          "input": "$scores",
          "as": "score",
          "in": {
            "$switch": {
              "branches": [
                {
                  "case": { "$eq": [ "$$score.type", "exam" ] },
                  "then": { "exam": { "$gt": [ "$$score.score", 40 ] } }
                },
                {
                  "case": { "$eq": [ "$$score.type", "quiz" ] },
                  "then": { "quiz": { "$gt": [ "$$score.score", 40 ] } }
                },
                {
                  "case": { "$eq": [ "$$score.type", "homework" ] },
                  "then": { "homework": { "$gt": [ "$$score.score", 40 ] } }
                }
              ],
              "default": "$$score"
            }
          }
        }
      }
    }
  },
  {
    "$match": {
      "pass.exam": true,
      "pass.quiz": true,
      "pass.homework": true
    }
  },
  // uncomment to "$unset" "pass"
  //  {
  //    "$unset": "pass"
  //  }
])

Try it on mongoplayground.net.