CODEWITHSUNDEEP
mongodbmongodb-query
Alvin
Alvin

Reputation: 8499

mongoose "and or" return no result

I want to find a record that match with either name or passport and the record is not suspended:

Person.find({'$or': [{name: name},
                     {passportNo: passportNo}
                    ], 
             "$and":[{suspended: false}]
            }, function(err, result){

I get no record, am I doing it correctly?

Upvotes: 0

Views: 72

Answers (2)

wdberkeley
wdberkeley

Reputation: 11671

If the problem is

Find all documents where (name is X or passportNo is Y) and (suspended is false or not present)

use the following query:

{
    "$and" : [
        { "$or" : [
            { "name" : X }, 
            { "passportNo" : Y } 
        ] }, 
        { "$or" : [
            { "suspended" : false }, 
            { "suspended" : { "$exists" : false } } 
        ] }
    ]
}

You should simplify your life by setting suspended to false by default, though, so the query can just be

{
    { "$or" : [{ "name" : X }, { "passportNo" : Y } ] }, 
    { "suspended" : false }, 
}

Upvotes: 1

Kevin Brady
Kevin Brady

Reputation: 1724

The $and statement should be at the start and should contain the two separate conditions (record is not suspended) AND (OR statement where name or passport matches)

{"$and":[{suspended: false}, {'$or': [{name: name}, {passportNo: passportNo}]}] }

*****Update

Here is an updated statement based on the following logic
(name field matches AND suspended is false) OR (passportNo field matches AND suspended is false)

{"$or":[{'$and': [{name: name}, {suspended: false}]}, {'$and': [{passportNo: passportNo}, {suspended: false}]}] }

does this match the logic you are looking for?

****** Update 2

This query is now checking if
(the suspended field exists AND its value is false AND the name or passport field matches)
OR (the suspended field does not exist AND the name or passport field matches)

   {
'$or': [
   {"$and":[
            { "suspended": { $exists: true, $in: [ false ] } }, 
            {'$or': [{"name": name}, {"passportNo": passportNo}]} ]
     },
    {"$and":[
            { "suspended": { $exists: false } } ,
            {'$or': [{"name": name}, {"passportNo": passportNo}]} ]
     } ]
}

Upvotes: 1

Related Questions