MongoDB map-filter with nested arrays not working as expected

Question

I have documents having the following schema:

[
  {
    "documentKey": "documentData",
    "functions": [
      {
        "name": "firstFunction",
        "outerArray": ["elements", "in", "the", "array"],
        "objectsToBeFiltered": [
          {
            "objectName": "firstObject",
            "innerArray": ["elements", "in", "the", "array"],
            "columns": ["a", "b"]
          },
          {
            "objectName": "secondObject",
            "innerArray": ["elements", "in", "the", "array"],
            "columns": ["z"]
          }
        ]
      },
      {
        "name": "secondFunction",
        "outerArray": ["elements", "in", "the", "array"],
        "objectsToBeFiltered": [
          {
            "objectName": "firstObject",
            "innerArray": ["elements", "in", "the", "array"],
            "columns": ["m"]
          },
          {
            "objectName": "secondObject",
            "innerArray": ["elements", "in", "the", "array"],
            "columns": ["a"]
          }
        ]
      }
    ]
  }
]

I want to discard elements of objectsToBeFiltered array without "a" in columns

I have gone through this SO answer for a similar problem but replicating it has not worked out for me: Mongo Playground. I end with empty objectsToBeFiltered array for all objects in functions array.

My expected output is:

[
  {
    "documentKey": "documentData",
    "functions": [
      {
        "name": "firstFunction",
        "outerArray": ["elements", "in", "the", "array"],
        "objectsToBeFiltered": [
          {
            "objectName": "firstObject",
            "innerArray": ["elements", "in", "the", "array"],
            "columns": ["a", "b"]
          }
        ]
      },
      {
        "name": "secondFunction",
        "outerArray": ["elements", "in", "the", "array"],
        "objectsToBeFiltered": [
          {
            "objectName": "secondObject",
            "innerArray": ["elements", "in", "the", "array"],
            "columns": ["a"]
          }
        ]
      }
    ]
  }
]

score 2 · Accepted Answer · answered Nov 12 '20 at 13:11

you just need to use $in instead of $eq

as $eq compares both type and value, so you're trying to search for elements in the array which have columns = 'a', so columns should be a string and has a value = 'a'

while $in gives you the ability to check if some value exists in an array or not

db.collection.aggregate([
  {
    "$addFields": {
      functions: {
        $map: {
          input: "$functions",
          as: "fld2",
          "in": {
            name: "$$fld2.name",
            outerArray: "$$fld2.outerArray",
            objectsToBeFiltered: {
              $filter: {
                input: "$$fld2.objectsToBeFiltered",
                as: "fld4",
                cond: {
                  "$in": [
                    "a", // the value you're looking for
                    "$$fld4.columns" // the array to search in
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

and here is a working example Mongo Playground

MongoDB map-filter with nested arrays not working as expected

1 Answers1