0

I have a MongoDB(v 5.0.6) collection with the following data:

[{
  "_id": {
    "$oid": "63bc06e6aa310000d6004a68"
  },
  "key": "title",
  "label": "title",
  "type": "text",
  "searchable": 0
},{
  "_id": {
    "$oid": "63bc06e6aa310000d6004a69"
  },
  "key": "attribution",
  "label": "attribution",
  "type": "text",
  "searchable": 0
},{
  "_id": {
    "$oid": "63bc06e6aa310000d6004a6a"
  },
  "key": "description",
  "label": "description",
  "type": "text",
  "searchable": 0
},{
  "_id": {
    "$oid": "63bc06e6aa310000d6004a6b"
  },
  "key": "publisher",
  "label": "publisher",
  "type": "text",
  "searchable": 0
},{
  "_id": {
    "$oid": "63bc06e6aa310000d6004a6c"
  },
  "key": "published",
  "label": "published",
  "type": "number",
  "searchable": 0
},{
  "_id": {
    "$oid": "63bc06e6aa310000d6004a6d"
  },
  "key": "author",
  "label": "author",
  "type": "text",
  "searchable": 0
}]

Now I want to set up an unique index on the key (text) field. But each time I try to create the index, it produces this error:

Index build failed: 64014369-44e7-4371-976a-25f0aed89614: Collection db.collection ( c9b12814-686b-4603-8e90-bba31f67d930 ) :: caused by :: E11000 duplicate key error collection: db.collection index: key_text dup key: { _fts: "publish", _ftsx: 1.0 }

enter image description here

I have no idea why this appears. As the error indicates, there is no value matching 'publish' in the data. Can anyone please take a look into it and help? Thanks in advance for going through this far and some more if you can point the solution.

sariDon
  • 7,782
  • 2
  • 16
  • 27
  • It looks like you're attempting to create a text index (can you confirm the command that you are running?). Such index perform stemming and tokenization likely leading to the exception that you are observing. What are your goals with querying and uniqueness enforcement? Knowing that will help us advise on a solution – user20042973 Jan 09 '23 at 19:56
  • I am using MongoDB compass to create the index. I am updating the question with a screenshot for reference. – sariDon Jan 09 '23 at 20:00
  • I tried reproducing the issue in my local setup with the provided data and even creating the same text index but it's working fine on my side. Would it be possible that some other data(that is not included in this question) reside in your database? – ray Jan 09 '23 at 22:00
  • This is the actual data (full) exported. I will try to remove the collection and start fresh. – sariDon Jan 10 '23 at 04:20
  • The answer (https://stackoverflow.com/questions/75061974/error-e11000-in-creating-mongodb-unique-index/75063411#75063411) seems to explain the problem. However I need to explore this issue a bit more and try some addendums later on. A ton of thanks for your effort so far. – sariDon Jan 10 '23 at 06:53
  • According to the given answer, your trial reproduction would have failed due to the stemming issue (for 'published' and 'publisher'). So can you please confirm the parameters/command you have used? – sariDon Jan 10 '23 at 06:57

2 Answers2

0

Now I want to set up an unique index on the key (text) field.

There are no "text" fields in MongoDB. Field types are listed there: https://www.mongodb.com/docs/manual/reference/bson-types/ and key is a "string".

The index type is one of:

  • asc
  • desc
  • geospatial
  • fulltext

You are selecting the later, which is described here https://www.mongodb.com/docs/manual/core/index-text/

It makes very little sense to create unique full-text index. It will violate uniqueness if at least one word (post-stemed) appears in another document. In your case it's published and publisher. The common stem is publish.

Pleasae use one of asc or desc types to build unique index for the whole key. It will violate uniqueness only when keys are identical.

Alex Blex
  • 34,704
  • 7
  • 48
  • 75
0

I used console to run this command and it worked (index reflected in compass too):

db.<collection_name>.createIndex( { "key" : 1 }, { unique : true } )

Hope it helps someone.

sariDon
  • 7,782
  • 2
  • 16
  • 27