CODEWITHSUNDEEP
mongodbnullmongodb-queryexists
Andrew
Andrew

Reputation: 238647

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?

Upvotes: 161

Views: 233106

Answers (10)

yoshigev
yoshigev

Reputation: 45

To get a count of all the documents which have the sent_at field either null or not-existing, the following short query can be used:

db.emails.count({ sent_at: { $not: { $ne: null } } })

Explanation:

  1. Using { $ne: null } queries for all the documents which have a value in the field (in your example, emails that were sent).
  2. Adding $not over it, queries for the opposite: all the documents which doesn't have a value in the field (in your example, emails that were not sent).

Upvotes: 2

Anil Tallam
Anil Tallam

Reputation: 351

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

Upvotes: 18

S.Yadav
S.Yadav

Reputation: 4509

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.

Upvotes: 4

Shashank Agrawal
Shashank Agrawal

Reputation: 25797

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.

Upvotes: 5

jdi
jdi

Reputation: 92559

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

Upvotes: 273

Kishan Mundadiya
Kishan Mundadiya

Reputation: 21

you can use $in operator to check both cases

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

Upvotes: 2

SagitSri
SagitSri

Reputation: 521

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

Upvotes: -1

Vivek Maraina
Vivek Maraina

Reputation: 1

You can also try this:

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

Upvotes: 0

tvs
tvs

Reputation: 799

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

Upvotes: 22

Jim Horng
Jim Horng

Reputation: 1677

Seems you can just do single line:

{ "sent_at": null }

Upvotes: 10

Related Questions