CODEWITHSUNDEEP
mongodb
Eran Medan
Eran Medan

Reputation: 45775

Query for boolean field as "not true" (e.g. either false or non existent)

I'm sure I'm missing something very basic in MongoDB queries, can't seem to get this simple condition.

Consider this collection 

> db.tests.find()
{ "_id" : ObjectId("..."), "name" : "Test1" , "deleted" : true}
{ "_id" : ObjectId("..."), "name" : "Test2" , "deleted" : false}
{ "_id" : ObjectId("..."), "name" : "Test3" }

I would simply like to query all the items that are "not deleted"

I know how to find the item that has a "deleted" flag set to true: 

> db.tests.find({deleted:true})
{ "_id" : ObjectId("..."), "name" : "Test1" , "deleted" : true}

But how do I find all items that are NOT "deleted" (e.g. negate the above query, or in other words, any items that either doesn't have a "deleted" field, or have it with value false

What I tried by guessing (please don't laugh...)

> db.tests.find({$not : {deleted: true}})

(returns no results)

> db.tests.find({$not : {$eq:{deleted:true}}})

error: { "$err" : "invalid operator: $eq", "code" : 10068 }

> db.tests.find({deleted:{$not: true}})

error: { "$err" : "invalid use of $not", "code" : 13041 }

> db.tests.find({deleted:{$not: {$eq:true}}})

error: { "$err" : "invalid use of $not", "code" : 13034 }

What am I missing?

Upvotes: 107

Views: 95596

Answers (5)

Jon Kern
Jon Kern

Reputation: 3235

In case you are looking for mongoid syntax (I am using this in a rails app), this is what I came up with for a company's users:

2.3.1 :042 > accepted_consent = org.users.active.where(:accepted_terms_and_conditions => true).count
 => 553 
2.3.1 :043 > not_accepted_yet = org.users.active.where(:accepted_terms_and_conditions.ne => true).count
 => 6331 
2.3.1 :044 > 6331+553
 => 6884 
2.3.1 :045 > org.users.active.count
 => 6884

Upvotes: 0

JohnPan
JohnPan

Reputation: 1210

For the case that someone needs this in an aggregation pipeline instead of find, this is what worked for me

db.getCollection('tests').aggregate([ 
  // ...previous operations...
  { $addFields: { "deleted_conclusion": { $cond: {
        if:{ $ne: [ "$deleted", false ]}, then: { $cond: [ "$deleted", ":TRUE", ":FALSY"]}, else: ":FALSE"
  }}}}
])

After adding the extra field you can go on with pipeline stages and have the information you miss

Upvotes: 1

tidwall
tidwall

Reputation: 6949

JohnnyHK has the best answer. The $in selector is the shortest and cleanest IMO.

This will test for exactly "false" or "non existent". And can be indexed.

db.tests.find({$or:[{deleted:false},{deleted:{$exists:false}}]})

An example with a use of an index.

((function(){
    print("creating collection 'testx' and inserting 50 trues, 50 falses, 50 non-existents");
    db.testx.drop();
    db.testx.ensureIndex({deleted:1});
    for (var i=0;i<50;i++){
        db.testx.insert({i:i,deleted:false});
    };
    for (var i=0;i<50;i++){
        db.testx.insert({i:i,deleted:true});
    };
    for (var i=0;i<50;i++){
        db.testx.insert({i:i});
    };
    var res0 = db.testx.find().explain();
    var res1 = db.testx.find({deleted:false}).explain();
    var res2 = db.testx.find({deleted:true}).explain();
    var res3 = db.testx.find({deleted:{$exists:false}}).explain();
    var res4 = db.testx.find({$or:[{deleted:false},{deleted:{$exists:false}}]}).explain();
    var res5 = db.testx.find({$or:[{deleted:true},{deleted:{$exists:false}}]}).explain();
    var res6 = db.testx.find({deleted:{$in:[false,null]}}).explain();
    print("res0: all objects                      ("+res0["n"]+" found, "+res0["nscannedObjects"]+" scanned)");
    print("res1: deleted is false                 ("+res1["n"]+" found, "+res1["nscannedObjects"]+" scanned)");
    print("res2: deleted is true                  ("+res2["n"]+" found, "+res2["nscannedObjects"]+" scanned)");
    print("res3: deleted is non-existent          ("+res3["n"]+" found, "+res3["nscannedObjects"]+" scanned)");
    print("res4: deleted is false or non-existent ("+res4["n"]+" found, "+res4["nscannedObjects"]+" scanned)");
    print("res5: deleted is true or non-existent  ("+res5["n"]+" found, "+res5["nscannedObjects"]+" scanned)");
    print("res6: deleted is in [false,null]       ("+res5["n"]+" found, "+res5["nscannedObjects"]+" scanned)");
})())

This should print

creating collection 'testx' and inserting 50 trues, 50 falses, 50 non-existents
res0: all objects                      (150 found, 150 scanned)
res1: deleted is false                 (50 found, 50 scanned)
res2: deleted is true                  (50 found, 50 scanned)
res3: deleted is non-existent          (50 found, 50 scanned)
res4: deleted is false or non-existent (100 found, 100 scanned)
res5: deleted is true or non-existent  (100 found, 100 scanned)
res6: deleted is in [false,null]       (100 found, 100 scanned)

Upvotes: 8

JohnnyHK
JohnnyHK

Reputation: 312149

For the sake of completeness, another way to do this is with $in:

db.test.find({deleted: {$in: [null, false]}})

Including null in the array pulls in the docs where the deleted field is missing. This query can use an index on {deleted: 1} in the current 2.6.6 MongoDB release.

Upvotes: 43

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230551

db.tests.find({deleted: {$ne: true}})

Where $ne stands for "not equal". (Documentation on mongodb operators)

Upvotes: 196

Related Questions