MongoDB: How to query for records where field is null or not set?
MongodbNullMongodb QueryExistsMongodb Problem Overview
I have an Email
document which has a sent_at
date field:
{
'sent_at': Date( 1336776254000 )
}
If this Email
has not been sent, the sent_at
field is either null, or non-existant.
I need to get the count of all sent/unsent Emails
. I'm stuck at trying to figure out the right way to query for this information. I think this is the right way to get the sent count:
db.emails.count({sent_at: {$ne: null}})
But how should I get the count of the ones that aren't sent?
Mongodb Solutions
Solution 1 - Mongodb
If the sent_at
field is not there when its not set then:
db.emails.count({sent_at: {$exists: false}})
If it's there and null, or not there at all:
db.emails.count({sent_at: null})
If it's there and null:
db.emails.count({sent_at: { $type: 10 }})
The Query for Null or Missing Fields section of the MongoDB manual describes how to query for null and missing values.
> ## Equality Filter
>
> The { item : null }
query matches documents that either contain the item field whose value is null
or that do not contain the item
field.
>
> lang-javascript > db.inventory.find( { item: null } ) >
> ## Existence Check
>
> The following example queries for documents that do not contain a field.
>
> The { item : { $exists: false } }
query matches documents that do not contain the item
field:
>
> lang-javascript > db.inventory.find( { item : { $exists: false } } ) >
> ## Type Check
>
> The { item : { $type: 10 } }
query matches only documents that contain the item
field whose value is null
; i.e. the value of the item field is of BSON Type Null
(type number 10
) :
>
> lang-javascript > db.inventory.find( { item : { $type: 10 } } ) >
Solution 2 - Mongodb
If you want to ONLY count the documents with sent_at
defined with a value of null
(don't count the documents with sent_at
not set):
db.emails.count({sent_at: { $type: 10 }})
Solution 3 - Mongodb
Use:
db.emails.count({sent_at: null})
Which counts all emails whose sent_at property is null or is not set. The above query is same as below.
db.emails.count($or: [
{sent_at: {$exists: false}},
{sent_at: null}
])
Solution 4 - Mongodb
Seems you can just do single line:
{ "sent_at": null }
Solution 5 - Mongodb
All the above answers are amazing and correct. Just want to add one improvement on the answers which is using $type
.
Starting with MongoDB 3.2, you can avoid using 10
(as hardcoded literals reduce the code readability) and instead can simply use string aliases i.e. "null"
. So to summarize-
sent_at
exists and has the value null
1. If you want to pick records where db.emails.count({sent_at: { $type: 'null' }});
// or
// This reduces the code readability as your peer might not know
// that what is the meaning of value 10
db.emails.count({sent_at: { $type: 10 }});
sent_at: null
or sent_at
does not exist
2. If you want to pick records which has either // This will ensure both the conditions
db.emails.count({ sent_at: null })
sent_at
does not exist
3. If you only want records where db.emails.count({sent_at: { $exists: false }});
sent_at
where the field exists and may have any value
4. If you only want to pick db.emails.count({sent_at: { $exists: true }});
Remember, this will pull any document of emails
which has any value including null
, 0
, ''
, false
.
Solution 6 - Mongodb
You can also try this:
db.emails.find($and:[{sent_at:{$exists:true},'sent_at':null}]).count()
Solution 7 - Mongodb
you can use $in operator to check both cases
db.emails.find({"sent_at": {$in:[null,""]}).count()
Solution 8 - Mongodb
If it is required t check existence of attribute and value equals null in the same document as-
db.emails.count($or: [
{sent_at: {$exists: false}},
{sent_at: null}
])
Above can be done in a single line-query which will take lesser time as-
db.emails.count($or: [ {sent_at: nil }])
because nil
will fetch documents if the key doesn't exist
too in addition to value is null
.
Better to have a glance on source it has saved my day.
> Note: Use nil
instead of null
in newer version of mongodb.
Solution 9 - Mongodb
db.employe.find({ $and:[ {"dept":{ $exists:false }, "empno": { $in:[101,102] } } ] }).count();