5

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.
Aiden Zhao
  • 633
  • 4
  • 15

3 Answers3

5

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.

naught101
  • 18,687
  • 19
  • 90
  • 138
Aiden Zhao
  • 633
  • 4
  • 15
1

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.

Mani
  • 1,471
  • 1
  • 13
  • 19
  • sorry I have pasted the wrong error message from my earlier attempt. I have updated my problem. Thank you! – Aiden Zhao Feb 28 '19 at 03:18
  • Please check the replacement document you sending to the query, that field values already exist in the database. – Mani Feb 28 '19 at 03:47
  • are you saying if the replacement document is essentially the same document in the database, then it will throw the error? Even if this is the case, currently in my script, I have made a try and catch so the script will continue to run, and I have observed that even when the document does change the old document wouldn't be replaced with the new one. – Aiden Zhao Feb 28 '19 at 08:02
  • I have just did the same replace_one with the mongo console, and it works, see my edit to the question. Thanks – Aiden Zhao Feb 28 '19 at 19:58
0

You are telling mongodb to replace one document. Your filter is most likely returning multiple documents.

Use a find with the same filter and figure out a filter that will always return one document. In most cases just using an id should be enough to return one document so long as your schema is layed out correctly.

Jab
  • 26,853
  • 21
  • 75
  • 114
  • it is only returning one, because I have created a compound index with unique, so it is not possible to have multiple documents with that query. and I have used find and indeed the returned document is always either nothing or just 1. – Aiden Zhao Feb 28 '19 at 02:56
  • Have you seen [this](https://stackoverflow.com/questions/24430220/e11000-duplicate-key-error-index-in-mongodb-mongoose) question? – Jab Feb 28 '19 at 02:59
  • Yes, and it is different, that person had "email" as a unique single index, and it was inserting "null" as the email, when another document already has "null". which is different from my case, as I don't have any other index set. – Aiden Zhao Feb 28 '19 at 03:03