CODEWITHSUNDEEP
node.jsmongodb
Dallas Baker
Dallas Baker

Reputation: 376

How can I optimize my MongoDB Upsert statement?

A decision was made to switch our database from SQL to noSQL and I have a few questions on best practices and if my current implementation could be improved.

My current SQL implementation for upserting player data after a game.

   let template = Players.map(
      (player) =>
        `(
          ${player.Rank},"${player.Player_ID}","${player.Player}",${player.Score},${tpp},1
         )`,
       ).join(',');

    let stmt = `INSERT INTO playerStats (Rank, Player_ID, Player, Score, TPP, Games_Played) 
                VALUES ${template}
                ON CONFLICT(Player_ID) DO UPDATE 
                SET Score = Score+excluded.Score,
                            Games_Played=Games_Played+1,
                            TPP=TPP+excluded.TPP`;

    db.run(stmt, function (upsert_error) { ...

The expected code is to update existing players by checking if a current Player_id exist. If so update their score among other things. Else insert a new player.

Mongo Implementation

const players = [
        { name: 'George', score: 10, id: 'g65873' },
        { name: 'Wayne', score: 100, id: 'g63853' },
        { name: 'Jhonny', score: 500, id: 'b1234' },
        { name: 'David', score: 3, id: 'a5678' },
        { name: 'Dallas', score: 333333, id: 'a98234' },
      ];
const db = client.db(dbName);
const results = players.map((player) => {

    // updateOne(query, update, options)
    db.collection('Players')
      .updateOne(
         { Player_Name: player.name },
         {
           $setOnInsert: { Player_Name: player.name, id: player.id },
           $inc: { Score: player.score },
         },
         { upsert: true, multi: true },
       );
    });

Is there a better way in mongo to implement this? I tried using updateMany and bulkUpdate and I didn't get the results I expected.

Are there any tips, tricks, or resources aside from the mongo.db that you would recommend for those moving from SQL to noSQL?

Thanks again!

Upvotes: 1

Views: 81

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

Your approach is fine. However, there are a few flaws:

  • Command updateOne updates exactly one document as the name implies. Thus multi: true is obsolete.
  • Field names are case-sensitive (unlike most SQL databases). It should be $inc: { score: player.score }, not "Score"
  • Field Player_Name does not exist, it will never find any document for update.

So, your command should be like this:

db.collection('Players').updateOne(
   { name: player.name }, //or { id: player.id } ?
   {
      $setOnInsert: { name: player.name, id: player.id },
      $inc: { score: player.score },
   },
   { upsert: true }
)

According to my experience, moving from SQL to NoSQL is harder if you try to translate the SQL statement you have in your mind into a NoSQL command one-by-one. For me it worked better when I wiped out the SQL idea and try to understand and develop the NoSQL command from scratch.

Of course, when you do your first find, delete, insert, update then you will see many analogies to SQL but latest when you approach to the aggregation framework you are lost if you try to translate them into SQL or vice versa.

Upvotes: 1

Related Questions