0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.