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

Question

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 ?

Tom Slabbaert · Accepted Answer · 2021-09-23 07:42:42Z

1

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

answered Sep 23, 2021 at 7:42

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jagadeesh Over a year ago

It works for latest verion.. but not workingfor my mongo version 3.2.

Tom Slabbaert Over a year ago

You can try this without $expr.

Jagadeesh Over a year ago

"Unrecognized pipeline stage name: '$addFields'", version 3.4

ttrasn · Accepted Answer · 2021-09-23 06:41:15Z

0

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

answered Sep 23, 2021 at 6:41

ttrasn

4,8975 gold badges34 silver badges49 bronze badges

1 Comment

Jagadeesh Over a year ago

No the posted query doent return any records.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related