Upsert in MongoDB
In MongoDB, upsert is an option that is used for update operation e.g. update(), findAndModify(), etc. Or in other words, upsert is a combination of update and insert (update + insert = upsert). If the value of this option is set to true and the document or documents found that match the specified query, then the update operation will update the matched document or documents. Or if the value of this option is set to true and no document or documents matches the specified document, then this option inserts a new document in the collection and this new document have the fields that indicate in the operation. By default, the value of the upsert option is false. If the value of upsert in a sharded collection is true then you have to include the full shard key in the filter.
Syntax:
upsert: <boolean>
The value of upsert option is either true or false.
Now we will learn the usage of the upsert option:
Upsert with findAndModify() method:
We can use upsert option with findAndModify() method. In this method, the default value of this option is false. If we set the value of this option is true, then the method performs one of the following operation:
- If a document or documents found that matches the given query criteria, then the findAndModify() method updates the document/documents.
- If no document/documents match the given query criteria, then the findAndModify() method inserts a new document in the collection.
Syntax:
db.Collection_name.findAndModify(
{
selection_criteria:<document>,
sort: <document>,
remove: <boolean>,
update: <document>,
new: <boolean>,
fields: <document>,
upsert: <boolean>,
bypassDocumentValidation: <boolean>,
writeConcern: <document>,
collation: <document>,
arrayFilters: [ <filterdocument1>, … ]
})
For example:
In this example we are working with
Database: gfg
Collection: employee
Database: three documents that contains details of employees
Now we are going to insert a new document in employee collection by setting the value of upsert option to true.
db.employee.findAndModify({query:{name:"Ram"}, update:{$set:{department:"Development"}}, upsert:true})
Here, no document matches the name “Ram”, so the findAndModify() method inserts a new document that contains two fields(i.e., name: “Ram” and department: “Development”) because the value of the upsert option is set to true.
Upsert with update() method:
We can use upsert option with update() method. In this method, the default value of this option is false. If we set the value of this option is true, then the method performs one of the following operations:
- If a document or documents found that matches the given query criteria, then the update() method updates the document/documents.
- If no document/documents match the given query criteria, then the update() method inserts a new document in the collection.
Note: To prevent MongoDB from inserting the same document more than once, create a unique index on the name field. With a unique index, if multiple documents want the same update with upsert: true, only one update operation successfully inserts a new document.
Syntax:
db.Collection_name.update({Selection_criteria}, {$set:{Update_data}}, {
upsert: <boolean>,
multi: <boolean>,
writeConcern: <document>,
collation: <document>,
arrayFilters: [ <filterdocument1>, … ],
hint: <document|string>
})
For example:
In this example, we are working with
Database: gfg
Collection: employee
Database: three documents that contains details of employees
Now we are going to insert a new document in employee collection by setting the value of upsert option to true.
db.employee.update({name:"Priya"}, {$set: {department: "HR"}},{upsert:true})
Here, no document matches the name “Priya”, so the update() method inserts a new document that contains two fields(i.e., name: “Priya” and department: “HR”) because the value of the upsert option is set to true.
Upsert with Operator Expressions:
If no document matches the filter from the given collection and the update parameter is a document that contains update operators, also the value of upsert option is set to true, then the update operation creates new documents from the equality clauses in the given query parameter and applies the expressions from the update parameter. Or in other words, when the value of upsert option is true and no document matches the given filter, then the update operation inserts a new document in the given collection, and the fields inserted in this new document are the fields that specify in the query and update documents.
Example:
In this example, we are working with
Database: gfg
Collection: example
Database: five documents that contains details of students
Now we are going to insert a new document in the example collection by setting the value of the upsert option to true.
db.example.update({Name: "Rekha"}, // Query parameter {$set: {Phone: '7841235468 '}, // Update document $setOnInsert: {Gender: 'Female'}}, {upsert: true})
Here, the update() method creates a new document with field “Name: Rekha” from the query condition and then applies the $set and $setOnInsert operations to this document.
Upsert with Replacement Document:
If no document matches the filter from the given collection and the update parameter contains a replacement document, also the value of upsert document is set to true, the update operation inserts a new document in the collection and the fields inserted in this new document are the fields that specify in the replacement document. Here, if the replacement document contains an _id field then MongoDB does not create a new _id field for the new document. Or if the replacement document does not contain an _id field then MongoDB does create a new _id field for the new document.
Note: You are not allowed to specify different _id field values in the query parameter and replacement document. If you do, then you will get errors.
Example:
In this example, we are working with
Database: gfg
Collection: example
Database: four documents that contains details of students
Now we are going to insert a new document in the example collection by setting the value of the upsert option to true.
db.example.update({Name:"Hema"}, // Query parameter {Name:"Hema", Phone:8332564578}, // Replacement document {upsert:true})
Upsert with Aggregation Pipeline:
The aggregation pipeline is a multi-stage pipeline, so in each state, the documents taken as input and produce the resultant set of documents now in the next stage(id available) the resultant documents taken as input and produce output, this process is going on till the last stage. The pipeline can have 1 to n number of stages
If no document matches the given filter and the update parameter contains aggregation pipeline, also the value of upsert option is set to true then update operation inserts a new document in the collection. This new document is created from the equality clause present in the query parameter and after that applies the pipeline to the document to create the document to insert.
Example:
In this example, we are working with
Database: gfg
Collection: employee
Database: three documents that contains details of employees
Now we are going to insert a new document in the employee collection by setting the value of the upsert option to true.
db.employee.update({name:”Ram”}, [{$set: {department: “HR”, age:30}}],{upsert:true})
Upsert with Dotted _id Query:
Till now, we have studied that the update() method can modify data in the collection based on the query and if any matching document is not found with help of upsert option new field can be added to the collection. But upsert with a dotted _id query is an exception and if you attempt to insert a document in this way MongoDB will show you an error.
Example:
Consider the following update operation. Since the update operation specifies upsert:true and the query specifies conditions on the _id field using dot notation, then the update will result in an error when constructing the document to insert.
db.employee.update({“_id.name”:”Roma”, “_id.uid”:0},{age:20}, {upsert:true})
Contact Us