How to properly do a Bulk upsert/update in MongoDB

Question

I'm trying to:

• Find a document according to a search criteria,
• If found, update some attributes
• If not insert a document with some attributes.

I'm using a Bulk.unOrderedOperation as I'm also performing a single insert. And I want to do everything in one operation againast DB.

However something it's causing nothing is being inserted for the update/upsert operation.

This is the insert document:

  var lineUpPointsRoundRecord = {
    lineupId: lineup.id,  // String
    totalPoints: roundPoints, // Number
    teamId: lineup.team, // String
    teamName: home.team.name, // String
    userId: home.iduser, // String
    userName: home.user.name, // String
    round: lineup.matchDate.round, // Number
    date: new Date()
  }

This is the upsert document:

  var lineUpPointsGeneralRecord = {
    teamId: lineup.team, // String
    teamName: home.team.name, // String
    userId: home.iduser, // String 
    userName: home.user.name, // String
    round: 0,
    signupPoints: home.signupPoints, // String
    lfPoints: roundPoints+home.signupPoints, // Number 
    roundPoints: [roundPoints] // Number
  };

This is how I'm trying to upsert/update:

var batch = collection.initializeUnorderedBulkOp();

batch.insert(lineUpPointsRoundRecord);

batch.find({team: lineUpPointsRoundRecord.teamId, round: 0}).
  upsert().
  update({
    $setOnInsert: lineUpPointsGeneralRecord,
    $inc: {lfPoints: roundPoints},
    $push: {roundPoints: roundPoints}
  });

batch.execute(function (err, result) {
  return cb(err,result);
});

Why wouldn't it be upserting/updating?

Note

That is JS code using waterline ORM which also uses mongodb native driver.

Answer 1

Your syntax here is basically correct, but your general execution was wrong and you should have "seperated" the "upsert" action from the other modifications. These will otherwise "clash" and produce an error when an "upsert" occurs:

LineupPointsRecord.native(function (err,collection) {

    var bulk = collection.initializeOrderedBulkOp();

    // Match and update only. Do not attempt upsert
    bulk.find({
        "teamId": lineUpPointsGeneralRecord.teamId,
        "round": 0
    }).updateOne({
        "$inc": { "lfPoints": roundPoints },
        "$push": { "roundPoints": roundPoints }
    });

    // Attempt upsert with $setOnInsert only
    bulk.find({
        "teamId": lineUpPointsGeneralRecord.teamId,
        "round": 0
    }).upsert().updateOne({
        "$setOnInsert": lineUpPointsGeneralRecord
    });

    bulk.execute(function (err,updateResult) {
        sails.log.debug(err,updateResult);
    });
});

Make sure your sails-mongo is a latest version supporting the Bulk operations properly be the inclusion of a recent node native driver. The most recent supports the v2 driver, which is fine for this.

Answer 2

I recommend use bulkWrite exemplary code with bulk upsert of many documents:

In this case you will create documents with unique md5. If document exists then will be updated but no new document is created like in classical insertMany.

const collection = context.services.get("mongodb-atlas").db("master").collection("fb_posts");

return collection.bulkWrite(
  posts.map(p => { 
    return { updateOne:
      {
        filter: { md5: p.md5 },
        update: {$set: p},
        upsert : true
      }
    }
  }
  ),
  { ordered : false }
);

https://docs.mongodb.com/manual/reference/method/db.collection.bulkWrite/

Answer 3

Typically I have always set upsert as a property on update. Also update should be able to find the record itself so no need to find it individually.

Depending on the environment the $ may or may not be necessary.

batch.update(
 {team: lineUpPointsRoundRecord.teamId, round: 0},
    {      
      $setOnInsert: lineUpPointsGeneralRecord,
      $inc: {lfPoints: roundPoints},
      $push: {roundPoints: roundPoints},
      $upsert: true
    });