How can I used aggregate to fetch items inside nested documents?-CodePudding

I have two documents inside a collection that has nested arrays:

{
      faculty: '2019-00001-FC-0',
      sections: [
        {
          section: 'DEET 3-1',
          date: '04-19-2022',
          subject: 'Integrated Circuits',
          attendance: [
            {
              number: '2019-00005-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00006-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00007-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00015-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00016-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00018-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00019-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00020-MN-0',
              status: 'Present'
            },
            {
              number: '2019-14614-MN-0',
              status: 'Present'
            }
          ]
        }
      ]
    }

and

{
      faculty: '2019-00002-FC-0',
      sections: [
        {
          section: 'DEET 3-1',
          date: '04-19-2022',
          subject: 'Integrated Circuits',
          attendance: [
            {
              number: '2019-00005-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00006-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00007-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00015-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00016-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00018-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00019-MN-0',
              status: 'Present'
            },
            {
              number: '2019-00020-MN-0',
              status: 'Present'
            },
            {
              number: '2019-14614-MN-0',
              status: 'Present'
            }
          ]
        }
      ]
    }

I have been using find and aggregate to fetch the specific values inside the nested arrays with:

db.attendances.aggregate({
  $project: {
    sections: {
      $filter: {
        input: '$sections',
        as: 's',
        cond: {
          $and: [
            {
              $eq: ['$$s.section', 'DEET 3-1'],
            }
          ]
        }
      }
    }
  }
});

But whenever I nest the code to go deeper in the array it doesn't work.

I want the output print out'2019-000005-MN-0' from different documents like:

{
      faculty: '2019-00001-FC-0',
      sections: [
        {
          section: 'DEET 3-1',
          date: '04-19-2022',
          subject: 'Integrated Circuits',
          attendance: [
            {
              number: '2019-00005-MN-0',
              status: 'Present'
            }
          ]
        }
      ]
    }

and

{
      faculty: '2019-00002-FC-0',
      sections: [
        {
          section: 'DEET 3-1',
          date: '04-19-2022',
          subject: 'Integrated Circuits',
          attendance: [
            {
              number: '2019-00005-MN-0',
              status: 'Present'
            }
          ]
        }
      ]
    }

Is there a way to do this or something similar? Thanks in advance!

CodePudding user response：

Query

because you have nested array you need nested map/filter
for the nested you need filter to keep just the attendance with the number
for the outer array you need map, not filter because you want not just keep some member, but you want to change them
the outer map replaces the sections that dont match with null and if match, they go nested and filter the attendance
we need one last filter on sections to remove those nulls

Playmongo

aggregate(
[{"$set": 
   {"sections": 
     {"$map": 
       {"input": "$sections",
        "as": "s",
        "in": 
         {"$cond": 
           [{"$eq": ["$$s.section", "DEET 3-1"]},
             {"$mergeObjects": 
               ["$$s",
                 {"attendance": 
                   {"$filter": 
                     {"input": "$$s.attendance",
                      "as": "a",
                      "cond": {"$eq": ["$$a.number", "2019-00005-MN-0"]}}}}]},
            null]}}}}},
 {"$set": 
   {"sections": 
     {"$filter": 
       {"input": "$sections", "cond": {"$ne": ["$sections", null]}}}}}])