Filter nested array using projection without using unwind

Question

I have my collection1 which holds the _ids of collection2 in projects field as follows:

    {
        "name": "adafd",
        "employeeId": "employeeId",
        "locations": [
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)"
        ]
    }

collection2 is as follows

"collection2": [
    {   
        "location": "india",
        "states": [
            {
                "stateCode": "TN",
                    "districts": {
                        "cities": [
                            {
                                "code": 1,
                                "name": "xxx"
                            },
                            {
                                "code": 4,
                                "name": "zzz"
                            },
                            {
                                "code": 6,
                                "name": "yyy"
                            }
                        ]
                    }
            }
        ]
    }
]

I am trying to filter nested arrays inside collection2 after lookup as follows:

    db.collection.aggregate([
        {
            $lookup: {
                from: "collection2",
                localField: "locations",
                foreignField: "_id",
                as: "locations"
            }
        },
        {
            $match: {
                "name": "adafd",
            },
        },
        {
            $project: {
                'details': {
                    $filter: {
                        input: "$locations",
                        as: "location",
                        cond: { 
                            "$eq": ["$$location.states.stateCode", "TN" ]
                        }
                    }
                }
            }
        }
    ]
)

It is returning an empty array for locations.

I modified the project as follows to even filter states inside collection2 array in the projection as follows, but filters are not applying. It is returning all the data inside the states array.

{
        $project: {
            'details': {
                $filter: {
                    input: "$locations",
                    as: "location",
                    cond: { 
                        $filter: {
                            input: "$location.states",
                            as: "state",
                            cond: { 
                                "$eq": ["$$state.stateCode", "TN" ]
                            }
                        }
                    }
                }
            }
        }
    }

I have found several solutions regarding this but none worked for me. As I don't want to use unwind. Is there any way to achieve this..?

Note: I don't want to use pipeline inside $lookup as it is not supported by DocumentDB. And also there should be any $unwind and $group in the query.

Answer 1

• $match your conditions
• $lookup with collection2
• $project to filter locations by location name
• $unwind deconstruct the locations array
• $project to filter states by state code
• $unwind deconstruct the states array
• $project to filter cities by city code
• $unwind deconstruct the cities array

db.collection1.aggregate([
  { $match: { name: "adafd" } },
  {
    $lookup: {
      from: "collection2",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
    }
  },
  {
    $project: {
      locations: {
        $filter: {
          input: "$locations",
          cond: { $eq: ["$$this.location", "india"] }
        }
      }
    }
  },
  { $unwind: "$locations" },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          $filter: {
            input: "$locations.states",
            cond: { $eq: ["$$this.stateCode", "TN"] }
          }
        }
      }
    }
  },
  { $unwind: "$locations.states" },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          stateCode: "$locations.states.stateCode",
          districts: {
            cities: {
              $filter: {
                input: "$locations.states.districts.cities",
                cond: { $eq: ["$$this.code", 1] }
              }
            }
          }
        }
      }
    }
  },
  { $unwind: "$locations.states.districts.cities" }
])

Playground

---

Second option without using $unwind, instead of you can use $arrayElemAt,

db.collection1.aggregate([
  { $match: { name: "adafd" } },
  {
    $lookup: {
      from: "collection2",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
    }
  },
  {
    $project: {
      locations: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$locations",
              cond: { $eq: ["$$this.location", "india"] }
            }
          },
          0
        ]
      }
    }
  },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          $arrayElemAt: [
            {
              $filter: {
                input: "$locations.states",
                cond: { $eq: ["$$this.stateCode", "TN"] }
              }
            },
            0
          ]
        }
      }
    }
  },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          stateCode: "$locations.states.stateCode",
          districts: {
            cities: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$locations.states.districts.cities",
                    cond: { $eq: ["$$this.code", 1] }
                  }
                },
                0
              ]
            }
          }
        }
      }
    }
  }
])

Playground