[SERVER-6566] Support conditional updates: $updates Created: 23/Jul/12  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: Improvement Priority: Major - P3
Reporter: Scott Hernandez (Inactive) Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Do Votes: 36
Labels: positional-operator, transactions
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-34973 Upsert querying an array, then pushin... Closed
is duplicated by SERVER-30587 Doing Upsert in Subdocuments when per... Closed
is duplicated by SERVER-40266 Add on modified option to $currentDate Closed
Related
related to SERVER-2476 New $update atomic modifier for chang... Closed
related to SERVER-6399 Refactor update() code Closed
related to SERVER-8777 update the data based on old values a... Closed
is related to SERVER-20203 upsert querying an array field and $p... Closed
is related to SERVER-3326 Support $ positional operator with an... Closed
is related to SERVER-13578 add $setOnUpdate to update operation Closed
is related to SERVER-14094 Conditional Updates: Conditionally up... Closed
is related to SERVER-22102 New update operator for compare-and-s... Closed
is related to SERVER-28789 Add a 'initial value' parameter to $inc Closed
is related to SERVER-30976 Conditional Update/Insert Operation, ... Closed
is related to SERVER-33482 update existing item or push a new it... Closed
is related to SERVER-1014 A modifier to delete a single value f... Closed
is related to SERVER-7894 A $setOnChange command would be very ... Closed
is related to SERVER-10523 Lower/Upper limit for $inc 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-786 Update a size field with addToSet Closed
is related to SERVER-1050 not allowed to $push and $pop to same... Closed
is related to SERVER-1947 can't push and set in one operation Closed
Assigned Teams:
Query
Participants:

 Description   

Add support for multiple conditional updates in a single update operation. An array of match + update will be executed against each found document. All positional matching is done on the update match, not the query. See below for examples.

db.coll.update( query, {$updates: [
	{when:{a:null}, do:{$set:{a:3}}}, // if missing or null set it
	{when:{_id:null}, do:{ $set:/* initial values */}}, // same as $setOnInsert since _id will be null/missing
	
	{when:{votes:{$lt:19}}, do:{$inc:{votes:1}}},// max for field of 20
	{when:{votes:{$gt:20}}, do:{$set:{votes:20}}},// max for field of 20, cleanup bad values
	
	{when:{tags:{$ne:null}}}, do:{$push:{tags:"a"}}},// $push if tags array exists
	{when:{tags:null}, do:{$set:{tags:["a"]}}},// replace null with tags array if incorrectly set to null
	
	{when:{$not: {"users.id":"scott"}}, do:{$push:{users:{id:"scott", views:0 }}}},// Support for "push if missing" (kinda like upsert for arrays)
	{when:{"users.id":"scott"}, do:{$inc:{"users.$.views:1 }}},
 
	//SERVER-1014 example $pull first match of 2 in array
	{when:{vals:2}, do:{$unset:{"vals.$":true}}},// unset array element (sets to null)
	{when:{vals:null}, do:{$pull:{vals:null}},// remove nulls from array
 
	//SERVER-1050 example (no match/do needed -- unconditional)
	{$push:{vals:-200}},
	{$pop:{vals:-1}}},
 
	//SERVER-1947 $set w/positional and $push to same array
	{$set: { "version" : 2, "name" : "NEWNAME"}, $inc: {"versionsCount" : 1 }, $push: {"versions" : { "z" : "anewone" }}}
	{when:{ "versions.version" : 1}, do:{$inc:{"versions.$.versionToInstant" : 1000}}}
]})

The server would execute the query, run through each conditional update element in the array (in order) and apply the update only if the match succeeds for the document.



 Comments   
Comment by Asya Kamsky [ 03/Jul/19 ]

nithin.huliyappa@gmail.com

Unfortunately with current syntax the only way to do that is to create an embedded object and then in the next stage move it to top level which may be a bit unwieldy.

 

Can you give an example you have in mind - we would like to add simpler syntax for such cases and this way we can make sure it accommodates the use case.

 

Comment by Nithin Huliyappa [ 01/Jul/19 ]

@asya  can we update more then one fields using the if-else condition only once? If "No" we need to use the same condition for every field update.

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.

I believe this can handle all of the examples/use cases that were mentioned in the comments.

db.c.update({}, [
      {$set:{a:{$cond:{
            if: {},    // some condition
            then:{} ,   // val1
            else: {}    // val2 or "$$REMOVE" to not set the field or "$a" to leave existing value
      }}}}
]);

Comment by Ewgenij Gawrilow [ 23/Nov/18 ]

A bulkWrite always executes all its parts, while the request here is to perform just one operation, corresponding to the first met criterion, and to skip the rest.  How bulkWrite can help then?

 

Comment by NOVALUE Mitar [ 03/Sep/18 ]

Would bulkWrite available since MongoDB 3.2 help here?

Comment by Eli Revach [ 25/Sep/17 ]

We find this enhancement very useful for one of our production use cases .
We massively update/Upsert the last state of events we read from external systems .
As we don't know what part of the event was change we must update the entire document

Using this enhancement we can calculate HASH based (before the update) on the incoming event and update mongo only if the HASH we calculate is different that what is already stored in mongo (We can't simply filter the HASH on the update filter as it will create new records when no match )

Comment by Glenn Maynard [ 05/Mar/15 ]

That's incorrect. This is building directly on the fundamental concept that Mongo is built on-compare and modify-and just allows more than one of them to be done (on a single document) atomically. That's what's so nice about this: it's extremely simple, and fixes a deep problem that Mongo has today.

Stored procedures are a different beast entirely. This is just a list of queries and updates, not a programming language.

Comment by Christoph Menge [ 05/Mar/15 ]

I think that moving too much logic into the database or in database-related code is the root of all evil. I might have an extreme opinion, but the path of stored procedures and super-duper complex query and update statements for me is a key reason to refrain from SQL. If I write my program in Java, I want the business logic in Java, not in some kind of wicked DSL that was created iteratively. Introducing control-flow statements like $when and $do makes it a full-blown language, with all strings attached.

Comment by srinivasa somepalli [ 07/Feb/15 ]

@Arnaldo. sure can.

Comment by Pierre Le Roux [ 15/Sep/14 ]

I would be very happy to see Glenn Maynard purposes in next Mongodb release.
When we begin in Mongodb, we are sure we don't need transactions as we have atomic modifications on each item.
In the real life, we need to do a lot of updates in order to do conditional updates in arrays.
So we can't really enjoy the atomicity promise.

So a big +1 for me !

Comment by Arnaldo Capo [ 15/Jul/14 ]

I think this feature will make save lives.

Comment by Glenn Maynard [ 05/Feb/14 ]

This would be a big improvement. It will allow doing server-side, atomic updates in a single round trip, which are currently impossible without layering in a lot of extra complexity and making extra round trips. It solves a lot of problems with one straightforward vocabulary. Just rename "when" to $when and "do" to $do, to avoid ambiguity and avoid the JS keyword.

It would avoid more weirdness like $push's $slice sub-modifier. $slice could have just been a simple, standalone operator. It also reduces the need to add more special-case modifiers; for example, $min, $max and $addToSet would all be unnecessary.

Mixing $updates with other modifiers should be disallowed. There's no reason to say { $set: {}, $updates: [] } (just put the $set in the array), and the order of operations would be unclear. I'd recommend just using an array directly instead of a modifier. For example:

db.coll.update(query, [{
    $when: { x: 1 }, $do: { $set: { x: 2 } },
}, {
    y: 10
}]);

It would also help findAndModify. Currently, it's not possible to use findAndModify to read a record, modify it if a condition holds, and return the final document whether or not it was modified. For example, to update a user's top_score and top_score_date, and atomically retrieve the best score regardless of whether the new score was better or not:

result = db.best_score.findAndModify({
    query: { _id: "UserID" },
    update: [{
        $when: {
            $or: [
                { score: { '$exists': false } }, // new record
                { score: { '$lt': 10 } }, // or a better score
            ],
        }, $do: {
            $set: { top_score: 10, top_score_date: Date() }
        }
    }],
    new: true, upsert: true,
});

Comment by David Pfeffer [ 08/Sep/13 ]

This is a feature that is sorely lacking in Mongo. We have to do > 25 queries in some cases to work around the problem.

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