MongoDB upsert example using Mongo Shell and Java Driver

Filed Under: MongoDB

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages