Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongodb update with upsert and multi syntax

I am new to mongodb and so stressed out because of mongodb's incomplete documentation leaving me for trial and error... sadly, all my attempts are not working with no error, leaving me confused about what was happening and what to debug...

I just need to update multiple records on the database matching certain criteria, and for the non existing records, create new entries for that. I believe I can do it with a single database access with update, upsert and multi. Here's what I've come up with:

dbschema.Person.update( { person_id: { $in: ["734533604" ,"701084015"] } }, { $set: {"scores": 1200} }, { options: { upsert: true, multi: true } } );

I've also tried multiple combinations or even the old version such as:

dbschema.Person.update( { person_id: { $in: ["734533604" ,"701084015"] } }, { $set: {"scores": 1200} }, { upsert: true }, { multi: true } );

none of them works...

Please help me with this so trivial stuff... I can easily do it in sql, but the nosql thingy is so limiting on me.. Thanks!

EDIT:

The same query on find works perfectly:

dbschema.Person.find( { person_id: { $in: ["734533604" ,"701084015"] } }, function ( err, results ) {
    console.log( 'result: ' + results );
    console.log( 'error: ' + err );
    console.log( 'result length: ' + results.length );
} );

EDIT:

I am expecting the "not found" record to be created, and the found record to be updated. my logic may be flawed and I am so much confused now.

Originally, I was find()-ing one record at a time, change the value, and called save() for each of the record modified, but when I deployed to live, the response time become hundreds of time slower especially when there's a few hundred records to be updated on each request.

Then I found find() + $in, and the performance is restored and even better than previous (when query), but the update is still unacceptably slow.. Therefore, now I am looking for ways to update all documents at one query..

what I normally do in SQL is using UPDATE WHEN CASE THEN... eg:

UPDATE person SET score = CASE
WHEN person_id = "734533604" THEN 1200
WHEN person_id = "701084015" THEN 1200
ELSE
score
END
like image 695
Zennichimaro Avatar asked Mar 25 '23 03:03

Zennichimaro


1 Answers

You cannot update multiple records based on different criteria and expect "upsert" to figure out what you mean. Upsert flag can cause insertion of at most one document and if you check the documentation you'll see that it doesn't make sense to have a "compound" criteria for update in case of an upsert.

In your example, which of the two fbid values should the insert use?

I think in your case you can take several approaches (all involve more than a single operation). You can update using the upsert flag in a loop calling update once for each fbid value - this will work like you expect and if fbid is not found a new document for it will be created. Other ways involve querying before running the update but I think those ways may be more prone to race conditions.

Here is explanation of how update works - I find it pretty complete: http://docs.mongodb.org/manual/core/update/#update-operations-with-the-upsert-flag

like image 104
Asya Kamsky Avatar answered Apr 02 '23 22:04

Asya Kamsky