skip to Main Content

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.

2

Answers


  1. Chosen as BEST ANSWER

    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}
    

  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search