Mongo unique compound text index

Question

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.

Ptijohn · Accepted Answer · 2019-08-16T13:11:50.450

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})

Ok thanks. Making these changes, and doing some test inserts, it seems to be behaving as expected. — quickshiftin, Aug 16 '19 at 13:11

score 0 · Answer 2 · answered Mar 19 '20 at 10:27

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.

Mongo unique compound text index

2 Answers2