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

mongodb boolean query
mongodb query unset field
sql boolean example
mongodb field exists
mongodb aggregate match boolean
mongodb two fields equal
mongodb find all documents with field
mongodb delete document where field exists

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?

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

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

MongoDB Query for boolean field as “not true”, The query to create a collection with a document is as follows − > db.​queryForBooleanFieldsDemo.insertOne({"EmployeeName":"Larry"  What is the importance of Boolean class in Java? How to convert a value into Boolean in JavaScript? A Boolean Matrix Question in C++? Increase database field value by specified percentage using user-defined variables in MySQL; MySQL query to add dots if string has more than 10 words? MySQL query to increment one of the column values

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.

PostgresQL BOOLEAN Data Type with Practical Examples, You can imply the true value by using the Boolean column without any operator. The following query returns all products that are available: SELECT * FROM  Actually, the Boolean field in SharePoint works in 1 (TRUE) and o (FALSE). So we have to modify the code like below: SharePoint 2013 caml query boolean field (For True Condition) First we will see how to use caml query in SharePoint 2013/Online for boolean field for true condition.

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)

An Introduction to MySQL BOOLEAN Data Type, Before saving data into the Boolean column, MySQL converts it into 1 or 0. The following query retrieves data from tasks table: SELECT id, title, completed FROM​  This SharePoint tutorial, we will discuss how to use CAML query for boolean fields in the SharePoint client object model (csom) for SharePoint Online site.. We had one document library where we have added one field as “IsCopied” which is of type Boolean.

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

'False' values for boolean field show up as NULL in SQL Runner , I'm having an issue that I traced to this unexpected (by me) behavior: 'False' values for boolean fields show up as NULL in SQL Runner, and of course in query  Query for boolean field as “not true” (e.g. either false or non existent) Ask Question this query will not use the index. So pls don't use in production.

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 

My query on a Boolean field (true/false) returns unexpected results , I set the field to 'True' (or checked) on 3 contacts. I then added a line to a query that said "AND [field] equals false" and expected to see the entirety of the database  To query rich-text fields, see Query by titles, IDs, and rich-text fields. Identity. Short text string that identifies a user identity. Integer. A 32-bit integer that is signed, such as 0, 1, 2, 34. PlainText or Text field (multi-line) Text strings that support long descriptions, such as the Application Start Information field. These fields are automatically indexed for full-text search, when full-text search is available.

Query Using a Boolean Field in the Where clause, I was trying to write a simple query to get those records where the boolean field was not set to true. In the table, the field was set to true or it was  A boolean in SQL is a bit field. This means either 1 or 0. The correct syntax is: In most DBs, fields can also be . You may need to check for NULL as well if you don't configure the table with a default value for the active field. using SQLite within Rails (4) it made queries using 'f' or 't' (not as chars though).

Boolean datatype | Elasticsearch Reference [7.6], boost. Mapping field-level query time boosting. Accepts a floating point number, defaults to 1.0 . doc_values. Should the field be stored on disk in a column-stride​  How to return a boolean value on SQL Select Statement? I tried this code: SELECT CAST(1 AS BIT) AS Expr1 FROM [User] WHERE (UserID = 20070022) And it only returns TRUE if the UserID exists on the table. I want it to return FALSE if the UserID doesn't exist on the table.

Query vs Writing Booleans & Checking Field Types, Do you know the difference between querying and writing booleans? Read this week's tech tips on how to write acceptable boolean syntax. standard if all fields in the query looked like the real field "Paid" BTW - impressed with the crosstab feature in Access better than other DB's I've used.- Hide quoted text - - Show quoted text - open the query right click anywhere and select properties from the list of fields that make up the query, hilight the whole 'internet' column

Comments
  • According to the docs, using $ne doesn't appear to utilize an index - is there another solution that would be able to take advantage of an index? docs.mongodb.org/manual/faq/indexes/…
  • Just to re-iterate for other readers - this query will not use the index. So pls don't use in production.
  • So what should be used in production instead?
  • @Juhana I just tested this with MongoDB 2.6.6 and it does now use an index created for {deleted: 1}.
  • Did not index correctly under 3.6.8 with compound indexes, but $in: [false,null] seemed to work.
  • When I do an explain on this query it shows the index is only used for the 'deleted:false' portion of the query. The 'deleted:{$exists:false}' does not seem to use the index.
  • @emilebaizel $exists should available for indexing. Older versions of Mongo <2.5.5 may not have that feature. jira.mongodb.org/browse/SERVER-10608
  • aha! that's probably it. we're on 2.4.