MongoDB upsert option is used with update method which creates a new document if the query does not retrieve any documents satisfying the criteria. The default value for this option is false. The upsert option does an insert based on the field and value pairs specified in the update parameter or field and value pairs specified in both query and update parameter.
Upsert option set for update
This operation first searches for the document if not present then inserts the new document into the database.
> db.car.update(
... { name: "Qualis" },
... {
... name: "Qualis",
... speed: 50
... },
... { upsert: true }
... )
WriteResult({
"nMatched" : 0,
"nUpserted" : 1,
"nModified" : 0,
"_id" : ObjectId("548d3a955a5072e76925dc1c")
})
>
The car with the name Qualis is checked for existence and if not, a document with car name “Qualis” and speed 50 is inserted into the database. The nUpserted with value “1” indicates a new document is inserted.
Note that to avoid inserting the same document more than once, create an index on the name field thereby ensuring that the document will be inserted only once for the upsert option on every update specified. If the upsert fails because of duplicate index key error, retrying results in the successful update operation.
Bulk.find.upsert()
The upsert operation can be used for multiple documents or in bulk with the methods-update, updateOne and replaceOne.
The syntax for upsert option is
Bulk.find(<query>).upsert().update(<update>);
Bulk.find(<query>).upsert().updateOne(<update>);
Bulk.find(<query>).upsert().replaceOne(<replacement>);
Upsert option set for find() method in bulk
The upsert operation for multiple documents in find method inserts a single document if the query does not satisfy the criteria as specified in the input parameter.
> var car = db.car.initializeUnorderedBulkOp();
> car.find( { name: "Chevrolet" } ).upsert().update(
... {
... $setOnInsert: { name: "Chevrolet",speed:55,regno:567,color: "AshBrown" },
... $set: { cno:"H768" }
... }
... );
> car.execute();
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 0,
"nUpserted" : 1,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [
{
"index" : 0,
"_id" : ObjectId("548d3b475a5072e76925dc1d")
}
]
})
>
This operation first initialize and creates an unordered bulk list named “car” and then calls the find method with upsert option in update. The Insert field and value pairs are specified with $setOnInsert tag to insert if the document is not present. The $set is specified to update the “cno” for the car if the document already exists. Finally bulk execute method is called to execute either the insert or update statements. Since the document is not present insertion is done first and upon that the cno is updated for the inserted record.
Upsert option set for updateOne method in bulk
This method accepts parameters for updation as field/key and value pairs.
> var car = db.car.initializeUnorderedBulkOp();
> car.find( { name: "Ferrari" } ).upsert().updateOne(
... {
... name: "Ferrari",
... speed:67,
... regno:456,
... color: "ChromeRed"
... }
... );
> car.execute();
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 0,
"nUpserted" : 1,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [
{
"index" : 0,
"_id" : ObjectId("548d57525a5072e76925dc1e")
}
]
})
>
This creates an unordered bulk list with upsert option and inserts the “ferrari” document if not found in the updateOne method using the field and value pairs specified.
Upsert set for updateOne using update operators
This uses the update operators for setting the values for the fields. $setOnInsert and $set are the operators.
> var car = db.car.initializeUnorderedBulkOp();
> car.find( { speed:67,regno:768} ).upsert().updateOne(
... {
... $setOnInsert: { name:"Audi"},
... $set: { speed: 89 }
... }
... );
> car.execute();
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 0,
"nUpserted" : 1,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [
{
"index" : 0,
"_id" : ObjectId("548d57b35a5072e76925dc1f")
}
]
})
>
The value of name is set using $setonInsert and update is done using $set operators.
Upsert option set for replaceOne method in bulk
This performs an insert using the replaceOne method by accepting field and value pairs only as the argument.
> var car = db.car.initializeUnorderedBulkOp();
> car.find( { name: "Skoda" } ).upsert().replaceOne(
... {
... name: "Skoda",
... cno: "H7865",
... speed: 80,
... }
... );
> car.execute();
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 0,
"nUpserted" : 1,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [
{
"index" : 0,
"_id" : ObjectId("548d58205a5072e76925dc20")
}
]
})
>
This performs an insertion of document and this inserted document is the replacement document.
Java Program for MongoDB upsert
In this section we will write a java program to work on the upsert option with update, updateOne and replaceOne methods.
package com.journaldev.mongodb;
import com.mongodb.BasicDBObject;
import com.mongodb.BulkWriteOperation;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.MongoClient;
import com.mongodb.WriteResult;
import java.net.UnknownHostException;
public class MongoDBUpsert {
public static void upsertTrue() throws UnknownHostException {
MongoClient m1 = new MongoClient("localhost");
DB db = m1.getDB("test");
DBCollection coll = db.getCollection("car");
BasicDBObject o1 = new BasicDBObject();
o1.append("$set", new BasicDBObject("name", "Innova"));
BasicDBObject query = new BasicDBObject().append("speed", 56);
WriteResult c1 = coll.update(query, o1, true, false);
DBCursor carcursor = coll.find();
try {
while (carcursor.hasNext()) {
System.out.println(carcursor.next());
}
} finally {
carcursor.close();
}
}
public static void upsertBulkUnorderedDocsForUpdate()
throws UnknownHostException {
// Get a new connection to the db assuming that it is running
MongoClient mongoClient = new MongoClient("localhost");
// //use test as a datbase,use your database here
DB db = mongoClient.getDB("test");
// //fetch the collection object ,car is used here,use your own
DBCollection coll = db.getCollection("car");
// intialize and create a unordered bulk
BulkWriteOperation b1 = coll.initializeUnorderedBulkOperation();
BasicDBObject o1 = new BasicDBObject();
o1.append("$setOnInsert",
new BasicDBObject("name", "innova").append("speed", 54));
o1.append("$set", new BasicDBObject("cno", "H456"));
b1.find(new BasicDBObject("name", "Zen")).upsert().update(o1);
b1.execute();
DBCursor c1 = coll.find();
System.out.println("---------------------------------");
try {
while (c1.hasNext()) {
System.out.println(c1.next());
}
} finally {
c1.close();
}
}
public static void upsertBulkUnordereDocsForUpdateOne()
throws UnknownHostException {
// Get a new connection to the db assuming that it is running
MongoClient mongoClient = new MongoClient("localhost");
// use test as a database,use your database here
DB db = mongoClient.getDB("test");
// fetch the collection object ,car is used here,use your own
DBCollection coll = db.getCollection("car");
// intialize and create a unordered bulk
BulkWriteOperation b1 = coll.initializeUnorderedBulkOperation();
BasicDBObject o1 = new BasicDBObject();
o1.append(
"$setOnInsert",
new BasicDBObject("name", "Xylo").append("speed", 67).append(
"cno", "H654"));
b1.find(new BasicDBObject("name", "Xylo")).upsert().updateOne(o1);
b1.execute();
DBCursor c1 = coll.find();
System.out.println("---------------------------------");
try {
while (c1.hasNext()) {
System.out.println(c1.next());
}
} finally {
c1.close();
}
}
public static void upsertBulkForUpdateOneWithOperators()
throws UnknownHostException {
// Get a new connection to the db assuming that it is running
MongoClient mongoClient = new MongoClient("localhost");
// //use test as a datbase,use your database here
DB db = mongoClient.getDB("test");
// //fetch the collection object ,car is used here,use your own
DBCollection coll = db.getCollection("car");
// intialize and create a unordered bulk
BulkWriteOperation b1 = coll.initializeOrderedBulkOperation();
BasicDBObject o1 = new BasicDBObject();
// insert if document not found and set the fields with updated value
o1.append("$setOnInsert", new BasicDBObject("cno", "H123"));
o1.append("$set", new BasicDBObject("speed", "63"));
b1.find(new BasicDBObject("name", "Santro").append("speed", 654))
.upsert().updateOne(o1);
b1.execute();
DBCursor c1 = coll.find();
System.out.println("---------------------------------");
try {
while (c1.hasNext()) {
System.out.println(c1.next());
}
} finally {
c1.close();
}
}
public static void upsertBulkUnorderedDocsForReplaceOne()
throws UnknownHostException {
// Get a new connection to the db assuming that it is running
MongoClient mongoClient = new MongoClient("localhost");
// //use test as a datbase,use your database here
DB db = mongoClient.getDB("test");
// fetch the collection object ,car is used here,use your own
DBCollection coll = db.getCollection("car");
// intialize and create a unordered bulk
BulkWriteOperation b1 = coll.initializeOrderedBulkOperation();
// insert query
BasicDBObject o1 = new BasicDBObject("name", "Qualis").append("speed",
76).append("color", "Palebrown");
b1.find(new BasicDBObject("name", "Qualis")).upsert().replaceOne(o1);
b1.execute();
DBCursor c1 = coll.find();
System.out.println("---------------------------------");
try {
while (c1.hasNext()) {
System.out.println(c1.next());
}
} finally {
c1.close();
}
}
public static void main(String[] args) throws UnknownHostException {
// invoke all the methods
upsertTrue();
upsertBulkUnorderedDocsForUpdate();
upsertBulkUnordereDocsForUpdateOne();
upsertBulkForUpdateOneWithOperators();
upsertBulkUnorderedDocsForReplaceOne();
}
}
Output of the above program when executed for a collection that doesn’t exists produce following results.
{ "_id" : { "$oid" : "548d59285a5072e76925dc25"} , "speed" : 56 , "name" : "Innova"}
---------------------------------
{ "_id" : { "$oid" : "548d59285a5072e76925dc25"} , "speed" : 56 , "name" : "Innova"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc26"} , "name" : "innova" , "speed" : 54 , "cno" : "H456"}
---------------------------------
{ "_id" : { "$oid" : "548d59285a5072e76925dc25"} , "speed" : 56 , "name" : "Innova"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc26"} , "name" : "innova" , "speed" : 54 , "cno" : "H456"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc27"} , "name" : "Xylo" , "speed" : 67 , "cno" : "H654"}
---------------------------------
{ "_id" : { "$oid" : "548d59285a5072e76925dc25"} , "speed" : 56 , "name" : "Innova"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc26"} , "name" : "innova" , "speed" : 54 , "cno" : "H456"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc27"} , "name" : "Xylo" , "speed" : 67 , "cno" : "H654"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc28"} , "name" : "Santro" , "speed" : "63" , "cno" : "H123"}
---------------------------------
{ "_id" : { "$oid" : "548d59285a5072e76925dc25"} , "speed" : 56 , "name" : "Innova"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc26"} , "name" : "innova" , "speed" : 54 , "cno" : "H456"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc27"} , "name" : "Xylo" , "speed" : 67 , "cno" : "H654"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc28"} , "name" : "Santro" , "speed" : "63" , "cno" : "H123"}
{ "_id" : { "$oid" : "548d59285a5072e76925dc29"} , "name" : "Qualis" , "speed" : 76 , "color" : "Palebrown"}
That’s all for MongoDB upsert method with different scenario examples, we will look into more of MongoDB features in coming posts.