[SERVER-13578] add $setOnUpdate to update operation Created: 14/Apr/14  Updated: 06/Dec/22  Resolved: 29/Jun/19

Status: Closed
Project: Core Server
Component/s: Write Ops
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Asya Kamsky Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Do Votes: 23
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-6566 Support conditional updates: $updates Closed
is related to SERVER-10711 $setOnInsert mods should not conflict... Closed
is related to SERVER-14094 Conditional Updates: Conditionally up... Closed
is related to SERVER-2643 Allow Field Name Duplication with Mod... Closed
is related to SERVER-30587 Doing Upsert in Subdocuments when per... Closed
is related to SERVER-51208 New field `$setOnUpdate` will solve s... Closed
Assigned Teams:
Query
Participants:

 Description   

Currently all operations specified in update are applied for update and upsert, and $setOnInsert only applies operations on insert/upsert.

This request is to add a $setOnUpdate for operations that would be applied only on update (and not on upsert/insert) and possibly analogously $incOnUpdate. This would allow handling many of the use cases described in SERVER-2643 and SERVER-10711 (and possibly some others).

Syntax might need to be different as "on update" all operations, not just $set might need to be accessible. Possible example:

db.collection.update({_id:297},
      { $set:{updatedAt:"$currentTime"},
        $setOnInsert:{createdAt:"$currentTime",score:100},
        $setOnUpdate:{returnedAt:"$currentTime"},
        $incOnUpdate:{score:20}
     }, {upsert:true} );



 Comments   
Comment by Ben Rotz [ 04/Jul/19 ]

Thank you for the clarification. Very helpful as always. I've created SERVER-42084 based on your feedback.

Comment by Asya Kamsky [ 03/Jul/19 ]

You might note that this ticket was filed by me over five years ago and I can assure you that what $setOnUpdate was always intended to mean is in the description - that it would be exactly like $set but would only apply to documents that already existed (aka being updated) and not missing and therefore upserted.  That's an exact counterpart to $setOnInsert.

It was not tracking a conditional, "only update this field if you updated other fields."  I don't mind having some clarification discussion in Jira since this is where people come to see if the issue is fixed and how it may have been fixed, so I want them to find examples or links to examples.

If you look at the tickets I linked in my previous comment, they all talk about conditional updates where condition has to do with other fields or actions on those fields, in particular note that SERVER-14094 is exactly what you are attributing to this ticket.  This ticket was never considered for the "update this field if the rest of the updates had some effect" - that was simply not ever part of its description or any of the examples in comments until you highlighted that use case yesterday.  

The other ticket is also closed due to new syntax making this possible.   I agree it's not necessarily simple to write, but it's a complex concept to express something in the language that's inherently a meta-question (you're saying "I want to do this update, but if this update changes my document in any way, I want to amend it and make a slightly different update")

We are working on figuring out what "syntactic sugar" we can add to the pipeline to make it simpler to write all pipelines (update and otherwise) so feel free to open a new feature request for this and we'll triage it together with other pipeline helpers/simplications/new expressions/etc.

Comment by Daniel Santana [ 03/Jul/19 ]

+1

Comment by NOVALUE Mitar [ 03/Jul/19 ]

I think $setOnUpdate is really useful if we are writing queries by hand. It looks like MongoDB with whole pivot to pipelines is making language more suitable for ORMs. Then the verbosity does not matter so much (if queries are still fast).

Comment by Ben Rotz [ 03/Jul/19 ]

Consider the following

 

db.test.drop();
# true
 
db.test.update({a: 1}, {
  $set: {
    a: 1,
    b: 2,
    updated_at: ISODate()
  }, $setOnInsert: {
    created_at: ISODate()
  }}, {
  upsert: true
});
# WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, ...

 

Re-running the last command continuously results in

 

WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

Which is understandably updating the updated_at timestamp each time, but i would prefer to not update the updated_at field if, in fact, nModified < 1 (excluding the updated_at field itself, of course). So, I would assume using the old proposed syntax would look something like:

 

 

# ... assuming the test collection has a single document from example above
db.test.update({a: 1}, {
 $set: {
   a: 1,
   b: 2
 }, $setOnInsert: {
   created_at: ISODate()
 }, $setOnUpdate: {
   updated_at: ISODate()
 }}, {
 upsert: true
});
# WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 0 })

 

So, how to do desired query with new 4.2 syntax? Presumably something like

db.runCommand(
 {
 update: "test",
 updates: [
 {
 q: { a: 1 },
 u: [
 { $set: { a: 1, b: 2,
created_at: {$cond: [
 {$eq:[ {$type:"$_id"}, "missing" ] },
 ISODate(),
 "$created_at"
 ] },
updated_at: {$cond: [
 {$or: [
 $ne:["$a", 1],
 $ne:["$b", 2],
 ]},
 ISODate(),
 "$updated_at"
 ] }
} },
 ],
 upsert: true
 }
 ],
 writeConcern: { w: "majority", wtimeout: 5000 }
 }
)

 

I must be missing something, but the construction of the conditionals for the updated_at becomes super verbose for large documents. I read that $setOnInsert not allowed in pipeline to I used your equivalent suggestion for created_at of type $_id missing.

Anyway, I hate to use Jira as a discussion board, so happy to take this offline if it goes further, just let me know.

 

Comment by Asya Kamsky [ 03/Jul/19 ]

nefiga actually, the way $setOnInsert works, $setOnUpdate is specifically for operations that should apply on update of a document (and not insert/upsert of the document) and don't apply on a per field basis - I think you may be thinking of functionality asked for in SERVER-6566SERVER-14094 or even SERVER-28789 but all of those can be done using the new syntax.

If there is a scenario that's complex (subdocuments/etc.) that some sort of update modifier could handle (the way you envisioned it) but new syntax cannot (or cannot easily), could you provide an example please?

 

Comment by Ben Rotz [ 02/Jul/19 ]

@asya I would argue that this is not quite what was being asked for in this ticket.

For example, I would assume that

db.test.insert({a: 1, b: 2});
db.test.update({a: 1}, {$set: {b: 2}, $setOnUpdate: {c: 3}});

Would NOT result in a field of c: 3 being added. In other words, my definition of $setOnUpdate is that some record actually had > 0 value for nModified, not just that nMatched > 0. I anticipate that such behavior, however, is extremely difficult due to how mongo internals work.

 

Now, I suppose this specific example could be done with some conditional using the new syntax like

{$ne: [b, 2]} //only update if b is already not equal to 2

but to do this for a complex update dealing with subdocuments/etc. would be quite cumbersome, as opposed to a simple $setOnUpdate

Comment by Asya Kamsky [ 30/Jun/19 ]

> Can you elaborate how exactly can one know inside an aggregation pipeline if it is an update or upsert?

If it makes a difference (i.e. sometimes you want to do the same thing on update when a field is missing as on upsert) you can test for existence of _id field. In other words, if your conditional is

{$eq:[ {$type:"$_id"}, "missing"]}


This will be true in case of an upsert (insert) and it cannot be true for an update of an existing document.

This is always true in the first stage of the pipeline (and in later stages, as long as you haven't generated a new _id field).

I've opened a ticket to add a shorthand expression for this since it seems to be a common thing (SERVER-42019)

Comment by NOVALUE Mitar [ 29/Jun/19 ]

> This can be done by using conditional expressions in agg to determine which (update or upsert) this case is.

Can you elaborate how exactly can one know inside an aggregation pipeline if it is an update or upsert?

Comment by Asya Kamsky [ 29/Jun/19 ]

SERVER-40381 implemented support for aggregation expressions to specify update for 4.2. 

You can see some examples here.

This can be done by using conditional expressions in agg to determine which (update or upsert) this case is.

Comment by Henri-Maxime Ducoulombier [ 25/Apr/17 ]

Hey there, any update on the addition of this feature ? That would be GREAT (imho).

Thanks

Comment by Taras [ 12/May/16 ]

no $setOnUpdate yet and conflicting $set with $setOnInsert make no atomic way for this:

> db.test.findOneAndUpdate(
  {x: {$elemMatch: {a: 1}}},
 
  {
     $setOnInsert: {y: 'initial', x: [{a: 1, b: 'info'}]},
     $set: {'x.$.b': 'info'}
  },
 
  {upsert: true}
);
 
< Error: findAndModifyFailed failed: {
	"ok" : 0,
	"errmsg" : "The positional operator did not find the match needed from the query. Unexpanded update: x.$.b",
	"code" : 16836
}

Comment by Kevin Rice [ 15/Apr/16 ]

This would be GREAT! We have data going into an array, but there's no way to indicate that the data should go into an array vs. a dict:

What I want to do is create the array with null values except for my first value. I can't. I try this:

$set :  {  'fieldName.22' : 6 }
$setOnInsert: {   'fieldName' : [None,None,None,...,6, None, None, ...] }

Error message is two updates to same fieldname.

There is NO WAY to have an array here, it has to be a dict, because there's no way to tell the $set that I'm talking about an array offset instead of a dict key.
As it is, I have to (instead) do this:

setOnInsertDict = { }
for i in range(sizeOfArray):
    if i == offsetIndex:
        # would be duplicate of $set
        continue
    setOnInsertDict['ts60data.%s' % (i)] = None
retval = self.myCollection.update_one( 
    { 'key1' : k1, 'key2' : k2 },
    {
        '$set'         : { 
            'ts60data.%s' % (offsetIndex)   : updateValue,
            },
        '$setOnInsert' : setOnInsertDict,
    }, upsert = True)

The above-mentioned concept of a verb / function named $setOnUpdate would solve the problem.

Comment by Carlos Alvidrez [ 17/Mar/16 ]

I'd love to have this feature too. Is there at least a workaround/design pattern to achieve this efficiently?
Thanks

Comment by Andrey Hohutkin [ 20/May/15 ]

It is really important feature.
What a progress with that?

Comment by Scott Molinari [ 15/Apr/14 ]

Thanks Asya.

Scott

Generated at Thu Feb 08 03:32:10 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.