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?

Improve this question
4
  • 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. Commented Jun 5, 2013 at 1:25
  • @AsyaKamsky Yes, I know how it works, and I'm looking for workaround this limitation. Commented Jun 5, 2013 at 1:30
  • There is already a bug filed on the same issue : jira.mongodb.org/browse/SERVER-2193 Commented Jun 5, 2013 at 1:40
  • here is the good explanation: stackoverflow.com/questions/7955040/… Commented May 26, 2016 at 20:32

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
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 }
Improve this answer

12 Comments

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?
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.
and what happens when you need to do an upsert based on a different value - say based on a username or id or ... ?
I am only doing upserts when all 3 keys are present, to ensure uniqueness, otherwise it is just an normal insert
don't you think his application may need to do updates as well as inserts?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.