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

Answer 2

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

Answer 3

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}
])

Answer 4

Seems you can just do single line:

{ "sent_at": null }

Answer 5

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-

1. If you want to pick records where sent_at exists and has the value null

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 }});

2. If you want to pick records which has either sent_at: null or sent_at does not exist

// This will ensure both the conditions
db.emails.count({ sent_at: null })

3. If you only want records where sent_at does not exist

db.emails.count({sent_at: { $exists: false }});

4. If you only want to pick sent_at where the field exists and may have any value

db.emails.count({sent_at: { $exists: true }});

Remember, this will pull any document of emails which has any value including null, 0, '', false.

Answer 6

You can also try this:

db.emails.find($and:[{sent_at:{$exists:true},'sent_at':null}]).count()

Answer 7

you can use $in operator to check both cases

db.emails.find({"sent_at": {$in:[null,""]}).count()

Answer 8

db.employe.find({ $and:[ {"dept":{ $exists:false }, "empno": { $in:[101,102] } } ] }).count();