Filter deeply nested array in MongoDB-CodePudding

I have a requirement to filter a mongo collection with deeply nested array data. The document has 3 levels of nesting. Below is the sample document. The requirement is to filter the data with "status" as "verified" and also filter "array1" and "array2" based on condition and only return record which has matching data.

To summarise the filter params,
"status":"verified",
"name": "john",
"city": "mexico"

[
  {
    "_id": "111",
    "array1": [
      {
        "name": "john",
        "array2": [
          {
            "city": "mexico",
            "array3": [
              {
                "address": "address1",
                "status": "verified"
              },
              {
                "address": "address2",
                "status": "unverified"
              }
            ]
          }
        ]
      }
    ]
  },
  {
    "_id": "112",
    "array1": [
      {
        "name": "john",
        "array2": [
          {
            "city": "mexico",
            "array3": [
              {
                "address": "address1",
                "status": "unverified"
              },
              {
                "address": "address2",
                "status": "unverified"
              }
            ]
          }
        ]
      }
    ]
  }
]

The expected output is as below,

{
  "_id": "111",
  "array1": [
    {
      "name": "john",
      "array2": [
        {
          "city": "mexico",
          "array3": [
            {
              "address": "address1",
              "status": "verified"
            }
          ]
        }
      ]
    }
  ]
}

CodePudding user response：

Here's how to do it using nested $filter and $map, as you'll see the syntax is not very clean due to the schema being complex to work with.

Without knowing your product I recommend you revisit it, it might be worth to restructure depending on your common access patterns.

db.collection.aggregate([
  {
    $match: {
      "array1.array2.array3.status": "verified"
    }
  },
  {
    $addFields: {
      array1: {
        $filter: {
          input: {
            $map: {
              input: "$array1",
              as: "mapone",
              in: {
                "$mergeObjects": [
                  "$$mapone",
                  {
                    array2: {
                      $filter: {
                        input: {
                          $map: {
                            input: "$$mapone.array2",
                            as: "maptwo",
                            in: {
                              "$mergeObjects": [
                                "$$maptwo",
                                {
                                  array3: {
                                    $filter: {
                                      input: "$$maptwo.array3",
                                      as: "three",
                                      cond: {
                                        $eq: [
                                          "$$three.status",
                                          "verified"
                                        ]
                                      }
                                    }
                                  }
                                }
                              ]
                            }
                          }
                        },
                        as: "filtertwo",
                        cond: {
                          $and: [
                            {
                              $gt: [
                                {
                                  $size: [
                                    "$$filtertwo.array3"
                                  ]
                                },
                                0
                              ]
                            },
                            {
                              $eq: [
                                "$$filtertwo.city",
                                "mexico"
                              ]
                            }
                          ]
                        }
                      }
                    }
                  }
                ]
              }
            }
          },
          as: "filterone",
          cond: {
            $and: [
              {
                $gt: [
                  {
                    $size: [
                      "$$filterone.array2"
                    ]
                  },
                  0
                ]
              },
              {
                $eq: [
                  "$$filterone.name",
                  "john"
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground