4

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.

Community
  • 1
  • 1
Prinzhorn
  • 22,120
  • 7
  • 61
  • 65

1 Answers1

1

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)

coffee_machine
  • 1,203
  • 14
  • 28
  • It's not pretty but would probably work. I did however manage to use another workaround by adding more domain specific fields to the index, which made sense in my case. I added the `title` of the event as well, this way I don't have a problem with two events at the same time (I added the creator/user as well). – Prinzhorn Sep 23 '13 at 16:53