Mongodb replace_one() with upsert = true throws duplicate key error

Question

I have read that when I use pymongo's upsert and when I don't supply the "_id", the upsert will try to generate a new Id, and that will cause the operation to fail. Is this true? and how do I upsert without using the "_id"?

Here is my replace_one using pymongo:

db['dataitemdetails'].replace_one({'asset_Id':tdata['asset_id'],'period_type':tdata['period_type'],'detail_id':tdata['detail_id'], 'currencycode':tdata['currencycode'],'dataitem_Id':tdata['dataitem_id'],'period_end':tdata['period_end'], 'scenario_id':tdata['scenario_id'],}, tdata, upsert=True)

I have created a compound index and set to unique using my search criteria, so that in the collection what I search will always be unique if it exists.

and with replace_one I'm getting this error:

pymongo.errors.DuplicateKeyError: E11000 duplicate key error collection: finance.dataitemdetails index: asset_id_1_dataitem_id_1_detail_id_1_period_type_1_scenario_id_1_currencycode_1_period_end_1 dup key: { : 19, : 1211, : 0, : "Month", : 1, : "RC", : new Date(949276800000) }

Here is my query for find with the same filter and it returns 1 document.

> db.dataitemdetails.find({'asset_id':19,'dataitem_id':1211,'detail_id':0,'period_type':'Month','currencycode':'RC','period_end':new Date(949276800000)})
{ "_id" : ObjectId("5c7721c17314e53a85be7e89"), "Value" : "USD", "period_end" : ISODate("2000-01-31T00:00:00Z"), "currencycode" : "RC", "scenario_id" : 1, "dataitem_id" : 1211, "period_type" : "Month", "detail_id" : 0, "asset_id" : 19 }

I'm stuck on not knowing what to try and how to fix this.

the complete error message:

Traceback (most recent call last):
  File "./periodic_update.sh", line 307, in <module>
    db['dataitemdetails'].replace_one({'asset_Id':tdata['asset_id'],'period_type':tdata['period_type'],'detail_id':tdata['detail_id'], 'currencycode':tdata['currencycode'],'dataitem_Id':tdata['dataitem_id'],'period_end':tdata['period_end'], 'scenario_id':tdata['scenario_id'],}, tdata, upsert=True)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/collection.py", line 925, in replace_one
    collation=collation, session=session),
  File "/usr/local/lib64/python3.7/site-packages/pymongo/collection.py", line 851, in _update_retryable
    _update, session)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/mongo_client.py", line 1248, in _retryable_write
    return self._retry_with_session(retryable, func, s, None)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/mongo_client.py", line 1201, in _retry_with_session
    return func(session, sock_info, retryable)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/collection.py", line 847, in _update
    retryable_write=retryable_write)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/collection.py", line 818, in _update
    _check_write_command_response(result)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/helpers.py", line 217, in _check_write_command_response
    _raise_last_write_error(write_errors)
  File "/usr/local/lib64/python3.7/site-packages/pymongo/helpers.py", line 198, in _raise_last_write_error
    raise DuplicateKeyError(error.get("errmsg"), 11000, error)
pymongo.errors.DuplicateKeyError: E11000 duplicate key error collection: finance.dataitemdetails index: asset_id_1_dataitem_id_1_detail_id_1_period_type_1_scenario_id_1_currencycode_1_period_end_1 dup key: { : 19, : 1211, : 0, : "Month", : 1, : "RC", : new Date(949276800000) }

```


Find in Mongodb console returned only 1 document


```


>db.dataitemdetails.find({'asset_id':19,'dataitem_id':1211,'detail_id':0,'period_type':'Month','currencycode':'RC','period_end':new Date(949276800000)})
{ "_id" : ObjectId("5c7721c17314e53a85be7e89"), "Value" : "USD", "period_end" : ISODate("2000-01-31T00:00:00Z"), "currencycode" : "RC", "scenario_id" : 1, "dataitem_id" : 1211, "period_type" : "Month", "detail_id" : 0, "asset_id" : 19 }
>

```

Here is my replace one query in the mongo console instead of pymongo:

```
> db.dataitemdetails.replaceOne({'asset_id':19,'dataitem_id':1211,'detail_id':0,'period_type':'Month','currencycode':'RC','period_end':new Date(949276800000)} ,{'asset_id':19,'dataitem_id':1211,'detail_id':0,'period_type':'Month','currencycode':'RC','period_end':new Date(949276800000), 'Value':'USD'}, upsert = true)
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
>
```

some other notes:

If I remove the compound index, the error message would go away, but I would end up with duplicate documents that are the same.

I tried to drop the collection, database and it would end up the same.

Answer 1

I have found the problem:

in this line of code:

db['dataitemdetails'].replace_one({
    'asset_Id': tdata['asset_id'],
    'period_type': tdata['period_type'],
    'detail_id': tdata['detail_id'],
    'currencycode': tdata['currencycode'],
    'dataitem_Id': tdata['dataitem_id'],
    'period_end': tdata['period_end'],
    'scenario_id': tdata['scenario_id'],
}, tdata, upsert=True)

my filter is wrong.

where asset_Id should have been asset_id, and dataitem_Id should be dataitem_id.

thus no match is found but when inserting a duplicate key error raised.

Answer 2

To answer your first question,

If you don't provide _id field value for upsert=true, MongoDB will generate a new id

And the reason why you are getting the duplicate key error is, you haven't used the period field in your filter condition.