Mysql – Insert on duplicate key update using MongoDB

mongodbMySQL

Can somebody tell me how I should build MongoDB "updateOne" query to get analog of the following SQL query:

INSERT INTO `collection`  
(`hash`, `total`, `base_fare`)  
VALUES ('aaa', 200, 150)
ON DUPLICATE KEY UPDATE
`base_fare`=IF(VALUES(`total`)<`total`,VALUES(`base_fare`),`base_fare`), 
`total`=IF(VALUES(`total`)<`total`,VALUES(`total`),`total`);

What I tried:

db.collection.updateOne(
    {"hash": "aaa", "total": {$lte: 200}},
    {$set: {"hash": "aaa", "total": 200, "base_fare": 150}},
    {upsert: true}
)

But this query insert new document into the collection if existed document with hash "aaa" has field "total" greater that 200. I understand that this is default behavior.

Is any solutions to get behavior equal to MySQL's INSERT ON DUPLICATE KEY UPDATE?

Thanks

Best Answer

As per MongoDB documentation here Since upsert:true the document is inserted based on the filter and update criteria.

As I am able to see that you are using { upsert: true } in query statement.

For example:

Update with Upsert

The restaurant collection contains the following documents:

{ "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan", "violations" : 3 },
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "Borough" : "Queens", "violations" : 2 },
{ "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", "violations" : "0" }

The following operation attempts to update the document with name : "Pizza Rat's Pizzaria", while upsert: true :

try {
   db.restaurant.updateOne(
      { "name" : "Pizza Rat's Pizzaria" },
      { $set: {"_id" : 4, "violations" : 7, "borough" : "Manhattan" } },
      { upsert: true }
   );
} catch (e) {
   print(e);
}

Since upsert:true the document is inserted based on the filter and update criteria. The operation returns:

{
   "acknowledged" : true,
   "matchedCount" : 0,
   "modifiedCount" : 0,
   "upsertedId" : 4
}

The collection now contains the following documents:

{ "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan", "violations" : 3 },
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "Borough" : "Queens", "violations" : 2 },
{ "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", "violations" : 4 },
{ "_id" : 4, "name" : "Pizza Rat's Pizzaria", "Borough" : "Manhattan", "violations" : 7 }

The name field was filled in using the filter criteria, while the update operators were used to create the rest of the document.

Related Question