Add a compound index when some fields can be null or arrays of strings

Question

I have a mongodb collection.

{
    "p1": "V1",
    "p2": ["V2","V3","V4"],
    "p3": ["V5","V6","V7"],
    "p4": "V8"
}

I want to create an index on fields p1, p2, p3. My documents may not have p2 or p3. How should I create my index?

Answer 1

Assuming p2 and p3 may be missing or may be equal to null, I would go for a sparse index like so :

db.myCollection.createIndex( { p1:1, p2:1, p3:1 } , { sparse: true } )

Answer 2

According to your question it is clear that you are going to create a compound multikey index where two of them are array. But the limitation of compound multikey index is, it can have at most one indexed field whose value is an array.

Compound Multikey Indexes For a compound multikey index, each indexed document can have at most one indexed field whose value is an array. As such, you cannot create a compound multikey index if more than one to-be-indexed field of a document is an array. Or, if a compound multikey index already exists, you cannot insert a document that would violate this restriction.

So, according to mongodb manual you can take either p1 or p2 as indexed field.

learn more about compound multikey index from here

Your document may not have indexed fields, In this case Sparse Index can be used.

For the given collection:

db.collection.createIndex( { p1:1, p2:1, p4:1 } , { sparse: true } )

will create an Sparse Index. But you have to consider the following limitations :

1. If any document does not contain the indexed field the sparse index will not contain an entry for that document.

2. Use of hint can solve the limitation 1 but you may get incomplete result.

You can learn more about sparse index from here