Question 403215 in Mongodb Question posted in
The official documentation can be found here.

Conditionally update 2 fields OR upsert a document in MongoDB

I’m trying to upsert a document OR update 2 fields in one round trip with an aggregation (or some variation of one of the many update methods) in mongodb.

Example Record I’m trying to upsert:

{ _id: "1", favoriteColor: "blue", updatedAt: 123 }

I want my aggregation/update query+criteria to do the following:

  1. Fetch document with id: 1
  2. If the document doesn’t exist, insert it
  3. If it does exist, check the existing value for the field: ‘favoriteColor’
  4. If they match, do nothing
  5. If they’re different, update the field ‘favoriteColor’, and the field ‘updatedAt’

It’s very easy to see how you would do this in multiple API calls, but not so much with one call.

Every option seems to break down when you add the requirement that I must both be able to insert the document if it doesn’t exist, or update 2 fields.

Tags:

2

Answers


  1. 0

    In my opinion, one of the canonical templates to perform upsert would be using $documents and $merge.

    1. feed $documents with the payload/input documents
    2. $merge to upsert to the collection. For your specific case,
      • on: we just use _id, for the first check
      • whenNotMatched: insert
      • whenMatched: we use a pipeline here to perform a conditional update based on the value of $$new.favoriteColor, which refers to your input documents’ favoriteColor field.
    db.aggregate([
  {
    "$documents": [
      {
        _id: "1",
        favoriteColor: "blue",
        updatedAt: 123
      }
    ]
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": [
        {
          "$set": {
            "favoriteColor": {
              "$cond": {
                "if": {
                  "$ne": [
                    "$favoriteColor",
                    "$$new.favoriteColor"
                  ]
                },
                "then": "$$new.favoriteColor",
                "else": "$favoriteColor"
              }
            },
            "updatedAt": {
              "$cond": {
                "if": {
                  "$ne": [
                    "$favoriteColor",
                    "$$new.favoriteColor"
                  ]
                },
                "then": "$$new.updatedAt",
                "else": "$updatedAt"
              }
            }
          }
        }
      ],
      "whenNotMatched": "insert"
    }
  }
])

    Mongo Playground for not exist case

    Mongo Playground for different favoriteColor field

    Mongo Playground for do nothing case

    The syntax is slightly different in playground, due to some limitation of playground around $documents. The above syntax should work just fine on your shell/application code.

    Login or Signup to reply.
  2. 0

    You could use the pipeline form of update with the $cond operator for the updatedAt:

    db.collection.update(
  {_id: "1"},
  [{$set: {
      favoriteColor: "blue",
      updatedAt: {
        $cond: [
          {$eq: ["$favoriteColor","blue"]},
          "$updatedAt",
          newDate
        ]
      }
  }}],
  {upsert: true}
)

    This will:

    • search for a document with _id:"1", creating it if it doesn’t exit
    • set "favoriteColor" to "blue"
    • if "favoriteColor" was already "blue", leave "updatedAt" unchanged
    • if "favoriteColor" was not already "blue", set "updatedAt" to newDate
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search