Command failed with error 67 :cannot create unique index over { Key.IdentifierValue:: -1 } with shard key pattern { _id: "hashed" }'

Question

I need to avoid duplications Transactions while inserting the documents in MongoDB database . Getting below exception .The condition is if we try to insert the Key.IdentifierValue with the same value it should throw the duplicate exception but the documents are getting inserted in MongoDB database successfully without any issue. If we remove index options in createIndex method , the document is getting inserted but duplicate transactions are happening and not able to do with upsert operation .Any help will be appreciated

Document Structure:

"_id" : ObjectId("12345678"), 
"Key": {
      "color": "red",
      },
    
      "IdentifierValure": "Sample.txt",
    
  Command failed with error 67 (CannotCreateIndex): 'Index build failed: 48888886ee5e3: Collection sample ( 48888886ee5e3-0788-4414-bc43-2e118e79246e ) :: caused by :: cannot create unique index over { Key.IdentifierValue:: -1 } with shard key pattern { _id: "hashed" }'

    @ResponseBody
            public boolean createSample(@Valid @RequestBody Document... sample) {
                MongoDatabase database = this.mongoClient.getDatabase("database");
                MongoCollection<Document> collection = database.getCollection("Sample");
                  
                 List<Document> documentList = new ArrayList<>(sample.length);
                 IndexOptions indexOptions = new IndexOptions().unique(true);
          
            String resultCreateIndex = collection.createIndex(Indexes.descending("Key.IdentifierValue"), indexOptions);
                for (Document doc : sample) {
                    documentList.add(Document.parse(doc.toJson()));
                }
                collection.insertMany(documentList);
        
                return sucessfully inserted;
            }
      
    
        

Answer 1

I was attempting to write an answer that expanded on my comments and addressed your original question/error message. It looks like you have just edited your post to meaningfully change the question. Please ask a different question next time instead.

---

Original Problem

There are probably a few problems here, but the central one is that you currently do not seem to have a unique index on the Key.IdentifierValue field.

Reformatted, the full error that you are receiving is:

com.mongodb.MongoCommandException: Command failed with error 86 (IndexKeySpecsConflict): 
  An existing index has the same name as the requested index. 
  When index names are not specified, they are auto generated and can cause conflicts. 
  Please refer to our documentation. 
  Requested index: { v: 2, unique: true, key: { Key.IdentifierValue: 1 }, name: "Key.IdentifierValue: 1" }, 
  existing index: { v: 2, key: { Key.IdentifierValue: 1}, name: "Sample.Service_1" }

More specifically, there is already an index present in the collection (second definition below) which conflicts with the one that the code is trying to create (first definition below). Formatted further for easy comparison:

{ v: 2, unique: true, key: { Key.IdentifierValue: 1 }, name: "Key.IdentifierValue: 1" } 
{ v: 2,               key: { Key.IdentifierValue: 1 }, name: "Sample.Service_1" }

In order to create your unique index, you will need to drop the existing one first:

> //First attempt fails because of the existing index
>  db.sample.createIndex({ "Key.IdentifierValue": 1 },{name: "Key.IdentifierValue: 1", unique: true})
{
    "ok" : 0,
    "errmsg" : "Index: { v: 2, unique: true, key: { Key.IdentifierValue: 1.0 }, name: \"Key.IdentifierValue: 1\", ns: \"test.sample\" } already exists with different options: { v: 2, key: { Key.IdentifierValue: 1.0 }, name: \"Sample.Service_1\", ns: \"test.sample\" }",
    "code" : 85,
    "codeName" : "IndexOptionsConflict"
}
>
> //Drop the conflicting index that is present
> db.sample.dropIndex({ "Key.IdentifierValue": 1 })
{ "nIndexesWas" : 2, "ok" : 1 }
>
> //Index can now be created successfully
> db.sample.createIndex({ "Key.IdentifierValue": 1 },{name: "Key.IdentifierValue: 1", unique: true})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

Newer versions of MongoDB behave differently. You can create the unique index without dropping the existing one first:

> db.version()
6.0.1
test> db.sample.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { 'Key.IdentifierValue': 1 }, name: 'Sample.Service_1' },
  { v: 2, key: { 'Key.IdentifierValue': 1 }, name: 'Key.IdentifierValue: 1', unique: true }
]

Furthermore, you can also convert an existing non-unique index to be unique as mentioned here

---

New Problem

After editing your question, the new (different) error is:

Command failed with error 67 (CannotCreateIndex): 
  'Index build failed: 48888886ee5e3: 
  Collection sample ( 48888886ee5e3-0788-4414-bc43-2e118e79246e ) :: caused by :: 
  cannot create unique index over { Key.IdentifierValue:: -1 } with shard key pattern { _id: "hashed" }'

Apparently you have a sharded collection which is using the hashed value of _id as the shard key. The ability to enforce uniqueness in sharded clusters is documented here. Borrowing some text from there:

MongoDB can enforce a uniqueness constraint on a ranged shard key index. Through the use of a unique index on the shard key

...

• For an already-sharded collection, you cannot create unique indexes on other fields.

...

You cannot specify a unique constraint on a hashed index.

Based on that last line there, you will not be able to create the unique index that you want based on your current shard key. You will have to choose a different shard key per the linked documentation if you wish to enforce uniqueness on a sharded collection.

---

General Comment

It is not the responsibility of the application to create indexes directly. Having the code attempt to do so on every write adds unnecessary overhead and should be avoided.

Instead, you should create and manage your indexes separately outside of the code.