Mongodb get all documents in object array with only same values that is passed-CodePudding

I have the below document in my collection.

{ "_id" : ObjectId("ObjectId"), "MobNo" : "23232", "data" : [ { "status" : "PASS" }, { "status" : "PASS" } ] }
{ "_id" : ObjectId("ObjectId"), "MobNo" : "232323", "data" : [ { "status" : "PASS" }, { "status" : "FAIL" } ] }
{ "_id" : ObjectId("ObjectId"), "MobNo" : "32322", "data" : [ { "status" : "PASS" }, { "status" : "PASS" } ] }

I want a query to return all objects in data array not to have PASS status(All the objects in the array should NOT have the status as "PASS").

My Expected output here is

{ "_id" : ObjectId("ObjectId"), "MobNo" : "232323", "data" : [ { "status" : "PASS" }, { "status" : "FAIL" } ] }

My query for the above result :

db.Collection.aggregate({
    $match: {
        "data": {
            $exists: true, $not: { $size: 0 }, $all: [
                { "$elemMatch": { "status": { $nin: ["PASS"] } } },
            ]
        },
    }
})

Now i want to my collection to return documents with data array having all object status as passed .

My expected output :

{ "_id" : ObjectId("ObjectId"), "MobNo" : "23232", "data" : [ { "status" : "PASS" }, { "status" : "PASS" } ] }

{ "_id" : ObjectId("ObjectId"), "MobNo" : "32322", "data" : [ { "status" : "PASS" }, { "status" : "PASS" }

How can i get the above output, my below query doesnt work and it return even the failure data array.

db.Collection.aggregate({
    $match: {
        "data": {
            $exists: true, $not: { $size: 0 }, $all: [
                { "$elemMatch": { "status": { $in: ["PASS"] } } },
            ]
        },
    }
})

Output of the above query :

{ "MobNo" : "23232", "data" : [ { "status" : "PASS" }, { "status" : "PASS" } ] },
    { "MobNo" : "232323", "data" : [ { "status" : "PASS" }, { "status" : "FAIL" } ] },
    { "MobNo" : "32322", "data" : [ { "status" : "PASS" }, { "status" : "PASS" } ] }

I do not wanted the below record in my above query output:

{ "MobNo" : "232323", "data" : [ { "status" : "PASS" }, { "status" : "FAIL" } ] },

Any help ?

CodePudding user response：

You can do this in several different ways, here is the most straight forward in my opinion using $size and $filter to see if any "bad" documents exist in the array.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $eq: [
          {
            $size: {
              $filter: {
                input: "$data",
                cond: {
                  $ne: [
                    "$$this.status",
                    "PASS"
                  ]
                }
              }
            }
          },
          0
        ]
      },
      "data.0": {
        $exists: true
      }
    }
  }
])

Mongo Playground

CodePudding user response：

Cause your status values are in the array field (data), so you must to $unwind it, then query in it, see this:

db.Collection.aggregate({
    $unwind:{  path: "$data"},
    $match: {
        "data": {
            $exists: true, $not: { $size: 0 }, $all: [
                { "$elemMatch": { "status": { $in: ["PASS"] } } },
            ]
        },
    }
})