CODEWITHSUNDEEP
mongodbmongodb-query
rawat
rawat

Reputation: 165

MongoDB find query using $and and $nor operator

I have a collection as below

{
    "_id" : ObjectId("55bec0793bed809b2cb658ad"),
    "lock_1" : ["desc_1","desc_2"],
    "lock_2" : ["desc_1"]
},
{
    "_id" : ObjectId("55bec0793bed809b2cb658ab"),
    "lock_1" : ["desc_1","desc_2","desc_3"],
    "lock_2" : ["desc_1"]
}
{
    "_id" : ObjectId("55bec0793bed809b2cb658ac"),
    "lock_1" : ["desc_1"],
    "lock_2" : []
},
{
    "_id" : ObjectId("55bec0793bed809b2cb658ae"),
    "lock_1" : [],
    "lock_2" : ["desc_1"]
},
{
    "_id" : ObjectId("55bec0793bed809b2cb658aj"),
    "lock_1" : ["desc_3","desc_4"],
    "lock_2" : ["desc_5"]
},
{
    "_id" : ObjectId("55bec0793bed809b2cb658ak"),
    "lock_1" : [],
    "lock_2" : []
}

I have retrieved all the documents having "desc_1" in both "lock_1" and "lock_2" array by using below query, returning first two documents, which is correct.

db.locks.find( { $and: [ {lock_1 : "desc_1"} , {lock_2 : "desc_1"} ] } )

Now, I am trying to get documents which does not satisfy above condition. I tried using below query but it is returning last two documents.

db.locks.find( { $nor: [ {lock_1 : "desc_1"} , {lock_2 : "desc_1"} ] } )

How to retrieve documents where "desc_1" is either not present or present in one of the arrays?

Upvotes: 1

Views: 1088

Answers (2)

sergiuz
sergiuz

Reputation: 5529

db.locks.find({ 
    $or: [ 
        { lock_1 : { $nin: [ "desc_1" ] }} , 
        { lock_2 : { $nin: [ "desc_1" ] }} 
    ]
})

reference: $or $nin

Upvotes: 1

Sede
Sede

Reputation: 61225

What you need here is the $or operator which performs a logical OR operation and the $ne operator to test the contents of fields directly.

db.locks.find({ 
    "$or": [ 
        { "lock_1": { "$ne": "desc_1" } },  
        { "lock_2": { "$ne": "desc_1" } } 
    ] 
})

Upvotes: 1

Related Questions