MongoDB workaround for unsupported sparse unique compound index

Question

I need a workaround because MongoDB does not support sparse unique composite indexes (it will set to null if it does not exist, while it does not add a field to the index when it is a non-composite index). See https://jira.mongodb.org/browse/SERVER-2193

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

I thought I would just add this index to prevent duplicate copies when cronjob runs twice

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

Unfortunately, as stated above, MongoDB does not support sparse on composite indexes. This means that for one-time events, the parent field is absent and MongoDB is set to null . If I now have one one-time event at a time, this causes a recurring key error that I only want when setting the parent.

Any ideas?

Edit: I saw MongoDB: unique and rare compound indexes with sparse values , but application-level uniqueness checking is not-go. I mean, why do we need a database to guarantee uniqueness.

Answer 1

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

This way you can provide the uniqueness you want. However, you can hardly use it for anything else, except to force use of this restriction. (Although queries such as "get documents where the line begins with blah blah" can be quite effective)