$lookup to Join within Nested Array

Question

I have two collections Clients,Forms

Clients schema has following record

{
"_id" : ObjectId("5b0bd79adcbf901ee404d8c0"),
"Name" : "Danielle",
"Email" : "Janet@test.com",
"Projects" : [{
        "_id" : ObjectId("5b1e6f3410ef671cf82404be"),
        "Name" : "test",
        "Description" : "ttet",
        "Forms" : [
            ObjectId("5b03ff291c70c513bc9dbfa8"),
            ObjectId("5b16238f30491d1c643f7f28"),
            ObjectId("5afc23f3382646009c5210ab"),
        ],
        "IsActive" : true
    }, {
        "_id" : ObjectId("5b03ffc11c70c513bc9dbfb1"),
        "Name" : "apadei ief",
        "Description" : "ttasdadet",
        "Forms" : [
            ObjectId("5b03ff291c70c513bc9dbfa8"),
            ObjectId("5b16238f30491d1c643f7f28")
        ],
        "IsActive" : true
    }, {
        // array of projects
    }
],
"IsDeleted" : false,

}

Forms schema has following record

{
    "_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
    "Name" : "Employee Information",
    "Description" : "",
    "IsActive" : true
},
{
    "_id" : ObjectId("5b16238f30491d1c643f7f28"),
    "Name" : "test form",
    "Description" : "",
    "IsActive" : true
},
{
    "_id" : ObjectId("5afc23f3382646009c5210ab"),
    "Name" : "Android test",
    "Description" : "",
    "IsActive" : true
},
{
    "_id" : ObjectId("5a6304ffc3c3f119fc0e60c8"),
    "Name" : "feedback form",
    "Description" : "",
    "IsActive" : true
}

I want output be like as below

    {
    "_id" : ObjectId("5b0bd79adcbf901ee404d8c0"),
    "Name" : "Danielle",
    "Email" : "Janet@test.com",
    "Projects" : [{
            "_id" : ObjectId("5b1e6f3410ef671cf82404be"),
            "Name" : "test",
            "Description" : "ttet",
            "Forms" : [{
                    "_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
                    "Name" : "Employee Information",
                    "Description" : "",
                    "IsActive" : true
                }, {
                    "_id" : ObjectId("5b16238f30491d1c643f7f28"),
                    "Name" : "test form",
                    "Description" : "",
                    "IsActive" : true
                }, {
                    "_id" : ObjectId("5afc23f3382646009c5210ab"),
                    "Name" : "Android test",
                    "Description" : "",
                    "IsActive" : true
                }
            ],
            "IsActive" : true
        }, {
            "_id" : ObjectId("5b03ffc11c70c513bc9dbfb1"),
            "Name" : "apadei ief",
            "Description" : "ttasdadet",
            "Forms" : [{
                    "_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
                    "Name" : "Employee Information",
                    "Description" : "",
                    "IsActive" : true
                }, {
                    "_id" : ObjectId("5b16238f30491d1c643f7f28"),
                    "Name" : "test form",
                    "Description" : "",
                    "IsActive" : true
                }
            ],
            "IsActive" : true
        }, {
            // array of projects
        }
    ],
    "IsDeleted" : false
}

As per output i want forms should be come from Forms collections.

For this I am doing aggregation as below,

db.Clients.aggregate([{
            $match : {
                _id : ObjectId("5a8528ed0290f7eca89e9a5f"),
                IsDeleted : false
            }
        }, {
            $addFields : {
                "Forms" : {
                    $map : {
                        input : {
                            $map : {
                                input : "$Projects",
                                in : {
                                    $arrayElemAt : [{
                                            $objectToArray : "$$this"
                                        }, 1]
                                },
                            }
                        },
                        in : "$$this.v"
                    }
                }
            }
        }, {
            $lookup : {
                from : "Forms",
                localField : "Projects.Forms",
                foreignField : "_id",
                as : "Forms"
            }
        }, {
            $addFields : {
                "Forms" : {
                    $arrayElemAt : ["$Forms", 0]
                }
            }
        }
    ])

but it give me wrong output,it returns only one Form from one project.I want each Forms from each Projects.

Your answer is working fine for me, but what if i want to make filter within the $map for

$and: [{
   $eq: ["$Projects.IsActive", true]
}, {
   $eq: ["$Projects.IsDeleted", false]
}]

Answer 1

Being that you appear to have MongoDB 3.6 available to you by some of the features you are attempting to use, then you may as well use the ones that really matter. Namely the "sub-pipeline" form of $lookup that allows the declaration of an expression to match:

db.Clients.aggregate([
  { "$match" : { "_id": ObjectId("5a8528ed0290f7eca89e9a5f"), "IsDeleted": false } },
  { "$lookup": {
    "from": "Forms",
    "let": {
      "join": {
        "$reduce": {
          "input": "$Projects.Forms",
          "initialValue": [],
          "in": { "$concatArrays": [ "$$value", "$$this" ] }
        }
      }
    },
    "as": "join",
    "pipeline": [
      { "$match": { 
        "$expr": { "$in": [ "$_id", "$$join" ] }
      }}
    ]
  }},
  { "$addFields": {
    "Projects": {
      "$map": {
        "input": "$Projects",
        "in": {
          "$mergeObjects": [
            "$$this",
            { 
              "Forms": {
                "$map": {
                  "input": "$$this.Forms",
                  "in": {
                    "$arrayElemAt": [
                      "$join",
                      { "$indexOfArray": [ "$join._id", "$$this" ] }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    },
    "join": "$$REMOVE"
  }}
])

Basically you do the $lookup after using the $reduce and $concatArrays in order to "flatten" your nested array detail into a singular list of matching "Forms" ObjectId values. These can be compared to the foreign collection to return the correct related items.

Provided that there are no "missing" items in the foreign collection and all referenced items match, then it's really just a matter of processing the arrays using $map and swapping out the "joined" content from the "Forms" for the existing ObjectId values. Again since you're using MongoDB 3.6 you can use $mergeObjects in order to make the $map operations a bit more flexible without actually naming all properties explicitly.

The matching here is done using $indexOfArray to find the "match" and $arrayElemAt in order to extract that value and swap it out during the $map.

Even without MongoDB 3.6, you can still do pretty much the same thing:

db.Clients.aggregate([
  { "$match" : { "_id": ObjectId("5a8528ed0290f7eca89e9a5f"), "IsDeleted": false } },
  { "$addFields": {
    "join": {
      "$reduce": {
        "input": "$Projects.Forms",
        "initialValue": [],
        "in": { "$concatArrays": [ "$$value", "$$this" ] }
      }
    }
  }},
  { "$lookup": {
    "from": "Forms",
    "localField": "join",
    "foreignField": "_id",
    "as": "join"
  }},
  { "$project": {
    "Name": 1,
    "Email": 1,
    "Projects": {
      "$map": {
        "input": "$Projects",
        "in": {
          "_id": "$$this._id",
          "Name": "$$this.Name",
          "Description": "$$this.Description",
          "Forms": {
            "$map": {
              "input": "$$this.Forms",
              "in": {
                "$arrayElemAt": [
                  "$join",
                  { "$indexOfArray": [ "$join._id", "$$this" ] }
                ]
              }
            }
          },
          "IsActive": "$$this.IsActive"
        }
      }
    },
    "IsDeleted": 1
  }}
])

Just a little longer since you need to add the "flattened" array content before the $lookup and then also don't have the other features to fully enable the $addFields on remapping the arrays so we use $project instead.

Both ways return the same thing:

{
        "_id" : ObjectId("5b0bd79adcbf901ee404d8c0"),
        "Name" : "Danielle",
        "Email" : "Janet@test.com",
        "Projects" : [
                {
                        "_id" : ObjectId("5b1e6f3410ef671cf82404be"),
                        "Name" : "test",
                        "Description" : "ttet",
                        "Forms" : [
                                {
                                        "_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
                                        "Name" : "Employee Information",
                                        "Description" : "",
                                        "IsActive" : true
                                },
                                {
                                        "_id" : ObjectId("5b16238f30491d1c643f7f28"),
                                        "Name" : "test form",
                                        "Description" : "",
                                        "IsActive" : true
                                },
                                {
                                        "_id" : ObjectId("5afc23f3382646009c5210ab"),
                                        "Name" : "Android test",
                                        "Description" : "",
                                        "IsActive" : true
                                }
                        ],
                        "IsActive" : true
                },
                {
                        "_id" : ObjectId("5b03ffc11c70c513bc9dbfb1"),
                        "Name" : "apadei ief",
                        "Description" : "ttasdadet",
                        "Forms" : [
                                {
                                        "_id" : ObjectId("5b03ff291c70c513bc9dbfa8"),
                                        "Name" : "Employee Information",
                                        "Description" : "",
                                        "IsActive" : true
                                },
                                {
                                        "_id" : ObjectId("5b16238f30491d1c643f7f28"),
                                        "Name" : "test form",
                                        "Description" : "",
                                        "IsActive" : true
                                }
                        ],
                        "IsActive" : true
                }
        ],
        "IsDeleted" : false
}