Is MongoDB able to index a null value?

Yes, MongoDB can index null values. When you create an index on a field, MongoDB includes documents where that field is null or missing in the index. This allows efficient queries on null values using the index.

Syntax

db.collection.createIndex({"fieldName": 1})
db.collection.find({"fieldName": null})

Create Sample Data

First, create a unique index on the "Value" field and insert documents with null values ?

db.demo170.createIndex({"Value": 1}, {unique: true});

{
  "createdCollectionAutomatically": true,
  "numIndexesBefore": 1,
  "numIndexesAfter": 2,
  "ok": 1
}

db.demo170.insertMany([
  {"Value": 100},
  {"Value": null},
  {"Value": 90},
  {"Value": 78}
]);

WriteResult({ "nInserted": 4 })

Example: Query All Documents

Display all documents to see the null value ?

db.demo170.find();

{ "_id": ObjectId("5e369b789e4f06af551997da"), "Value": 100 }
{ "_id": ObjectId("5e369b7c9e4f06af551997db"), "Value": null }
{ "_id": ObjectId("5e369b809e4f06af551997dc"), "Value": 90 }
{ "_id": ObjectId("5e369b969e4f06af551997dd"), "Value": 78 }

Example: Sort Using Index with Null Values

Sort documents by the indexed field to see how null values are handled ?

db.demo170.find().sort({Value: 1});

{ "_id": ObjectId("5e369b7c9e4f06af551997db"), "Value": null }
{ "_id": ObjectId("5e369b969e4f06af551997dd"), "Value": 78 }
{ "_id": ObjectId("5e369b809e4f06af551997dc"), "Value": 90 }
{ "_id": ObjectId("5e369b789e4f06af551997da"), "Value": 100 }

Key Points

• Null values are indexed: MongoDB includes null and missing field values in indexes.
• Sort order: Null values appear first in ascending order sorts.
• Unique constraints: Only one document with a null value is allowed in unique indexes.
• Query performance: Queries for null values can use the index efficiently.

Conclusion

MongoDB successfully indexes null values, allowing efficient queries and sorting operations. The index treats null as the smallest value, placing it first in ascending sorts and enabling fast null value lookups.