MongoDB insert if not exists | Upsert Query Example

In MongoDB, if you want to add or insert any new document in the collection then we use MongoDB insert (db.collection.insert) command. Similarly for update any document we use update (db.collection.update). MongoDB Upsert is a combination of insert and update. In the Upsert query, MongoDB insert if not exists else update the document.

Before directly jump on MongoDB upsert, first we will see some syntax’s of insert and update query

Type of MongoDB Insert

Basically there are 3 types of MongoDB insert

  • InsertOne
    • Only one document is inserted in the collection.
  • InsertMany
    • Insert multiple documents in the collection.

MongoDB InsertMany Query Example

Syntax

db.COLLECTION_NAME.insertMany(document)

We have to specify the collection name in the syntax and array of JSON document which needs to be inserted in the collection.

Example

db.employees.insertMany("
      [{
         "firstName":"John",
         "lastName":"Irani",
         "preferredFullName":"JohnIrani",
         "employeeCode":"E1",
         "region":"CA",
         "phoneNumber":"408-1234567"
      },
      {
         "firstName":"Steve",
         "lastName":"Cook",
         "preferredFullName":"Steve Cook",
         "employeeCode":"E2",
         "region":"LA",
         "phoneNumber":"788-1234567",
         "emailAddress":"[email protected]"
      },
      {
         "firstName":"Jason",
         "lastName":"Thomas",
         "preferredFullName":"Jason Thomas",
         "employeeCode":"E3",
         "region":"CA",
         "phoneNumber":"982-1234567",
         "emailAddress":"[email protected]"
      },
      {
         "firstName":"Liz",
         "lastName":"Helister",
         "preferredFullName":"Liz Helister",
         "employeeCode":"E4",
         "region":"CA",
         "phoneNumber":"688-1255567",
         "emailAddress":"[email protected]"
      }]
)

Output

mongodb insertmany query result

Explanation

We have inserted multiple documents in our employee’s collection with insertMany query. In the case of the collection is not present, MongoDB creates a collection and inserts data.

Type of MongoDB Update

  • UpdateOne
    • Only a single document will be updated.
  • UpdateMany
    • UpdateMany helps us to update more than one documents.

MongoDB Update Query Example

Syntax

db.COLLECTION_NAME.update(
   <filter>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ],
   }
)

We have to specify the filter condition for which we want to perform and update operation. Along with filter condition, we have to specify the document values in JSON format which needs to be updated.

If the filter document is present then it will update the respective values.

Example

We have added multiple employees’ data in our collection. We will try to update the values for one of the employee.

Missing employee emailAddress fields

In inserted employee collection, you can see for the first employee we don’t have “emailAddress” field present in the collection.

We will try to update the “emailAddress” and “phoneNumber” field with MongoDB update query.

db.employees.update(
   {
      "firstName":"John"
   },
   {
      "$set":{
         "emailAddress":"[email protected]",
         "phoneNumber":"888-6669988"
      }
   }
)

Output

We can see MongoDB update query has ran successful. It has found 1 matching document and data has been modified.

Now, we will see the current collection. Whether the new “emailAddress” field is created or not and the “phonenumber” is updated or not?

mongodb update document result employee details updated

As you can see, MongoDB has updated the phoneNumber and inserted the emailAddress field in collection.

MongoDB follows the schema-less architecture for storing data. If any fields are not present in the collection it will create the respective fields on the fly.

MongoDB Upsert Exmaple

MongoDB Upsert helps us to add or update the document on filter condition. If filter document is exists it will update the respective values else MongoDB will insert document if not exists.

SQL Merge statement helps us to achieve same kind of behavior in SQL world.

Syntax

db.COLLECTION_NAME.update(
   <filter>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ],
   }
)

MongoDB Upsert syntax is similar to Update query syntax. After providing the filter condition and update values we have to specify the options in which we can find upsert as property. We have to set it true to achieve the expected behavior, by default it is fault.

Example

db.employees.update(
   {
      "firstName":"Carly"
   },
   {
      "$set":{
         "firstName":"Jason",
         "lastName":"Thomas",
         "emailAddress":"[email protected]",
         "Pincode":559966
      }
   },
   {
      "upsert":true
   })

Output

mongodb upsert query result

Explanation

As you can see, we try to update the document which is not present in the collection. Therefore, MongoDB has inserted the document and return the Upserted count.

Now we will check the existing collection after executing the MongoDB upsert command

> db.employees.find().pretty()
{
        "_id" : ObjectId("5eac25412f69ebde7c40158e"),
        "firstName" : "John",
        "lastName" : "Irani",
        "preferredFullName" : "JohnIrani",
        "employeeCode" : "E1",
        "region" : "CA",
        "phoneNumber" : "888-6669988",
        "emailAddress" : "[email protected]"
}
{
        "_id" : ObjectId("5eac25412f69ebde7c40158f"),
        "firstName" : "Steve",
        "lastName" : "Cook",
        "preferredFullName" : "Steve Cook",
        "employeeCode" : "E2",
        "region" : "LA",
        "phoneNumber" : "788-1234567",
        "emailAddress" : "[email protected]"
}
{
        "_id" : ObjectId("5eac25412f69ebde7c401590"),
        "firstName" : "Jason",
        "lastName" : "Thomas",
        "preferredFullName" : "Jason Thomas",
        "employeeCode" : "E3",
        "region" : "CA",
        "phoneNumber" : "982-1234567",
        "emailAddress" : "[email protected]"
}
{
        "_id" : ObjectId("5eac25412f69ebde7c401591"),
        "firstName" : "Liz",
        "lastName" : "Helister",
        "preferredFullName" : "Liz Helister",
        "employeeCode" : "E4",
        "region" : "CA",
        "phoneNumber" : "688-1255567",
        "emailAddress" : "[email protected]"
}
{
        "_id" : ObjectId("5ead578fba21c5ba105f5000"),
        "firstName" : "Jason",
        "Pincode" : 559966,
        "emailAddress" : "[email protected]",
        "lastName" : "Thomas"
}
{
        "_id" : ObjectId("5ead5a24ba21c5ba105f5028"),
        "firstName" : "Carly",
        "Pincode" : 559966,
        "emailAddress" : "[email protected]",
        "lastName" : "Monty"
}
>

We can see a new document is inserted at the end of the collection by MongoDB Upsert query.

Similarly, we can use Upsert statement not only to insert or update the single document but also the nested document as well .

MongoDB Upsert nested Document example

Example

db.employees.update(
   {
      "firstName":"Carly"
   },
   {
      "$set":{
         "firstName":"Carly",
         "lastName":"Monty",
         "emailAddress":"[email protected]",
         "Pincode":559966,
         "address":{
            "country":"USA",
            "city":"LA"
         }
      }
   },
   {
      "upsert":true
   }
)

Output

mongodb upsert nested document result

Explanation

We have updated the nested “address” document as address in the existing collection.

Conclusion

MongoDB Upsert is a really helpful feature when you have to update or insert multiple documents on certain filter criteria.

Leave a Comment