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
– turivishal, Commented Jul 20, 2021 at 3: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
– rajashekar, Commented Jul 20, 2021 at 4:13
there is confusion, you want to filter states and location both? or only location? — turivishal
– turivishal, Commented Jul 20, 2021 at 4:17

turivishal · Accepted Answer · 2021-07-20 04:54:15Z

$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

varman · Accepted Answer · 2021-07-20 03:55:44Z

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, 2021 at 3:55

varman

8,9326 gold badges25 silver badges56 bronze badges

6 Comments

rajashekar Over a year ago

is map operator inside projection supported by DocumentDB. Because it is a variable operator. I think it is not supported in DocumentDB

varman Over a year ago

Most of the operators are not supported by DocumentDB. I had this issue, so I gave up migrating to DocumentDB

rajashekar Over a year ago

Is there any other way to achieve the required result with the operators which are supported by DocumentDB

varman Over a year ago

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

rajashekar Over a year ago

how to apply a filter for location inside map i.e location should be equal to india

|

Prakash Harvani · Accepted Answer · 2021-07-20 04:16:05Z

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

Collectives™ on Stack Overflow

Filter nested array using projection without using unwind

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related