Hi everyone,
I would like to perform an “insert if exists” operation. In particular I would check if exists in the collection a specific document, then if it exists I would like to insert a new document in the same collection, otherwise not. Finally I would know if the insert operation has been performed or not.
Is there some command in mongodb that can help me in doing this?
The short answer is, “if you can’t do it with aggregate
you can’t do it” and of course you can.
1 Like
Hi @Matteo_Tarantino ,
Another option is to use update
with upsert : true
and $setOnInsert
clause.
Now the trick here is to perform a negative filter to cause the upsert to perform an insert when the filter value does exist.
For example I want to insert my document if _id : "xyz"
is in the collection I can:
db.collection.updateOne({_id : { $ne : "xyz"}}, {$setOnInsert :{ field1 : "data" }},{upsert :true});
So when _id : xyz is there the upsert will trigger and set the new document, the output will be how many documents were upserted, use this value to determine if it created the doc or not.
Thanks
Pavel
3 Likes
Hi @Pavel_Duchovny , first of all thanks for the help.
I’m trying to perform this query:
db.editor.updateOne({_id: ObjectId("61e05aa89abca5b90f4e4b00"), $nor: [{userId: "93YQnfW23lREn7122trpSQX4vMD4", groupId: "93YQnfW23lREn7122trpSQX4vMD0"}]},{$setOnInsert: newDoc}, {upsert: true})
So I would like to perform the $setOnInsert if “the _id on the doc is matched, but neither userId
nor groupId
of that document match with the provided value”. However mongo gives me this error:
MongoServerError: E11000 duplicate key error collection: 61c9b3bbf3947d31b13e623f_maps.editor index: _id_ dup key: { _id: ObjectId('61e05aa89abca5b90f4e4b00') }
Why the $setOnInsert gives duplicate error key if in the query there is a match on _id
property? It seems to give this error because it tries to insert exactly that key as _id
property! How to avoid this behavior?
Hi @Matteo_Tarantino ,
Well because the entire filter does not find a document it attempts the insert , why are you specifying an existing _id in this “newDoc”, obviously it will fail…
What are your goals exactly? What is this _id with $nor attempt what are you trying to achieve?
Thanks
Pavel
@Pavel_Duchovny
Just to simplify few things, assume that in one collection each document has a type
field. There exist exactly two types of documents:
type: "resource"
type: "event"
Each event is associated to a specifc resource, so each document of type event
has a property called resourceId
which is valued with the _id
property of the corresponding document of type resource
.
In addition, each document of type resource
has a property userId
, that specifies the owner of that resource.
Now, having clearer in mind what is the design of the database, the goal is:
“When a new event is generated on a resource, create the event on database only if the user who generates the event is the owner of the corresponding resource”.
I would like to do this with in a single round-trip to the mongo server, with the following query:
let resourceId = "..."
let userId = "..."
let event = {...}
`db.editor.updateOne({_id: ObjectId(resourceId), type: "resource", $nor: [{userId: userId}]}, {$setOnInsert: event}, {upsert: true})`
Note that the event object does not have any _id
property specified !! However, when the $setOnInsert
is triggered, mongo tries to create a new document by inserting the _id
that is specified in the query (i.e .: ObjectId (resourceId)
). This generates a duplicate key error
as I showed you above.
How can this be accomplished with a single round-trip?
@Matteo_Tarantino ,
Oh I see, so add a new _id value to this new document so the insert won’t be taken from the filter…
Make sense?
Pavel
@Pavel_Duchovny
Mongo denies it to me, generating the following error:
MongoServerError: Performing an update on the path '_id' would modify the immutable field '_id'
I assume that being an update operation it does not allow the insertion of an _id
as it is immutable, not even within the $setOnInsert
clause. Any suggestions to solve this problem?
Hmmm I will think about it.
Can you avoid the _id in the filter?
Thanks
Pavel
@Pavel_Duchovny
Unfortunately I think I can’t do that, because I have to identify the specific resource and, at the same time, I have to check on that resource the ownership through the userId
property.
@Matteo_Tarantino ,
Well it sounds like the _id is not good for A resource id . _id is a unique index , if you can have same resource id shared across users and groups in separate objects you should use a new field , the uniqueness should be enforced by this “resourceid” , “user” and “group” as a compound index.
If the type is “resource” the “resourceId” field will still represent its id but its not unique right…
Using _id does not seem right , it is only there to get a unique identifier for a single document…
Thanks
Pavel
Hi @Pavel_Duchovny ,
each resource represents a single document, for this reason using the _id
property directly suited my case well. Unfortunately I already have about 50K pre-existing resource
documents that I cannot delete, so to follow your advice I have to:
(1) For each pre-existing resource
document, generate a unique id (I want to use a UUID pre-generated by me)
(2) Update each pre-existing resource
document with the corresponding UUID generated in step (1).
Is there any way to do this in one-shot without using an “updateOne” for every single UUID to be entered? (maybe with an aggregation, I don’t know …)
Hi @Matteo_Tarantino ,
I am not sure I understand, can there be 2 documents with the same resource but different users? According to the logic if a collection has an object with a specific _id and not with the same user or group it should perform an insert of this id. Since the id is unique isn’t just locating the id makes it eligible for existence?? There cannot be 2 documents of the same _id so the $nor does not really make sense…
If you meant another logic let me know. You can have a simple aggregation that will $match on the resource type and place a new field called “resourceId” into the documents using $merge.
db.editor.aggregate([{$match: {
type: 'resource'
}}, {$addFields: {
resourceId: '$_id'
}}, {$merge: {
into: 'editor'
}}])
Other option is to perform a bulk update on every document fetched or an “updateMany” with a pipeline update depending on how impactful each…
@Pavel_Duchovny
Of course it makes sense for checking the existence, but it is not sufficient to check the ownership too. Assume that a user different from the actual owner generates an event on an existing resource, that event must not be created!
If I understand well, that aggregration suits for me only if I add an ObjectId to the property resourceId when I create a new resource. My question is: is it safe to generate a unique ObjectId server-side before send it to mongo (assume there are potentially 100 different servers that can create a new resource at the same time). What is the collision probabity? Is lower/higher than using a UUID?
@Matteo_Tarantino ,
Of course it makes sense for checking the existence, but it is not sufficient to check the ownership too.
There can be only one owner for a specific _id as there cannot be another object with that _id, So if this _id exist you need to create a new _id and this cannot be done with _id and $setOnInsert so for that reason you have to use another field…
I am not sure regarding the colision questions… Maybe someone else can help… Raise it as seperate post…