Insert data with new fields OR update conditionally using updateOne mongodb

Question

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} . I̶f̶ ̶f̶o̶u̶n̶d̶ ̶a̶n̶d̶ ̶i̶f̶ ̶f̶2̶.̶u̶p̶d̶a̶t̶e̶d̶A̶t̶ ̶<̶ ̶i̶n̶p̶u̶t̶f̶2̶.̶u̶p̶d̶a̶t̶e̶d̶A̶t̶ ̶t̶h̶e̶n̶ ̶u̶p̶d̶a̶t̶e̶ ̶w̶h̶o̶l̶e̶ ̶f̶2̶ ̶t̶o̶ ̶i̶n̶p̶u̶t̶f̶2̶.̶ ̶O̶t̶h̶e̶r̶w̶i̶s̶e̶,̶ ̶i̶n̶s̶e̶r̶t̶ ̶a̶ ̶n̶e̶w̶ ̶d̶o̶c̶u̶m̶e̶n̶t̶ ̶w̶i̶t̶h̶ ̶n̶e̶w̶ ̶f̶i̶e̶l̶d̶ ̶f̶1̶ ̶(̶v̶1̶ ̶i̶s̶ ̶a̶ ̶h̶a̶r̶d̶c̶o̶d̶e̶d̶ ̶v̶a̶l̶u̶e̶)̶.

Edit clearer logic:

• If found the id AND f2.updatedAt < inputf2.updatedAt => update whole f2 to inputf2.
• If found the id AND f2.updatedAt >= inputf2.updatedAt => do nothing (no update f2, no create new document).
• If not found the id => create 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.

Answer 1

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

Answer 2

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}