CODEWITHSUNDEEP
mongodbmongodb-query
Phil
Phil

Reputation: 13

MongoDB Exclude documents from the find result based on the values of a subdocument

I have collection of products. Each product contains an array of history items.

{
    "_id" : "product1"
    "name" : "Product 1",
    "history" : [
        {
                "date" : "2017-09-18 11:49:03.000",
                "processed" : 0,
                "status": "New"
        },
        {
                "date" : "2017-09-18 11:49:03.000",
                "processed" : 0,
                "diff": []
        }
    ]
}
{
    "_id" : "product2"
    "name" : "Product 2",
    "history" : [
        {
                "date" : "2017-09-18 11:49:03.000",
                "processed" : 1,
                "status": "New"
        },
        {
                "date" : "2017-09-18 11:49:03.000",
                "processed" : 0,
                "diff": []
        }
    ]
}

I can find all the products that are new and have history that hasn't been processed by using the following mongoDB query:

db.products.find(
    {
        "history": {
            $elemMatch: {
                "processed": {$eq: 0},
                "status": {$eq: "New"}
            }
        }
    }
)

But I can't figure out how to find the ones where:

So it should bring back "product2" and not "product1" as product1 needs to be handled differently.

Any help is greatly appreciated :-)

Upvotes: 1

Views: 1220

Answers (1)

Ricky
Ricky

Reputation: 155

you are getting only product 1 in output because you are doing an and search for both processed and status fields which match only product 1. If you want product 2 in out as well based only on processed field then you need to use $or query.

Check following query it will return both product 1 and product 2

{
     "history": {
         $elemMatch: {
            $or:[
              {"processed": {$eq: 0}},
              {"status": {$eq: "New"}}]
         }
     }
}

Updated the query based on your explanation in comments. 

.find({
    $and:[{
        "history": {
            $not: {
                $elemMatch: {
                    "processed": {$eq: 0},
                    "status":{$eq: "New"}
                }
            }
        }
    },
    {"history.processed":0}
]})

Upvotes: 1

Related Questions