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.

there is no `location` field in collection1, what is in the $match stage `{ "location": "india" }`? — turivishal, Jul 20 '21 at 03:42
Only thing is I should be able to filter nested arrays and all the oprators used in the query should also work in DocumentDB — rajashekar, Jul 20 '21 at 04:13
there is confusion, you want to filter states and location both? or only location? — turivishal, Jul 20 '21 at 04:17

turivishal · Accepted Answer · 2021-07-20T04:54:15.697

$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

score 2 · Answer 2 · answered Jul 20 '21 at 03:55

2

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

answered Jul 20 '21 at 03:55

varman

8,704
5
19
53

is map operator inside projection supported by DocumentDB. Because it is a variable operator. I think it is not supported in DocumentDB – rajashekar Jul 20 '21 at 04:20
1

Most of the operators are not supported by DocumentDB. I had this issue, so I gave up migrating to DocumentDB – varman Jul 20 '21 at 04:22
Is there any other way to achieve the required result with the operators which are supported by DocumentDB – rajashekar Jul 20 '21 at 04:29
1

I will try, most of the important operator are not supported. I advise you not to look into the temporary solution, Sometimes you need more operators in future. then you will be facing difficulties. There is another solution, you can get the documents after the lookup stage and do you work programmatically using for loops and if conditions – varman Jul 20 '21 at 04:50
how to apply a filter for location inside map i.e location should be equal to india – rajashekar Jul 20 '21 at 08:54
use `match` https://mongoplayground.net/p/JK7NliDzkxY – varman Jul 20 '21 at 09:02

score -1 · Answer 3 · answered Jul 20 '21 at 04:16

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" ]
                            }
                        }
                    }
                }
            }
        }
    }
    
])

Filter nested array using projection without using unwind

3 Answers3