CODEWITHSUNDEEP
mongodb
Milan
Milan

Reputation: 182

How to perform AND query in inner array document in MongoDB?

I want to get all document where (B.arrayC.X == 128 AND B.arrayC.Y == 'PENDING')

Collection records are like below which contains an Array of Embedded Documents.

{
    "_id" : NumberLong(1),
    "A" : NumberLong(0),
    "B" : {
        "arrayC" : [ 
            {
                "X" : NumberLong(128),
                "Y" : "COMPLETED",
                "Z" : NumberLong(50)
            }, 
            {
                "X" : NumberLong(109),
                "Y" : "PENDING",
                "Z" : NumberLong(0)
            }
        ]
    }
},
{
    "_id" : NumberLong(2),
    "A" : NumberLong(0),
    "B" : {
        "arrayC" : [ 
            {
                "X" : NumberLong(128),
                "Y" : "PENDING",
                "Z" : NumberLong(50)
            }, 
            {
                "X" : NumberLong(109),
                "Y" : "PENDING",
                "Z" : NumberLong(0)
            }
        ]
    }
}

I have tried the below queries, all queries are returning none PENDING jobs.

db.getCollection('demo').find({"B.arrayC.X" : 128, "B.arrayC.Y" : "PENDING"})

db.getCollection('demo').find({"$and":[{"B.arrayC.X" : 128, "B.arrayC.Y" : "PENDING"}])

db.getCollection('demo').find({"$and":[{"B.arrayC.X" : 128}, {"B.arrayC.Y" : "PENDING"}])

What I understand is MongoDB returning _id 1 record due to B.arrayC.X record with 109 id have "PENDING" state in it. What should be the correct query?

Upvotes: 1

Views: 37

Answers (1)

turivishal
turivishal

Reputation: 36154

What I understand is MongoDB returning _id 1 record due to B.arrayC.X record with 109 id have "PENDING" state in it. What should be the correct query?

As per your try both the field's conditions are separate and it will result document if X satisfied condition in any element of arrayC and if Y satisfied condition in any element of arrayC,

Try $elemMatch to match all the fields in every single element,

db.getCollection('demo').find({
  "B.arrayC": {
    $elemMatch: {
      X: 128,
      Y: "PENDING"
    }
  }
})

Playground

Upvotes: 2

Related Questions