MongoDB - Aggregation $filter in $filter / filtering array of subdocuments in subdocuments

Question

I have the following structure of a document in a collection:

A Factory has many departments, which has many areas.

factoryName: "",
departments: [
   { 
      departmentName: ""
      areas: [
         {
            areaName: ""
         } 
      ]
   }
]

When querying a document via the "areaName", I only want to get the area + respective parent department + respective parent factory.

As an example, please briefly see the below 2 documents.

db.factories.insertMany([{
  factoryName: "San Francisco",
  departments: [
    {
      departmentName: "Administration",
      areas: [
        {
          areaName: "Phone Guys"
        },
        {
          areaName: "Email Guys"
        }
      ]
    },
    {
      departmentName: "Development",
      areas: [
        {
          areaName: "Dev Ops"
        },
        {
          areaName: "Programming"
        },
        {
          areaName: "Architecture"
        }
      ]
    }
  ]
},{
  factoryName: "Chicago",
  departments: [
    {
      departmentName: "Administration",
      areas: [
        {
          areaName: "Phone Guys"
        },
        {
          areaName: "Email Guys"
        }
      ]
    },
    {
      departmentName: "Logistics",
      areas: [
        {
          areaName: "Delivery"
        },
        {
          areaName: "Human Resources"
        }
      ]
    }
  ]
}])

I wish to query by areaName = "Architecture" and receive back the following:

factoryName: "San Francisco",
departments: [
   { 
      departmentName: "Development"
      areas: [
         {
            areaName: "Architecture"
         } 
      ]
   }
]

Since usual query combinations via .find() with projection failed (so far), I've tried myself in aggregation. In order to achieve the wished result, I've tried many things but failing when it comes down to filtering the areas. Filtering the departments' work.

Using MongoDB Compass Visual Aggregation feature, the most logical to me seemed:

db.factories.aggregate([
  {
    '$match': {
      'departments.areas.areaName': 'Architecture'
    }
  }, {
    '$addFields': {
      'departments': {
        '$filter': {
          'input': '$departments', 
          'as': 'department', 
          'cond': {
            '$and': [
              {
                '$eq': [
                  '$$department.departmentName', 'Development'
                ]
              }, {
                '$filter': {
                  'input': '$$department.areas', 
                  'as': 'area', 
                  'cond': {
                    '$eq': [
                      '$$area.areaName', 'Architecture'
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

It seems a $filter, inside a $filter does not work or I'm missing something here since all 3 areas for the "Development" department are being returned instead of only "Architecture" whereas the "Development" department is being filtered correctly.

How can I achieve this? What am I missing? Any help is much appreciated.

Many thanks!

Answer 1

You need $filter and $map since you have nested arrays:

db.collection.aggregate([
    {   
        "$match": {
            "departments.areas.areaName": "Architecture"
        }
    },
    {
        $addFields: {
            departments: {
                $map: {
                    input: {
                        $filter: {
                            input: "$departments",
                            cond: {
                                $in: [ "Architecture", "$$this.areas.areaName" ]
                            }
                        }
                    },
                    in: {
                        departmentName: "$$this.departmentName",
                        areas: { $filter: { input: "$$this.areas", as: "d", cond: { $eq: [ "$$d.areaName", "Architecture" ] } } }
                    }
                }
            }
        }
    }
])

Mongo Playground