Compound index where one field can be null MongoDB-CodePudding

How can I create compound index in mongo where one of the fields maybe not present or be null?

For example in below documents if I create a compound index name age. How can I still achieve this with age being not present or null in some documents?

{
  name: "Anurag",
  age: "21",
},
{
  name: "Nitin",
},

CodePudding user response：

You can create partial Index as follow:

  db.contacts.createIndex(
   { name: 1 },
   { partialFilterExpression: { age: { $exists: true } } }
   )

Explained:

As per the documentation partial indexes only index the documents in a collection that meet a specified filter expression. By indexing a subset of the documents in a collection, partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance. In this particular case imagine your collection have 100k documents , but only 5 documents have the "age" field existing , in this case the partial index will include only those 5 fields in the index optimizing the index storage space and providing better performance.

For the query optimizer to choose this partial index, the query predicate must include a condition on the name field as well as a non-null match on the age field.

Following example queries will be able to use the index:

 db.contacts.find({name:"John"})
 db.contacts.find({name:"John",age:{$gt:20}})
 db.contacts.find({name:"John",age:30})

Following example query is a "covered query" based on this index:

db.contacts.find({name:"John",age:30},{_id:0,name:1,age:1})

( this query will be highly efficient since it return the data directly from the index )

Following example queries will not be able to use the index:

db.contacts.find({name:"John",age:{$exists:false}})
db.contacts.find({name:"John",age:null})
db.contacts.find({age:20})

Please, note you need to perform some analysis on if you need to search on the age field together with the name , since name field has a very good selectivity and this index will not be used in case you search only by age , maybe a good option is to create additional sparse/partial index only on the age field so you could fetch a list with contacts by certain age if this a possible search use case.