MongoDB: How to query for records where field is null or not set?

Question

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?

Answer 1

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.

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:

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) :

db.inventory.find( { item : { $type: 10 } } )