Search code examples
mongodbnullmongodb-queryexists

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

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?

Solution

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