[SERVER-28434] upsertAndFetch method for documents that are inserted if not found Created: 22/Mar/17  Updated: 06/Dec/22  Resolved: 21/Apr/17

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

Type: Improvement Priority: Major - P3
Reporter: Samaresh Singh Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query
Participants:
Case:

 Description   

Currently if one needs to insert a document only once for a particular "primary" value then they need to do something like:

db.coll1.findOneAndUpdate({"item":"abc1"},{$setOnInsert:{"item":"abc1","xyz":123}},{upsert:true,returnNewDocument:true})

The above returns the document if it exists already. If not, then it inserts that document. Instead of this can we have a API/method for upsertAndFetch().



 Comments   
Comment by Jason R. Coombs [ 29/Jun/17 ]

In working with Asya at MDBW17, I learned that my last example was in fact incorrect. An "update" of "{}" doesn't mean "update nothing" but means "update to empty", which is not what I intended but also unlikely what a user would have intended.

Instead, I'm looking for an operation of "update nothing". Here are two forms that might represent "update nothing" but which are rejected by the query engine:

> db.coll1.findOneAndUpdate({"item":"abc1"},{$set: {}},{upsert:true,returnNewDocument:true})
2017-06-29T14:30:06.449-0400 E QUERY    [thread1] Error: findAndModifyFailed failed: {
	"ok" : 0,
	"errmsg" : "'$set' is empty. You must specify a field like so: {$set: {<field>: ...}}",
	"code" : 9,
	"codeName" : "FailedToParse"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.findAndModify@src/mongo/shell/collection.js:768:1
DBCollection.prototype.findOneAndUpdate@src/mongo/shell/crud_api.js:823:12
@(shell):1:1

> db.coll1.findOneAndUpdate({"item":"abc1"},null,{upsert:true,returnNewDocument:true})
2017-06-29T14:29:55.092-0400 E QUERY    [thread1] TypeError: can't convert null to object :
DBCollection.prototype.findOneAndUpdate@src/mongo/shell/crud_api.js:782:16
@(shell):1:1

> It may be moot once we have sessions and retriable writes and can offer an idempotent insertOne.

I don't think an idempotent insertOne will solve this use case unless the insert might function as an upsert and return the new document.

But since this use-case seems to be one that MongoDB wishes not to support, I'll instead rely on the Python routine that implements the somewhat unintuitive "$setOnInsert: doc" technique.

Comment by Jason R. Coombs [ 11/May/17 ]

The use case I was looking for was even simpler than the one described above, allowing for upsert and fetch without updating anything.

Of course one can supply the filter as the $setOnInsert to achieve the desired effect:

> doc = {"item": "abc1"}
{ "item" : "abc1" }
> db.coll1.findOneAndUpdate(doc,{$setOnInsert: doc},{upsert:true,returnNewDocument:true})
{ "_id" : ObjectId("5914861a3fb9eab1a60b8fd1"), "item" : "abc1" }

Or to use an update that has no effect:

> db.coll1.findOneAndUpdate({"item":"abc1"},{$unset: {"__nothing":1}},{upsert:true,returnNewDocument:true})
{ "_id" : ObjectId("591486af3fb9eab1a60b8fd8"), "item" : "abc1" }

It seems it would be much less hacky if MongoDB simply allowed the update document to be empty, but it disallows it:

> db.coll1.findOneAndUpdate({"item":"abc1"},{},{upsert:true,returnNewDocument:true})
2017-05-11T11:32:02.016-0400 E QUERY    [thread1] Error: the update operation document must contain at least one atomic operator :
DBCollection.prototype.findOneAndUpdate@src/mongo/shell/crud_api.js:784:1
@(shell):1:1

It seems there's no clean way to upsertAndFetch (but not update) even though it seems that a degenerate update doc would serve exactly this purpose. This behavior is especially surprising as MongoDB allows for empty documents in other operations such as remove. Is there any downside to simply allowing an update doc to be empty, especially when in a case like this, it would be quite useful?

Comment by David Storch [ 21/Apr/17 ]

Since the shell's CRUD API implementation complies with the drivers spec, and per David's comment above the drivers team has chosen not to extend the spec for this case, we are closing this ticket as Won't Fix.

Comment by David Golden [ 17/Apr/17 ]

We've had discussions on the drivers side about this and have chosen in the past not to implement it. It may be moot once we have sessions and retriable writes and can offer an idempotent insertOne.

Comment by Ian Whalen (Inactive) [ 17/Apr/17 ]

The shell just complies with the Driver's CRUD API spec, so in order to see any change here we'd have to see a change with that spec. david.golden should this be moved to the DRIVERS project?

Generated at Thu Feb 08 04:18:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.