CODEWITHSUNDEEP
mongodbunique-index
quickshiftin
quickshiftin

Reputation: 69601

Mongo unique compound text index

I'm trying to create a Mongo index with 2 text fields, whereby either field can have a value in another document, but the same pair cannot. I am familiar with this concept in MySQL, but do not understand it in Mongo.

I would like to create a unique index on the symbol and date fields of these documents:

db.earnings_quotes.insert({"symbol":"UNF","date":"2017-01-04","quote":{"price": 5000}});

db.earnings_quotes.createIndex({symbol: 'text', date: 'text'}, {unique: true})

db.earnings_quotes.insert({symbol: 'HAL', date: '2018-01-22', quote: { "price": 10000 }});
WriteResult({
        "nInserted" : 0,
        "writeError" : {
                "code" : 11000,
                "errmsg" : "insertDocument :: caused by :: 11000 E11000 duplicate key error index: sample.earnings_quotes.$symbol_text_date_text  dup key: { : \"01\", : 0.6666666666666666 }"
        }
})

I don't understand the error message here... In this case, neither symbol, nor date overlap with the first record.

Upvotes: 0

Views: 228

Answers (2)

USMAN FAZIL
USMAN FAZIL

Reputation: 840

By default mongo uses _id as unique key and index, so one solution to your problem is save your data in _id field.

e.g:

{
    "_id":{
            "symbol" :"xyz" ,
            "date" :"12-12-20" ,
        }

        //Other fields in collection
}

This will create a composite key.

Upvotes: -1

Ptijohn
Ptijohn

Reputation: 231

A text index actually behaves a bit like a multikey index, it tries to cut text into bits that can be then queried using specific text search operators. Also, the order of the fields in the text index doesn't really matter (compared to a normal compound index), MongoDB will just go through all the values in both symbol and date and index those separately.

In this case I believe that mongo tries to index the 01 in the 2017 and the 01 in -01- separately.

I don't think in your case you really want to do a text index, it's made for searching through long texts, not fields with single values in them.

And also, the multikey nature of the text index makes it really hard to stay unique.

My advice would be to go like this: db.earnings_quotes.createIndex({symbol: 1, date: 1}, {unique: true})

Upvotes: 1

Related Questions