CODEWITHSUNDEEP
mongodbindexing
njLT
njLT

Reputation: 474

unique compound text indexes on mongodb

I've created an index on two fields f1, f2 with db.test.createIndex({"f1":"text","f2":"text"},{unique:true})

{
    "v" : 2,
    "unique" : true,
    "key" : {
        "_fts" : "text",
        "_ftsx" : 1
    },
    "name" : "f1_text_f2_text",
    "ns" : "test.test",
    "weights" : {
        "f1" : 1,
        "f2" : 1
    },
    "default_language" : "english",
    "language_override" : "language",
    "textIndexVersion" : 3
}

When I insert the two documents

db.test.insert({f1:"hello",f2:"there"})
db.test.insert({f1:"hello",f2:"there2"})

I get a duplicate key error

"E11000 duplicate key error collection: test.test index: f1_text_f2_text dup key: { : \"hello\", : 1.1 }"

however db.test.insert({f1:"hello2",f2:"there"}) works.

Are compound text indexes not supposed to work like regular compound indexes?

Upvotes: 3

Views: 2023

Answers (1)

glytching
glytching

Reputation: 47895

Are you sure that you want a unique text index?

If you create a standard compound index:

db.test.createIndex({"f1": 1, "f2": 1}, {unique: true})

Then the following inserts will all be successful:

db.test.insert({f1:"hello",f2:"there"})
db.test.insert({f1:"hello",f2:"there1"})
db.test.insert({f1:"hello",f2:"there2"})

And this insert will then fail with E11000 duplicate key error collection:

db.test.insert({f1:"hello",f2:"there"})

You don't have to create a text index in order to index string fields. A text index has a very specific role in supporting text searches but not all string searches require a text index. So, if you must ...

  • Facilitate 'quick' text matches covering both f1 and f2
  • Enforce uniqueness across f1 and f2

... then I suspect you will need to create two indexes:

  • db.test.createIndex({"f1":"text", "f2":"text"})
  • db.test.createIndex({"f1": 1, "f2": 1}, {unique: true})

Upvotes: 5

Related Questions