Mongodb document object array filter-CodePudding

How can I filter if all num of qty greater then 50 then this is return otherwise not:

this is examples 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: "L", num: 100, color: "green" }
    ]
 }

{
  _id: ObjectId("5234cc8a687ea597eabee676"),
  code: "abc",
  tags: [ "appliance", "school", "book" ],
  qty: [
      { size: "6", num: 100, color: "green" },
      { size: "6", num: 70, color: "blue" },
      { size: "8", num: 100, color: "brown" }
    ]
 }

 {
  _id: ObjectId("5234ccb7687ea597eabee677"),
  code: "efg",
  tags: [ "school", "book" ],
  qty: [
      { size: "S", num: 10, color: "blue" },
      { size: "M", num: 100, color: "blue" },
      { size: "L", num: 100, color: "green" }
    ]
  }

Is it possiblt with mongodb query? how?

I have tried many ways but didnt work. please help

I expected:

 {
   _id: ObjectId("5234cc8a687ea597eabee676"),
  code: "abc",
  tags: [ "appliance", "school", "book"],
  qty: [
      { size: "6", num: 100, color: "green" },
      { size: "6", num: 70, color: "blue" },
      { size: "8", num: 100, color: "brown" }
    ]
 }

  {
  _id: ObjectId("52350353b2eff1353b349de9"),
  code: "ijk",
   tags: [ "electronics", "school" ],
 qty: [
      { size: "M", num: 100, color: "green" }
    ]
 }

CodePudding user response：

If I understood your request correctly you would like to filter on the qty.num field right? If so, use this aggregation pipeline:

var options = {
    allowDiskUse: false
};
var pipeline = [
    {
        "$unwind": {
            "path": "$qty"
        }
    }, 
    {
        "$match": {
            "qty.num": {
                "$gte": 50.0
            }
        }
    }, 
    {
        "$group": {
            "_id": "$_id",
            "code": {
                "$first": "$code"
            },
            "tags": {
                "$first": "$tags"
            },
            "qty": {
                "$push": "$qty"
            }
        }
    }
];

var cursor = collection.aggregate(pipeline, options);

I hope this helps. It works for me.

CodePudding user response：

It is confusing as the record with _id: ObjectId("52350353b2eff1353b349de9") does not appear in your sample dataset. Nevertheless, from your description, you may be looking for $allElementsTrue. You can use $map to apply your > 50 criteria to the qty array and feed the result to $allElementsTrue.

db.collection.find({
  $expr: {
    $allElementsTrue: {
      $map: {
        input: "$qty",
        as: "q",
        in: {
          $gt: [
            "$$q.num",
            50
          ]
        }
      }
    }
  }
})

Mongo Playground