Select MongoDB documents where a field either does not exist, is null, or is false?

MongodbMongodb QueryDatabase

Mongodb Problem Overview


Suppose I have a collection that contains the following documents:

{ "_id": 1, name: "Apple" }
{ "_id": 2, name: "Banana", "is_reported": null }
{ "_id": 3, name: "Cherry", "is_reported": false }
{ "_id": 4, name: "Kiwi",   "is_reported": true }

Is there a simpler query to select all documents where "is_reported" is in a falsy state; that is, either non-existent, null, or false? That is, a query that selects Apple, Banana, and Cherry, but not Kiwi?

According to the MongoDB FAQ, { "is_reported": null } will select documents where "is_reported" is either null or nonexistent, but it still doesn't select documents where "is_reported" is false.

Right now I have the following query, which works fine, but it just doesn't seem very elegant. If there are multiple fields that I need to select on, it gets messy very fast. Is there a better query that achieves the same end result?

db.fruits.find({ $or: [ { "is_reported": null }, { "is_reported": false } ] })

Mongodb Solutions


Solution 1 - Mongodb

You can do this with $in:

db.fruits.find({is_reported: {$in: [null, false]}})

returns:

{
  "_id": 1,
  "name": "Apple"
}
{
  "_id": 2,
  "name": "Banana",
  "is_reported": null
}
{
  "_id": 3,
  "name": "Cherry",
  "is_reported": false
}

You could also flip things around logically and use $ne if you don't have any values besides true to exclude:

db.fruits.find({is_reported: {$ne: true}})

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionsffcView Question on Stackoverflow
Solution 1 - MongodbJohnnyHKView Answer on Stackoverflow