How to filter sub array and return document in mongo

Question

I have two collections Employee, Client

Employee schema has following record

{
    "_id": ObjectId("5a852dcd0290f7eca89e9a79"),
    "FirstName": "Nirav",
    "LastName": "Modi",
    "Gender": true,
    "Forms": [{
            "ClientId": ObjectId("5a8528ed0290f7eca89e9a5f"),
            "ProjectId": ObjectId("5a856fde0290f7eca89e9a88"),
            "FormId": ObjectId("5a62e561f6647f17f85e54c5")
        }]
}

Client schema has following record

{
    "_id" : ObjectId("5a8528ed0290f7eca89e9a5f"),
    "CompanyName" : "PNB",
    "Projects" : [{
            "_id" : ObjectId("5a856ca70290f7eca89e9a7f"),
            "Name" : "House Loan",
            "Description" : "Get house load",
            "Forms" : []
        }, {
            "_id" : ObjectId("5a856fde0290f7eca89e9a88"),
            "Name" : "Car Loan",
            "Description" : "get car loan",
            "Forms" : [
                ObjectId("5a62e82299d9fe0a14a1ead5"),
                ObjectId("5a6eec263bf43426d4d31780"),
                ObjectId("5a62e561f6647f17f85e54c5")
            ]
        }
    ]
}

In my system Employees are assigned forms to work on. as you can see employee schema contains Forms field which contains ClientId, ProjectId, FormId.

Form can be in multiple client for multiple project.

When I fetch data I want output be like as below

{
    "_id": ObjectId("5a852dcd0290f7eca89e9a79"),
    "FirstName": "Aartik",
    "LastName": "Ladumor",
    "Gender": true,
    "Clients": [{
            "_id": ObjectId("5a8528ed0290f7eca89e9a5f"),
            "CompanyName": "PNB",
            "Projects": [{
                    "_id": ObjectId("5a856fde0290f7eca89e9a88"),
                    "Name": "Car Loan",
                    "Description": "get car loan",
                    "Forms": [
                        ObjectId("5a62e82299d9fe0a14a1ead5"),
                        ObjectId("5a6eec263bf43426d4d31780"),
                        ObjectId("5a62e561f6647f17f85e54c5")
                    ]
                }
            ]
        }
    ]
}

Only get clients in which project contains forms that are assigned to employee.

For that Im doing aggregation as below

db.Employees.aggregate([{
            $lookup: {
                from: "Clients",
                localField: "Forms.ClientId",
                foreignField: "_id",
                as: "Clients"
            }
        }, {
              filter projects array in matched client contains only
              projects that match Employee.Forms each elements 
              ProjectsId -> Client.Projects._id and
              FormId -> in Client.Projects.Forms array containing forms ObjectId 
        }
    ]).pretty() 

Answer 1

try this aggregation, we $lookup by client id from employee collection, then $filter by project and form id from the matched clients

db.Employee.aggregate(
    [
        {$lookup : {
            from : "Client",
            localField : "Forms.ClientId",
            foreignField : "_id",
            as : "Clients"
        }},
        {$addFields : {
            "Clients.Projects" : {
                $filter : {
                input : {$arrayElemAt : ["$Clients.Projects", 0]},
                as : "project", 
                cond : {$and : [
                    {$eq : [{$arrayElemAt : ["$Forms.ProjectId", 0]}, "$$project._id"]},
                    {$in : [{$arrayElemAt : ["$Forms.FormId", 0]}, "$$project.Forms"]}
                ]}}
            }
        }}
    ]
).pretty()

Answer 2

You can try below aggregation

db.employees.aggregate([
  { $unwind: "$Forms" },
  {
    $lookup: {
        from: "clients",
        localField: "Forms.ClientId",
        foreignField: "_id",
        as: "Clients"
    }
  },
  { $unwind: "$Clients" },
  { $unwind: "$Clients.Projects" },
  {
    $redact: {
      $cond: {
        if: { $eq: [ "$Forms.ProjectId", "$Clients.Projects._id" ] },
        then: "$$KEEP",
        else: "$$PRUNE"
      }
    } 
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        ClientId: "$Clients._id"
      },
      FirstName: { $first: "$FirstName" },
      LastName: { $first: "$LastName" },
      Gender: { $first: "$Gender" },
      Client: { $first: "$Clients" },
      Projects: { $push: "$Clients.Projects" }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      FirstName: { $first: "$FirstName" },
      LastName: { $first: "$LastName" },
      Gender: { $first: "$Gender" },
      Clients: { $push: {
        _id: "$Client._id",
        CompanyName: "$Client.CompanyName",
        Projects: "$Projects"
      } }
    }
  }
])

Basically you have to use $unwind multiple times because you need to compare values not arrays. So $lookup merges two collections. Then you need to compare project ids so you have to $unwind again. To filter out projects that don't belong to an employee you can use $redact to compare two fields. Then to end up with two levels of nested arrays you should use $group.