CODEWITHSUNDEEP
mongodb
Saad
Saad

Reputation: 28496

Is mongodb able to index a null value?

I have an post whose field postStatus I want to set as either true false, or null. I was wondering whether mongo would be able to index null values.

Also, is there any difference if I choose to do null, true and false vs using -1,0,1 (or any 3 ints)?

thanks

Upvotes: 6

Views: 13333

Answers (1)

Markus W Mahlberg
Markus W Mahlberg

Reputation: 20703

Indexing of null

Yes, null values are indexed, as can be proven if you try to add a second null value to a unique index:

connecting to: test
> db.idxtest.createIndex({a:1},{unique:true})
{
    "createdCollectionAutomatically" : true,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> db.idxtest.insert({a:"foo"})
WriteResult({ "nInserted" : 1 })
> db.idxtest.insert({b:"bar"})
WriteResult({ "nInserted" : 1 })
> db.idxtest.insert({c:"baz"})
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error index: test.idxtest.$a_1 dup key: { : null }"
    }
})

Another way to demonstrate that null values matter is by sorting on an indexed field for which documents contain null values:

> db.idxtest2.createIndex({a:1})
{
    "createdCollectionAutomatically" : true,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> db.idxtest2.insert({a:1})
WriteResult({ "nInserted" : 1 })
> db.idxtest.insert({a:2})
WriteResult({ "nInserted" : 1 })
> db.idxtest2.insert({b:2})
WriteResult({ "nInserted" : 1 })
> db.idxtest2.insert({a:null})
WriteResult({ "nInserted" : 1 })
> db.idxtest2.find().sort({a:1})
{ "_id" : ObjectId("56786a93ada44c7ffcd38f9f"), "b" : 2 }
{ "_id" : ObjectId("56786aa1ada44c7ffcd38fa0"), "a" : null }
{ "_id" : ObjectId("56786a65ada44c7ffcd38f9d"), "a" : 1 }
> db.idxtest2.find().sort({a:-1})
{ "_id" : ObjectId("56786a65ada44c7ffcd38f9d"), "a" : 1 }
{ "_id" : ObjectId("56786aa1ada44c7ffcd38fa0"), "a" : null }
{ "_id" : ObjectId("56786a93ada44c7ffcd38f9f"), "b" : 2 }

Note that implicit null values have precedence; so to say, they are "more" null since they do not even have the according key.

On Booleans

As per BSON specification, a boolean value is a single byte, whereas an integer is at least a 32-bit integer consuming 4 bytes. So it does make a difference and if you have millions of entries that would amount to... ...well, a few megabytes. ;) So for all practical purposes there is no difference.

Upvotes: 8

Related Questions