2

I'm trying to add unique index on a group of fields in MongoDB. Not all of those fields are available in all of the documents and I'd like to index only those which have all of the fields.

So, I'm trying to run this:

db.mycollection.ensureIndex({date:1, type:1, reference:1}, {sparse: true, unique: true})

But I get an error E11000 duplicate key error index on a field which misses 'type' field (there are many of them and they are duplicate, but I just want to ignore them).

Is it possible in MongoDB or there is some workaround?

Community
  • 1
  • 1
sashkello
  • 17,306
  • 24
  • 81
  • 109
  • sparse means don't index a document that's missing the field you're indexing on. that doesn't make sense on a compound index unless you want to skip documents that don't have any of the fields set. – Asya Kamsky Jun 05 '13 at 01:25
  • @AsyaKamsky Yes, I know how it works, and I'm looking for workaround this limitation. – sashkello Jun 05 '13 at 01:30
  • There is already a bug filed on the same issue : https://jira.mongodb.org/browse/SERVER-2193 – Abhishek Kumar Jun 05 '13 at 01:40
  • here is the good explanation: http://stackoverflow.com/questions/7955040/mongodb-mongoose-unique-if-not-null – Dan K.K. May 26 '16 at 20:32

2 Answers2

3

There are multiple people who want this feature and because there is no workaround for this, I would recommend voting up feature request Jira tickets in jira.mongodb.org:

Note that because 785 would provide a way to enforce this feature, 2193 is marked "won't fix" so it may be more productive to vote up and add your comments to 785.

Asya Kamsky
  • 41,784
  • 5
  • 109
  • 133
  • Yeah, I found it as well after not receiving a definitive answer here. I'll accept this because while upsert is a viable alternative, it is only an alternative and doesn't really mimic the required behaviour. – sashkello Jun 05 '13 at 23:15
0

The uniqueness, you can guarantee, using upsert operation instead of doing insert. This will make sure that if some document already exist then it will update or insert if document don't exist

test:Mongo > db.test4.ensureIndex({ a : 1, b : 1, c : 1}, {sparse : 1})

test:Mongo > db.test4.update({a : 1, b : 1}, {$set : { d : 1}}, true, false)
test:Mongo > db.test4.find()
{ "_id" : ObjectId("51ae978960d5a3436edbaf7d"), "a" : 1, "b" : 1, "d" : 1 }
test:Mongo > db.test4.update({a : 1, b : 1, c : 1}, {$set : { d : 1}}, true, false)
test:Mongo > db.test4.find()
{ "_id" : ObjectId("51ae978960d5a3436edbaf7d"), "a" : 1, "b" : 1, "d" : 1 }
{ "_id" : ObjectId("51ae97b960d5a3436edbaf7e"), "a" : 1, "b" : 1, "c" : 1, "d" : 1 }
Abhishek Kumar
  • 3,328
  • 2
  • 18
  • 31
  • this is backwards. he wants to allow duplicates if all three fields are not present. Using upserts will prevent duplicates if only two out of three fields are presents - plus this would significantly complicate the logic as every insert will need to examine the document to be inserted for which of the three fields are present to do an update against. Plus what happens when you want to do a real upsert against a different attribute of the document? – Asya Kamsky Jun 05 '13 at 02:53
  • this can easily be solved by doing upserts ( instead of insert ) if all three keys are present in the document ( to insert ) and I guess it is very easy to examine from application. Rest I never talked about the performance. But given index on those fields won't be a huge performance issues because in normal inserts also, you will be updating the index thus have to be brought to RAM. This solution was just suggested as a work around. – Abhishek Kumar Jun 05 '13 at 04:21
  • and what happens when you need to do an upsert based on a different value - say based on a username or id or ... ? – Asya Kamsky Jun 05 '13 at 04:40
  • I am only doing upserts when all 3 keys are present, to ensure uniqueness, otherwise it is just an normal insert – Abhishek Kumar Jun 05 '13 at 05:21
  • don't you think his application may need to do updates as well as inserts? – Asya Kamsky Jun 05 '13 at 05:31
  • The only difference that the application has to bring is where ever we were doing insert and those 3 keys are present, then use upsert. Rest all the application operations will be as before. – Abhishek Kumar Jun 05 '13 at 05:36
  • you are incorrect. rest of application may have been doing updates by a different key in the application - how do you propose preventing it from updating these three fields? Or upserting by a different field? – Asya Kamsky Jun 05 '13 at 05:42
  • There is only one write operation that can run, and if I am doing some upsert on say ( { a : 1, b : 2, c : 4} ) and before this upsert someone updated the already existing document with same above params to ( { a : 2, b : 2, c : 4} ), then the first upsert will succeed because now its unique. I didn't get the issue, you are trying to point out. Please explain using some example. Also, we don't know exactly, what is the app requirement, so we can't make assumptions about the type of operations. – Abhishek Kumar Jun 05 '13 at 05:53
  • let's say you have a field "name" in addition to those other fields. you get a record with name:"abhishek" and some other fields and you want to insert it or update it if "abhishek" already exists. How do you specify the upsert? before you would do update({name:"abhishek"}, {name:"abhishek", a:1,b:1,c:1, x:"something"},{upsert:true}). What do you do now? – Asya Kamsky Jun 05 '13 at 12:55
  • update({a:1, b:1, c:1}, {$set : { name:"abhishek", x:"something" }},{upsert:true}) This will make sure that if no document exist for {a:1, b:1, c:1}, then we have to do insert, otherwise if the document is already existing for {a:1, b:1, c:1} then update the 'name' and 'x' keys – Abhishek Kumar Jun 05 '13 at 13:33
  • so instead of creating a new use named "abhishek" you just overwrote user named "asya" with abhishek's data? Because let's say you already had a user with {name:"asya", a:1, b:1, c:1}? – Asya Kamsky Jun 05 '13 at 13:39
  • yes, because there is a requirement for uniqueness in { a : 1, b : 1, c : 1 }. Anyways this is a total app requirement if the above case exists or not and if do exists then it is acceptable or not. – Abhishek Kumar Jun 05 '13 at 13:47