$elemMatch to fetch specific values inside array-CodePudding

I have a collection named 'attendance' that has an array:

[
  {
    "faculty": "20XX-XXXXX-XX-1",
    "sections": [
      {
        "section": "XXXX 3-1",
        "date": "04-11-2022",
        "attendance": [
          {
            "number": "XXXXX",
            "status": "Present"
          },
          {
            "number": "XXXXX",
            "status": "Present"
          },
          {
            "number": "XXXXX",
            "status": "Present"
          }
        ]
      },
      {
        "section": "XXXX 3-2",
        "date": "04-11-2022",
        "attendance": [
          {
            "number": "XXXXX",
            "status": "Present"
          },
          {
            "number": "XXXXX",
            "status": "Present"
          },
          {
            "number": "XXXXX",
            "status": "Present"
          }
        ]
      }
    ]
  }
]

I have been trying to query the values of the specific element in my array using $and and $elemMatch in:

db.attendance.find({$and:[{faculty:"20XX-XXXXX-XX-1"},{sections:{$elemMatch:{section:"XXXX 3-1",date:"04-11-2022"}}}]});

But it still prints the other section rather than one. I want to output to be:

{
    "faculty": "20XX-XXXXX-XX-1",
    "sections": [
      {
        "section": "XXXX 3-1",
        "date": "04-11-2022",
        "attendance": [
          {
            "number": "XXXXX",
            "status": "Present"
          },
          {
            "number": "XXXXX",
            "status": "Present"
          },
          {
            "number": "XXXXX",
            "status": "Present"
          }
        ]
      }

And I tried using the dot notation like:

db.attendance.find({"sections.section":"XXXX 3-1", "sections.date":"04-11-2022});

Still no luck. I'm not sure if what I'm doing is right or not. Thanks in advance!

CodePudding user response：

Option 1: find/elemMatch-> You will need to add the $elemMatch also to the project section of the find query as follow:

db.collection.find({
 "faculty": "20XX-XXXXX-XX-1",
 sections: {
   $elemMatch: {
    section: "XXXX 3-1",
    date: "04-11-2022"
   }
 }
},
{
 sections: {
   $elemMatch: {
     section: "XXXX 3-1",
     date: "04-11-2022"
   }
 }
})

Explained:

Find query has the following syntax:db.collection.find({query},{project}) Adding the project section allow you to filter the expected output.

playground option 1

Option 2: Via aggregation/$filter:

  db.collection.aggregate([
  {
  "$addFields": {
   "sections": {
    "$filter": {
      "input": "$sections",
      "as": "s",
      "cond": {
        $and: [
          {
            $eq: [
              "$$s.section",
              "XXXX 3-1"
            ]
          },
          {
            $eq: [
              "$$s.date",
              "04-11-2022"
            ]
          }
        ]
       }
      }
     }
    }
   }
 ])

Explaned:

Replace the original sections array with new ones where the array elements are filtered based on the provided criteria.

playground option 2