Insert data with new fields OR update conditionally using updateOne mongodb-CodePudding

I have a document with format like this:

{
  "f1": "v1",
  "f2": {
     "id": 1,
     "sub": "subv",
     "updatedAt": 123
   }
}

I have an another source that give me a inputf2 object. I want to write an upsert query to find document with matching filter {"f2.id": inputf2.id} . If found and if f2.updatedAt < inputf2.updatedAt then update whole f2 to inputf2. Otherwise, insert a new document with new field f1 (v1 is a hardcoded value). Example:

Input:

{"id": 1,"sub": "newsubv","updatedAt": 100}

Because 100 < 123. id = 1 will not be updated. Output:

{
  "f1": "v1",
  "f2": {"id": 1,"sub": "subv","updatedAt": 123}
}

Input:

{"id": 1,"sub": "newsubv","updatedAt": 150}

Because 150 > 123. id = 1 will be updated. Output:

{
  "f1": "v1",
  "f2": {"id": 1,"sub": "newsubv","updatedAt": 150}
}

Input:

{"id": 2,"sub": "newsubv","updatedAt": 100}

Because input id = 2 is not found in db. It will be inserted whatever updatedAt is. Output:

{
  "f1": "v1",
  "f2": {"id": 1,"sub": "subv","updatedAt": 123}
},
{
  "f1": "v1",
  "f2": {"id": 2,"sub": "newsubv","updatedAt": 100}
}

I tried with both 2 types of update, update document or update aggregattion pipeline, but seem that any of them fit my requirement.

With update document

I can use $setOnInsert but can't set f2 with f2.updatedAt < inputf2.updatedAt condition:

db.collection.update({
  "f2.id": "1"
},
{
  "$set": {
    // Can not check updatedAt = 100 < 123 and then do nothing
    "f2": {
      "id": 1
      "sub": "newsubv",
      "updatedAt": 100
    }
  },
  "$setOnInsert": {
    "f1": "v1"
  }
},
{"upsert": true});

With update aggregation pipeline

I can update f2 with f2.updatedAt < inputf2.updatedAt condition with a bit tricky by cloning f2 to new field oldf2, then apply the condition and remove oldf2. But in aggregation, there is no $setOnItem function:

db.test.updateOne(
{"f2.id": 1},
[
    {$set: {"oldf2": "$f2"}},
    {
        $set: {
            "f2": {
                $cond: [{$lt: ["$f2.updatedAt", 100]}, {
                    "id": 1,
                    "sub": "newsubv",
                    "updatedAt": 100
                }, "$oldf2"]
            }
        }
    },
    {$set: {"oldf2": "$$REMOVE"}},
    // How to apply something like $setOnInsert function?
],
{"upsert":true})

I must use updateOne because there is a list of f2 item and I want to batch update these items in bulkWrite.

Noted: f2.id is an unique field in collection

Can anyone help me to write query for this logic? Thank you guys very much.

CodePudding user response：

You can use the first method almost as you did, just with a condition.

Edit: But if you want to change f2.id in case of insert, you should separate the f2, like this:

db.collection.update({
  "f2.id": 1,
  "f2.updatedAt": {
    $lte: inputf2.updatedAt // Here you can check updatedAt = 100 < 123 and then stop updating
  }
},
{
  "$set": {
    "f2.sub": "newsubv",
    "f2.updatedAt": 100
  },
  "$setOnInsert": {
    "f1": "v1",
    "f2.id": newF2,
  }
},
{
  "upsert": true
})

You can see it on the playground And you can play with the inputf2.updatedAt to be 100 or 150 to see both cases that I pasted here:

If f2.updatedAt >= inputf2.updatedAt, we will get two different documents on the db. The new one will have inputf2.updatedAt, but with new id, as this should be a unique id:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "f1": "v1",
    "f2": {
      "id": 1,
      "sub": "subv",
      "updatedAt": 123
    }
  },
  {
    "_id": ObjectId("625b1873cdbb97ce928d8898"),
    "f1": "v1",
    "f2": {
      "id": 2,
      "sub": "newsubv",
      "updatedAt": 100
    }
  }
]

Otherwise, f2.updatedAt < inputf2.updatedAt : the document on the db will be updated, but will keep its original id:

  {
    "_id": ObjectId("5a934e000102030405000000"),
    "f1": "v1",
    "f2": {
      "id": 1,
      "sub": "newsubv",
      "updatedAt": 150
    }
  }

BTW, there is no problem to use this method inside a bulk update

CodePudding user response：

Finally, I can write the query. Here is my solution:

db.test.updateOne(
{"f2.id": 1},
[{
  $set: {
    "f2": { $cond: [
        {$lt: ["$f2.updatedAt", 100]}, // time check condition
        {"id": 1,"sub": "newsubv","updatedAt": 100}, // overwrite value
        "$f2" // no change, return origin value
    ]},
    "f1": {$ifNull: ["$f1", "v1"]}, // this can do the same as $setOnInsert
  }
}],
{"upsert":true}
)

Testcase:

Input 1: Mongo Playground

{"id": 1,"sub": "newsubv","updatedAt": 100}

Input 2: Mongo Playground

{"id": 1,"sub": "newsubv","updatedAt": 200}

Input 3: Mongo Playground

{"id": 2,"sub": "newsubv","updatedAt": 100}