mongodb getting an array inside a nested array

Question

I'm still new to mongodb, I have this basic enrollment system data:

{ "_id" : ObjectId("62277d92a561e550d5ec73ca"), "sid" : 1, "sname" : "sad", "semail" : "dsa", "scourse" : "it", "enrolled" : [ { "subjid" : 3 } ] }
{ "_id" : ObjectId("6227875bdbcc41a56a863697"), "sid" : 2, "sname" : "daws", "semail" : "dws", "scourse" : "cs", "enrolled" : [ { "subjid" : 1, "grades" : [ { "prelim" : "A", "midterm" : "B", "prefinal" : "B", "final" : "A" } ] }, { "subjid" : 2, "grades" : [ { "prelim" : "D", "midterm" : "A", "prefinal" : "B", "final" : "F" } ] } ] }

I want display the grades of sid 2 who has enrolled subjid 1.

I tried using this aggregation line:

 db.students2.aggregate( [{"$match":{"sid":{"$eq":2},"enrolled.subjid":{"$eq":2}}}, {$group: {_id:'$enrolled.subjid[1]', prelim:{$first:'$enrolled.grades.prelim'},midterm:{$first:'$enrolled.grades.midterm'},prefinal:{$first:'$enrolled.grades.prefinal'},"final":{$first:'$enrolled.grades.final'} } } ])

but this was the result:

{ "_id" : [ ], "prelim" : [ [ "A" ], [ "D" ] ], "midterm" : [ [ "B" ], [ "A" ] ], "prefinal" : [ [ "B" ], [ "B" ] ], "final" : [ [ "A" ], [ "F" ] ] }

I only wanted to get the grades of subjid 1 but it also got the grades of subjid 2

Answer 1

Maybe you need something like this:

db.collection.aggregate([
{
 "$match": {
  "sid": 2,
  "enrolled.subjid": 1
 }
},
{
 "$addFields": {
  "enrolled": {
    "$filter": {
      "input": "$enrolled",
      "as": "en",
      "cond": {
        $eq: [
          "$$en.subjid",
          1
        ]
      }
    }
   }
  }
 },
 {
  $unwind: "$enrolled"
 },
 {
  $unwind: "$enrolled.grades"
 },
 {$limit:1}
  ,
 {
  $project: {
  _id: "$enrolled.subjid",
  prelim: "$enrolled.grades.prelim",
  midterm: "$enrolled.grades.midterm",
  prefinal: "$enrolled.grades.prefinal",
  "final": "$enrolled.grades.final"
  }
 }
])

Explained:

1. Match the necessary documents (sid=2,subjid=1)
2. Filter only the enrolled elements based on subjid ( subjid=1 )
3. unwind the two array
4. limit to the first result document available only in case there is more.
5. project the necesary fields

playground