Filter nested array using projection without using unwind

user_8503505 0
Viewed 1035

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.

nosql-aggregation dynamodb-queries aggregation-framework mongodb-query mongodb
3 Answers
  • $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

user_89871280

You might use $map. There are several mistakes in your code. $match needs to be "locations.location":"india" and inside the filter input you need to use $$<var name>

db.collection1.aggregate([
  {
    $lookup: {
      from: "collection2",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
    }
  },
  {
    $project: {
      "details": {
        input: {
          $map: {
            input: "$locations",
            as: "location",
            in: {
              "_id": "$$location._id",
              location: "$$location.location",
              states: {
                $filter: {
                  input: "$$location.states",
                  as: "state",
                  cond: {
                    "$eq": [
                      "$$state.stateCode",
                      "TN"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

Working Mongo playground

Suppose if you need to eliminate documents which hold empty state, you can easily do with match

user_79757710

You can try it like this Its working

db.getCollection('col').aggregate([
    {
        $lookup: {
            from: "col",
            localField: "locations",
            foreignField: "_id",
            as: "locations"
        }
    },
    {
        $project: {
            'locations': {
                $filter: {
                    input: "$collection2",
                    as: "collection",
                    cond: { 
                        "$eq": ["$$collection.location", "india" ],
                    }
                }
            }
        }
    },
    {
        $project: {
            'details': {
                $filter: {
                    input: "$locations",
                    as: "location",
                    cond: { 
                        $filter: {
                            input: "$$location.states",
                            as: "state",
                            cond: { 
                                "$eq": ["$$state.stateCode", "TN" ]
                            }
                        }
                    }
                }
            }
        }
    }
    
])
user_105611910
Related