CODEWITHSUNDEEP
mongodbmongoosedatabase-indexes
rubixibuc
rubixibuc

Reputation: 7397

Mongoose/MongoDB and unexpected "text" index constraint violation

new mongoose.Schema({
    fieldA: {
        type: String,
        required: true,
        minlength: 3,
        maxlength: 3,
    },
    fieldB: {
        type: String,
        maxlength: 20,
        required: true,
        trim: true,
    }
}).index(
    {
        fieldA: 1,
        fieldB: "text",
    },
    {unique: true}
)

mongod --version

db version v4.2.6 git version: 20364840b8f1af16917e4c23c1b5f5efd8b352f8 allocator: system modules: none build environment: distarch: x86_64 target_arch: x86_64

"mongoose": "5.9.13",

Using that constraint I enter 3 values with the same fieldA and the 4th one fails on fieldB

  1. { fieldA: 'abc', fieldB: 'test' }
  2. { fieldA: 'abc', fieldB: 'test a' }
  3. { fieldA: 'abc', fieldB: 'test 1' }

This one fails the constraint:

  1. { fieldA: 'abc', fieldB: 'test b' }

Has anybody seen this? Ty!

Upvotes: 0

Views: 64

Answers (1)

Joe
Joe

Reputation: 28316

A text index is going to be problematic with the unique property. I'm frankly surprised that it is allowed to be created with that constraint.

A key in a text index does not use the field value as a whole. It collects all of the fields that are included in the text part of the index, splits them by whitespace and punctuation, applies language-specific stemming and sorting rules, and each term is used as a separate key in the index.

It is not clear where the unique constraint is checked, but there is clearly some inconsistency in the results.

The bottom line is using the unique constraint with a text index is probably not especially meaningful, and will not likely have the effect you are trying to achieve.

If you need to have text search for that field while ensuring the uniqueness of that field pair, the best option is to create 2 indexes, one on {fieldA:1, fieldB:"text"} without a unique constraint, and one on {fieldA:1, fieldB:1} with a unique constraint.

Upvotes: 1

Related Questions