Unique Index with partial filter is not being used by MongoDB

Working with Data
4

Hi @Aaron_Febo ,
When you run this query:

db.website.find({url: "mongo"})

the results may include documents with url as a Symbol, which is not covered by your index. For that reason, MongoDB driver will not use the index in this case.

from the docs:

MongoDB will not use the partial index for a query or sort operation if using the index results in an incomplete result set.

To test it, you can run this from the shell:

db.website.insertOne({url: BSONSymbol("mongo")})
db.website.find({url: "mongo"})

Btw, instead of changing your query, you can delete your index and create is as follows:

db.website.createIndex(
    {"url": 1}, 
    {unique: true, partialFilterExpression: {url: {$gte: ''}}}
);

See Partial Index with Unique Constraint for more information.

Thanks,
Rafael,

1 Like