$match in $lookup pipeline always returns all the documents, not filtering

Question

I have two collections.

First Collection

 {  _id:"601d07fece769400012f1280",
   FieldName:"Employee",
   Type:"Chapter" }

Second Collection

_id : "601d11905617082d7049153a",
SurveyId : "601d118e5617082d70491539",
TemplateName : "",
Elements : [

       {
            _id : "601d07fece769400012f1280",
            FieldName : "Employee",
            Type : "Chapter"
       },

       {
          _id : "601d07fece769400012f1281",
          FieldName : "Contract",
          Type : "Chapter"
       }]

When I do the lookup

        '$lookup': {
          'from': 'SecondCollection', 
          'localField': 'FieldName', 
          'foreignField': 'Elements.FieldName', 
          'as': 'SurveyInfo'
        }

I will get the correct result, but I get the "Total size of documents in SecondCollection matching pipeline's $lookup stage exceeds 16793600 bytes" sometimes.

So I changed my approach to join the second collection with the pipeline, so I get only the field I need.

 "from": 'SecondCollection',
"let": { "fieldname": "$fieldname" },
"pipeline": [
  { "$match": 
    { "$expr": 
      { "$eq": ["$elements.fieldname", "$$fieldname"] }}},
  { "$project": { "SurveyId": 1}}
],
"as": 'SurveyInfo'

Now the problem is this returns all the SecondCollection documents. Not returning the matching documents.

I am would like to get the below result

_id:"601d07fece769400012f1280",
FieldName:"Employee",
Type:"Chapter",
SurveyInfo: [
      {
       _id:"601d11905617082d7049153a",
       SurveyId:"601d118e5617082d70491539"
      }
 ]

I am not able to figure out the issue. Please help me.

Answer 1

Few Fixes,

• You have to try $in instead of $eq because $Elements.FieldName will return array of string
• Need to correct fields name in let and $match condition

db.FirstCollection.aggregate([
  {
    "$lookup": {
      "from": "SecondCollection",
      "let": { "fieldname": "$FieldName" },
      "pipeline": [
        { "$match": { "$expr": { "$in": ["$$fieldname", "$Elements.FieldName"] } } },
        { "$project": { "SurveyId": 1 } }
      ],
      "as": "SurveyInfo"
    }
  }
])

Playground

---

To match nested condition, you can try,

• $reduce to iterate loop of Elements.Children.FieldName nested array and we are going to merge nested level array in single array of sting, using $concatArrays

db.FirstCollection.aggregate([
  {
    "$lookup": {
      "from": "SecondCollection",
      "let": { "fieldname": "$FieldName" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$$fieldname",
                {
                  $reduce: {
                    input: "$Elements.Children.FieldName",
                    initialValue: [],
                    in: { $concatArrays: ["$$this", "$$value"] }
                  }
                }
              ]
            }
          }
        },
        { "$project": { "SurveyId": 1 } }
      ],
      "as": "SurveyInfo"
    }
  }
])

Playground