1

I added a new field in the production Mongo Database and need to create a unique index on it. If I try to create one it keeps throwing 'exception: E11000 duplicate key error index:'.

I understand documents without the new field have a null value which is causing the uniqueness violation. How should I do it?

Kihats
  • 3,326
  • 5
  • 31
  • 46
  • You need to make sure all documents have the new field with unique values. That means you would need to update all of them. – kkkkkkk Aug 30 '18 at 10:18
  • @Khang, was afraid of that approach but seems that is the only solution :-) – Kihats Aug 30 '18 at 10:29

2 Answers2

3

You can make a partial index so that unique constraint apply only to documents where the field is present. E.g

db.collection.createIndex(
   { newField: 1 },
   { unique: true, partialFilterExpression: { newField: { $exists: true } } }
)
Alex Blex
  • 34,704
  • 7
  • 48
  • 75
  • still getting the same error: 'E11000 duplicate key error index: db.trips.$reservationId_-1 dup key: { : null }' – Kihats Aug 30 '18 at 11:33
  • so you have documents where the field exists and is explicitly `null`. adjust the `partialFilterExpression` condition to meet your data. – Alex Blex Aug 30 '18 at 12:25
  • @rishabh agarwal answer worked. Here is the documentation: https://docs.mongodb.com/manual/core/index-sparse/ – Kihats Sep 03 '18 at 18:30
  • @Kihats, of course it does. The page you are referring to has an important yellow box. It worth reading. – Alex Blex Sep 04 '18 at 08:25
2

You can use Sparse Index which contain only entries for documents that have the indexed field. Example:

db.collection.createIndex( { "newIndex": 1 }, { sparse: true } )
Rishabh Agarwal
  • 1,988
  • 1
  • 16
  • 33