Is there a simpler way to update an entire record in MongoDB using python?-CodePudding

My use case is this: I am passing a json payload into a python program which conditionally upserts the record into my MongoDB collection. It checks for a unique combination in the collections records and updates the record if the 'timestamp' is greater, upserts if the combination is not found and retains the record if 'timestamp' is lower.

payload = {'stCode': 'ABC1', 'skCode': 'CDE2', 'batchCode': 'FGH3', ts: '2022/07/04 09:48:36'}
inp_ts = payload['ts']
inp_ts = datetime.strptime(inp_ts, '%Y/%m/%d %H:%M:%S')
inp_ts_iso = inp_ts.isoformat()

stCode = payload['stCode']
skCode = payload['skCode']
batchCode = payload['batchCode']
    
result = collection.find_one({'stCode': stCode,'skCode': skCode, 'batchCode': batchCode }) 

if result is None:
    collection.insert_one(payload) #Condition1: Insert record if not present
        
else:
    result_ts = result['ts']
    result_ts = datetime.strptime(result_ts, '%Y/%m/%d %H:%M:%S')
    result_ts_iso = result_ts.isoformat() #since the date is stored as string in the database
        
    if inp_ts_iso > result_ts_iso:
        temps={"storeCode": storeCode,"skuCode": skuCode, "batchCode": batchCode }, {"$set": payload}
        collection.update_one(*temps) #Condition2: Update record if timestamp is older
        print('Records successfully written to MongoDB')
            
    else:
        print("no records written") #Condition3: Do nothing if timestamp is newer

I know that it isn't the prettiest of the code but I would like it to be more simple and short. Is there a way of achieving the same by without having Lambda to do the logic test but rather do it on Mongo's side itself? I have seen complex update_one queries but I couldn't work it for my benefit. Thanks in advance!

CodePudding user response：

Yes you can, you can do it in a few different ways, but they all involve using pipelined updates which allows you to use aggregation operators in your updates.

Here is what I consider to be the most elegant way of achieving this: (summary of the approach at the bottom)

const payload = {'stCode': 'ABC1', 'skCode': 'CDE2', 'batchCode': 'FGH3', ts: '2022/07/04 09:48:36'}
const stCode = payload['stCode']
const skCode = payload['skCode']
const batchCode = payload['batchCode']

db.collection.updateOne(
    {'stCode': stCode,'skCode': skCode, 'batchCode': batchCode },
    [
      {
        "$replaceRoot": {
          "newRoot": {
            "$mergeObjects": [
              "$$ROOT",
              {
                $cond: [
                  {
                    $gt: [
                      "$ts",
                      payload.ts
                    ]
                  },
                  {},
                  payload
                ]
              }
            ]
          }
        }
      }
    ],
    {
      "upsert": true
    }
)

Or with pymongo:

payload = {'stCode': 'ABC1', 'skCode': 'CDE2', 'batchCode': 'FGH3', ts: '2022/07/04 09:48:36'}
stCode = payload['stCode']
skCode = payload['skCode']
batchCode = payload['batchCode']

collection.update_one(
    {'stCode': stCode, 'skCode': skCode, 'batchCode': batchCode},
    [
        {
            "$replaceRoot": {
                "newRoot": {
                    "$mergeObjects": [
                        "$$ROOT",
                        {
                            "$cond": [
                                {
                                    "$gt": [
                                        "$ts",
                                        payload['ts']
                                    ]
                                },
                                {},
                                payload
                            ]
                        }
                    ]
                }
            }
        }
    ],
    upsert=True
)

Mongo Playground

So we use $replaceRoot in the update, the new root is a merge of two objects. The first object is the $$ROOT parameter, so if the document does not exist it's basically an empty object, if it does exist then it's the original object, the idea behind this being the first object is to preserve the _id field (and any other required pre existing fields) that could exist on the object, this is why we don't just use the new object in the $replaceRoot as it will generate a new _id.

The second object is based on a condition using $cond, if the existing ts is bigger then we use an empty object, which does not change the original $$ROOT, else we just use the new payload which will overwrite any of the existing fields. As mentioned if the new payload has missing fields they will not be overwritten if this could be the case some minor changes need to be done, but based on your code sample this potential edge case was not handled so I assumed it does not exist.