How can I query for “falsey” values in MongoDB?

Question

I want to query a Mongo collection for documents where a specific field is either missing or has a value that would evaluate to false in Python. This includes atomic values null, 0, '' (the empty string), false, []. However, arrays containing such values (such as ['foo', ''] or just ['']) are not falsey and must not be matched. Can I do this with Mongo’s structured queries (without resorting to JavaScript)?

$type doesn’t seem to help:

> db.foo.insert({bar: ['baz', '', 'qux']});
> db.foo.find({$and: [{bar: ''}, {bar: {$type: 2}}]});
{ "_id" : ObjectId("50599937da5254d6fd731816"), "bar" : [ "baz", "", "qux" ] }

Answer 1

This should work

db.test.find({$or:[{a:{$size:0}},{"a.0":{$exists:true}}]})

Just make sure the a field doesn't have an object inside with the 0 key.

e.g.

> db.test.find()

{ "_id": ObjectId("5059ac3ab1cee080a7168fff"), "bar": [ "baz", "", "qux" ] }
{ "_id": ObjectId("5059ac48b1cee080a7169000"), "hello": 1, "bar": false, "world": 34 }
{ "_id": ObjectId("5059ac53b1cee080a7169001"), "hello": 1, "world": 42 }
{ "_id": ObjectId("5059ac60b1cee080a7169002"), "hello": 13, "bar": null, "world": 34 }
{ "_id": ObjectId("5059ac6bb1cee080a7169003"), "hello": 133, "bar": [ ], "world": 334 }
{ "_id": ObjectId("5059b36cb1cee080a7169004"), "hello": 133, "bar": [ "" ], "world": 334 }
{ "_id": ObjectId("5059b3e3b1cee080a7169005"), "hello": 133, "bar": "foo", "world": 334 }
{ "_id": ObjectId("5059b3f8b1cee080a7169006"), "hello": 1333, "bar": "", "world": 334 }
{ "_id": ObjectId("5059b424b1cee080a7169007"), "hello": 1333, "bar": { "0": "foo" }, "world": 334 }

> db.test.find({$or: [{bar: {$size: 0}}, {"bar.0": {$exists: true}}]})

{ "_id": ObjectId("5059ac3ab1cee080a7168fff"), "bar": [ "baz", "", "qux" ] }
{ "_id": ObjectId("5059ac6bb1cee080a7169003"), "hello": 133, "bar": [ ], "world": 334 }
{ "_id": ObjectId("5059b36cb1cee080a7169004"), "hello": 133, "bar": [ "" ], "world": 334 }
{ "_id": ObjectId("5059b424b1cee080a7169007"), "hello": 1333, "bar": { "0": "foo" }, "world": 334 }

Answer 2

I found this: https://jira.mongodb.org/browse/SERVER-1854?page=com.atlassian.jira.plugin.system.issuetabpanels:changehistory-tabpanel from back in the day.

I can replicate it on my MongoDB 2.0.1 (I also played around a bit to see if it picked it up as something else):

> db.g.find()
{ "_id" : ObjectId("50599eb65395c82c7a47d124"), "bar" : [ "baz", "", "qux" ] }
{ "_id" : ObjectId("5059a0005395c82c7a47d125"), "a" : 3, "b" : { "a" : 1, "b" : 2 } }
> db.g.find({bar: {$type: 4}});
> db.g.find({a: {$type: 2}});
> db.g.find({a: {$type: 16}});
> db.g.find({bar: {$type: 16}});
> db.g.find({bar: {$type: 2}});
{ "_id" : ObjectId("50599eb65395c82c7a47d124"), "bar" : [ "baz", "", "qux" ] }
> db.g.find({bar: {$type: 3}});
> db.g.find({b: {$type: 3}});
{ "_id" : ObjectId("5059a0005395c82c7a47d125"), "a" : 3, "b" : { "a" : 1, "b" : 2 } }

And when I use $type 4 I cannot get the document with the array in. As you can see $type 3 works fine (Which could be related to: https://jira.mongodb.org/browse/SERVER-1475) but the array cannot seem to be picked up.

It is possible that you are seeing a bug. If you file a JIRA on MongoDBs site (jira.mongodb.org) it could help to solve the problem.

However, though, the $type op might not solve your problem. This might be better done via a client side method like unsetting the field totally if it has no elements that way you query for existance. This standardises your querying patterns and makes it easier to integrate in general.

So my personal recommendation here is to standardise "falsey" values into one single conherrent value.

Edit

I noticed they have marked the original bug as a duplicate (that's why it is closed) however I am not sure how it is a duplicate. These arrays are not being picked up as objects but rather as strings, most likely since $type is acting on every element within that field rather than the field itself (or something like that).

I would still open a JIRA and stress that the array cannot be picked up at all.