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

Question

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?

turivishal · Accepted Answer · 2021-05-12 04:47:47Z

2

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

edited May 12, 2021 at 4:47

answered May 12, 2021 at 4:42

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related