CODEWITHSUNDEEP
mongodb
Prinzhorn
Prinzhorn

Reputation: 22508

MongoDB workaround for not supported sparse unique compound index

I need a workaround because MongoDB does not support sparse unique compound indexes (it will set the values to null if not present whereas it doesn't add the field to the index when it's a non-compound index). See https://jira.mongodb.org/browse/SERVER-2193

In my particular case I have events. They can either be one-time or recurring. I have a field parent which is only present when the event is an instance of a recurring event (I periodically create new copies of the parent to have the recurring events for the next weeks in the system).

I thought I'd just add this index in order to prevent duplicate copies when the cronjob runs twice

events.ensureIndex({ dateFrom: 1, dateTo: 1, parent: 1 }) { sparse: true, unique: true }

Unfortunately as said above MongoDB does not support sparse on compound indexes. What this means is that for one-time events the parent field is not present and is set to null by MongoDB. If I now have a second one-time event at the same time, it causes a duplicate key error, which I only want when parent is set.

Any ideas?

Edit: I've seen MongoDB: Unique and sparse compound indexes with sparse values , but checking for uniqueness on application level is a no-go. I mean that's what the database is there for, to guarantee uniqueness.

Upvotes: 4

Views: 1503

Answers (1)

coffee_machine
coffee_machine

Reputation: 1223

You can add a 4th field which would be dateFrom+dateTo+parent (string concatenation). When the parent is null, choose a uid, for example from ObjectId function, and then index that field (unique).

This way you can enforce the uniqueness you want. However you can hardly use it for anything else than enforcing this constraint. (Although queries like "get docs where the string starts with blah blah" may be pretty efficient)

Upvotes: 1

Related Questions