How to retrieve document only my condition meet all element in subarray?

my documents:

    {
        "_id" : ObjectId("5234cc89687ea597eabee675"),
        "code" : "xyz",
        "tags" : [ 
            "school", 
            "book", 
            "bag", 
            "headphone", 
            "appliance"
        ],
        "qty" : [ 
            {
                "size" : "S",
                "num" : 10,
                "color" : "blue"
            }, 
            {
                "size" : "M",
                "num" : 45,
                "color" : "blue"
            }, 
            {
                "size" : "M",
                "num" : 60,
                "color" : "blue"
            }, 
            {
                "size" : "L",
                "num" : 100,
                "color" : "green"
            }
        ]
    }
    
    {
        "_id" : ObjectId("5234cc8a687ea597eabee676"),
        "code" : "abc",
        "tags" : [ 
            "appliance", 
            "school", 
            "book"
        ],
        "qty" : [ 
            {
                "size" : "6",
                "num" : 100,
                "color" : "green"
            }, 
            {
                "size" : "6",
                "num" : 50,
                "color" : "blue"
            }, 
            {
                "size" : "8",
                "num" : 100,
                "color" : "brown"
            }
        ]
    }

for this query I retreive this document but I expected no result, because in the array there is an element with the value 60... all elements of the array must meet the condition

    db.getCollection('test').find({
                         qty: { $all: [
                                        { "$elemMatch" : { size: "M", num: { $lt: 50} } }
                                      ] }
                       } )

my result is:

    {
        "_id" : ObjectId("5234cc89687ea597eabee675"),
        "code" : "xyz",
        "tags" : [ 
            "school", 
            "book", 
            "bag", 
            "headphone", 
            "appliance"
        ],
        "qty" : [ 
            {
                "size" : "S",
                "num" : 10,
                "color" : "blue"
            }, 
            {
                "size" : "M",
                "num" : 45,
                "color" : "blue"
            }, 
            {
                "size" : "M",
                "num" : 60,
                "color" : "blue"
            }, 
            {
                "size" : "L",
                "num" : 100,
                "color" : "green"
            }
        ]
    }

Or in this situation only result a items that meet the condition, but not all :

    db.getCollection('test').aggregate([
        { $unwind: '$qty'},
        { $match: {'qty.size': {$eq: "M"}}},
        { $match: {'qty.num': {$lt: 50}}}
    ])

my result is:

    {
        "_id" : ObjectId("5234cc89687ea597eabee675"),
        "code" : "xyz",
        "tags" : [ 
            "school", 
            "book", 
            "bag", 
            "headphone", 
            "appliance"
        ],
        "qty" : {
            "size" : "M",
            "num" : 45,
            "color" : "blue"
        }
    }

CodePudding user response：

You can use $map to create an auxiliary array of booleans to perform the checking on your criteria. Afterwards, use $allElementsTrue to perform the filtering.

db.collection.aggregate([
  {
    "$addFields": {
      "filterArr": {
        "$map": {
          "input": "$qty",
          "as": "q",
          "in": {
            $and: [
              {
                $eq: [
                  "$$q.size",
                  "M"
                ]
              },
              {
                $lt: [
                  "$$q.num",
                  50
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$match": {
      $expr: {
        "$allElementsTrue": "$filterArr"
      }
    }
  },
  {
    "$project": {
      filterArr: false
    }
  }
])

Here is the Mongo playground for your reference.