Return only matched sub-document elements within a nested array

user_2396835 0
Viewed 100061

The main collection is retailer, which contains an array for stores. Each store contains an array of offers (you can buy in this store). This offers array has an array of sizes. (See example below)

Now I try to find all offers, which are available in the size L.

{
    "_id" : ObjectId("56f277b1279871c20b8b4567"),
    "stores" : [
        {
        "_id" : ObjectId("56f277b5279871c20b8b4783"),
        "offers" : [
            {
                "_id" : ObjectId("56f277b1279871c20b8b4567"),
                "size": [
                    "XS",
                    "S",
                    "M"
                ]
            },
            {
                "_id" : ObjectId("56f277b1279871c20b8b4567"),
                "size": [
                    "S",
                    "L",
                    "XL"
                ]
            }
        ]
    }
}

I've try this query: db.getCollection('retailers').find({'stores.offers.size': 'L'})

I expect some Output like that:

 {
"_id" : ObjectId("56f277b1279871c20b8b4567"),
"stores" : [
    {
        "_id" : ObjectId("56f277b5279871c20b8b4783"),
        "offers" : [
            {
                "_id" : ObjectId("56f277b1279871c20b8b4567"),
                "size": [
                    "S",
                    "L",
                    "XL"
                ]
            }
        ]
    }
}

But the Output of my Query contains also the non matching offer with size XS,X and M.

How I can force MongoDB to return only the offers, which matched my query?

Greetings and thanks.

aggregation-framework mongodb-query mongodb
3 Answers

It's also works without aggregate. here is the solution link:https://mongoplayground.net/p/Q5lxPvGK03A

db.collection.find({
  "stores.offers.size": "L"
},
{
  "stores": {
    "$filter": {
      "input": {
        "$map": {
          "input": "$stores",
          "as": "store",
          "in": {
            "_id": "$$store._id",
            "offers": {
              "$filter": {
                "input": "$$store.offers",
                "as": "offer",
                "cond": {
                  "$setIsSubset": [
                    [
                      "L"
                    ],
                    "$$offer.size"
                  ]
                }
              }
            }
          }
        }
      },
      "as": "store",
      "cond": {
        "$ne": [
          "$$store.offers",
          []
        ]
      }
    }
  }
})
user_83146420
Related