How to query like joins on 2 collections in mongodb using aggregate keyword

To query like JOINS between 2 collections in mongodb, we need to use $aggregate.

Lets see the example:

 
I have 2 collections, one is Employee and another one is Department.

 Employee Collection  
           {    
             "_id" : 1,    
             "name" : {    
                "first" : "Abhi",    
                "last" : "Dev"    
             },    
             "departmentId" : 2
            },    
            {    
             "_id" : 2,    
             "name" : {    
                "first" : "Abc",    
                "last" : "Agrawal"    
             },    
              "departmentId" : 1 
            },    
            {    
             "_id" : 3,    
             "name" : {    
                "first" : "Dhruv",    
                "last" : "Agrawal"    
             },    
             "departmentId" : 1,    
            }  
           }   

Department Collection  
           {    
             "_id" : 1,    
             "name" : "finance" 
            },    
            {    
             "_id" : 2,    
             "name" : "hr" 
            }
           } 

Now I need to fetch employee details for id 2 along with employee's department name. 

The query would be:

db.employee
    .aggregate([
      { $match: { _id: 2 } },
      {
        $lookup: {
          localField: 'departmentId',
          from: 'department',
          foreignField: '_id',
          as: 'departmentName',
        },
      },
      { $unwind: '$departmentName' }
    ]).toArray()

The output would be:

	{    
         "_id" : 2,    
         "name" : {    
         	"first" : "Abc",    
         	"last" : "Agrawal"    
         },
         "departmentName": {
            "name": "finance"
         }

Hope it helps.

How to compare two fields value while querying in mongodb

To compare 2 fields of a document while querying in mongodb, we need to use $where.

Lets see the example to compare 2 fields of a document:

 Employee Collection  
           {    
             "_id" : 1,    
             "name" : {    
                "first" : "Abhi",    
                "last" : "Dev"    
             },    
             "department" : "finance",    
             "joineddate" : "2010-04-10"     
            },    
            {    
             "_id" : 2,    
             "name" : {    
                "first" : "Agrawal",    
                "last" : "Agrawal"    
             },     
             "joineddate" : "2006-07-07"     
            },    
            {    
             "_id" : 3,    
             "name" : {    
                "first" : "Dhruv",    
                "last" : "Agrawal"    
             },    
             "department" : "finance",    
             "joineddate" : "2010-09-07"     
            }  
           }   

Now lets say I need to fetch data which has first and last values are equal.

The query would be:

 db.employee.find({ $where : "this.name.last == this.name.first" })  

The output would be:

{    
             "_id" : 2,    
             "name" : {    
                "first" : "Agrawal",    
                "last" : "Agrawal"    
             }

How to update specific records of array field in collection using MongoDB

To update specific records of an array field in MongoDB, we can achieve that by using JavaScript with Mongo query. Below are the two ways to update the arrays. Lets see both ways to update the data.

Employee Collection:

 {  
           "_id" : 1,    
        "name" : {    
           "first" : "Abhi",    
           "last" : "Dev"    
        },    
        "profile":[ {"department" : "finance", "joineddate" : "2010-04-10"},  
                          {"department" : "marketing", "joineddate" : "2008-05-05"},  
                          {"department" : "hr", "joineddate" : "2006-08-05"}  
                          ]  
      },    
      {    
        "_id" : 2,    
        "name" : {    
           "first" : "Dhruv",    
           "last" : "Sharma"    
        },    
        "profile":[ {"department" : "retail", "joineddate" : "2013-07-10"},  
                          {"department" : "finance", "joineddate" : "2010-05-25"},  
                          {"department" : "hr", "joineddate" : "2007-08-05"}  
                          ]    
      }  

Objective is to update above array's joineddate field where department is finance.
Option 1: Updating the array using JavaScript:

 db.employee.find({}).  
      forEach(function(doc){  
           doc.profile.forEach(function(d){  
                if(d.department == "finance")  
                {       
                     d.joineddate = "2016-12-12"  
                }  
           });            
           db.employee.save(doc);  
      })   

Option 2: Updating array using Mongo Query operators update and set:

 var query = {  
   profile: {  
     $elemMatch: {  
       department: "finance",  
       joineddate: {$ne: "2016-12-12"}  
     }  
   }  
 };  
 while (db.employee.find(query).count() > 0) {  
   db.employee.update(  
     query,  
     { $set: { "profile.$.joineddate": "2016-12-12" } },  
     { multi: true }  
   );  
 }  

How to update field using data from same document's another field

We cannot directly use update method to update the field with same document's another field. We need to first iterate the data and then use save method to update the same.

For example:

Employee Collection

 {    
     "_id" : 1,               
     "name" : {   
          "full_name" : "AD",  
          "first" : "Abhi",    
           "last" : "Dev"    
      },    
      "department" : "finance",    
     "joineddate" : "2010-04-10"     
 }  
   

Lets say I have above collection document that I need to update. Full name of this document should be updated with First and Last field.

 db.employee.find().snapshot().forEach(  
  function (e) {  
   // update document, using its own properties  
   e.name.full_name = e.name.first + " " +e.name.last;  
   // save the updated document  
   db.employee.save(e);  
  }  
 )       

As you can see here, we are using snapshot query modifier. The $snapshot operator prevents the cursor from returning a document more than once because an intervening write operation results in a move of the document.

How to connect MongoDB with authentication in JAVA

To connect MongoDB through JAVA, we will use MongoClient and to fetch and iterate data we will use MongoCollection and FindIterable classes.

As of version 3.2, mongoclient.getDB method is deprecated. The deprecation of this method effectively deprecates the DB, DBCollection, and DBCursor classes, among others. That's the reason we will use the new methods and classes like MongoCollection, MongoDatabase and FindIterable correspondingly.

Full example of MongoDB connection in JAVA

Employee Collection

 {    
             "_id" : 1,    
             "name" : {    
                "first" : "Abhi",    
                "last" : "Dev"    
             },    
             "department" : "finance",    
             "joineddate" : "2010-04-10"     
            },    
            {    
             "_id" : 2,    
             "name" : {    
                "first" : "Alok",    
                "last" : "Agrawal"    
             },     
             "joineddate" : "2006-07-07"     
            },    
            {    
             "_id" : 3,    
             "name" : {    
                "first" : "Dhruv",    
                "last" : "Sharma"    
             },    
             "department" : "finance",    
             "joineddate" : "2010-09-07"     
            }  
           }   

JAVA code for Mongo Connection and fetching the data.

 
import org.bson.Document;

import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;

public class Test {

 public static void main(String[] args) {
String mongoClientURI = "mongodb://username:password@hostname:port/dbname";  
           MongoClient mongoClient = new MongoClient(new MongoClientURI(mongoClientURI));  
           MongoDatabase md = mongoClient.getDatabase("dbname");  
           MongoCollection<Document> mgColl = md.getCollection("employee");  
           Document assortCCObj = new Document("department", "finance");            
           assortCCObj.append("joineddate", new Document("$gt", "2010-01-01"));  
           FindIterable<Document> dt = mgColl.find(assortCCObj);  
           for (Document document : dt) {  
                System.out.println(document.toJson());  
           }  
    }
 }

The above code will connect to mongo and fetch data from employee collection as per the given criteria. 

How to use $EXISTS to check the field is null or not in mongodb

$exists is an element query operator that can be use to check the existense of any field in a document.

Syntax: { field: { $exists: } }

If { $exists: true }, it will matches the documents which contain the field.

If { $exists: false }, it will matches the documents which does not contain the field.

For example:
Employee Collection

 {    
   "_id" : 1,    
   "name" : {    
   "first" : "Abhi",    
   "last" : "Dev"    
   },    
   "department" : "finance",    
   "joineddate" : "2000-04-10"     
  },    
  {    
   "_id" : 2,    
   "name" : {    
   "first" : "Alok",    
   "last" : "Agrawal"    
   },     
   "joineddate" : "2006-07-07"     
  },    
  {    
   "_id" : 3,    
   "name" : {    
   "first" : "Dhruv",    
   "last" : "Sharma"    
   },    
   "department" : "finance",    
   "joineddate" : "2010-09-07"     
  },   
  {    
   "_id" : 4,    
   "name" : {    
   "first" : "Ravi",    
   "last" : "Mann"    
   },    
   "department" : "information",    
   "joineddate" : "2008-04-07"     
  }  
 }    

In above collection, document {id : 2} does not have department field. So, lets say if we need to search the documents which does not have department field, the query would be:

 db.employee.find({department: {$exists : false}})  

Output of Above Query:

 {    
   "_id" : 2,    
   "name" : {    
   "first" : "Alok",    
   "last" : "Agrawal"    
   },     
   "joineddate" : "2006-07-07"     
 }  

How to increase performance while fetching the data from MongoDB

MongoDB is a no-sql database, which can contain huge data in collections. Due to non relational /un-structered data, it takes load of time while fetching it from DB.

We can if query mongo smartly it can fetch the data very fastly. One way to do it is to limit the output data. There would be many cases when the objective of querying the mongo is to just get some specific fields details. For example, I have a collection of employee and I need to search employee first name starts from 'A' and I only need to fetch its first name. now one way is to use below query to fetch the data which will fetch the whole document which mets the criteria:

  db.employee.find({"name" : "/^A/"})  

Output:

{   
   "_id" : 1,   
   "name" : {   
    "first" : "Abhi",   
    "last" : "Dev"   
   },   
   "department" : "finance",   
   "joineddate" : "2000-04-10"      
  },   
  {   
   "_id" : 2,   
   "name" : {   
    "first" : "Alok",   
    "last" : "Agrawal"   
   },   
   "department" : "information",   
   "joineddate" : "2006-07-07"      
  }

But if I limit the data outcome and only specify the fields which I want as in this case I only need to see first name, we can use projection and make it possible as:

  db.employee.find({"name" : "/^A/"},{"name.first":1})  

Output

  {   
   "_id" : 1,   
   "name" : {   
    "first" : "Abhi"
   }   
  },   
  {   
   "_id" : 2,   
   "name" : {   
    "first" : "Alok   
   }      
  }

As we can see on above output it reduces the data, which will enhance the performance of query.

How to use like keyword in mongodb

Like SQL statements we can use LIKE keywords in Mongo as well. We can query mongdb with LIKE search. We can restrict LIKE search as we do in SQL using '%' as

db.employee.find({name: /text/})  //like '%text%'

db.employee.find({name: /^txt/})  //like 'text%'

db.employee.find({name: /text$/})  //like 'text%'

Examples:

Employee Collection

 {   
   "_id" : 1,   
   "name" : {   
    "first" : "Abhi",   
    "last" : "Dev"   
   },   
   "department" : "finance",   
   "joineddate" : "2000-04-10"      
  },   
  {   
   "_id" : 2,   
   "name" : {   
    "first" : "Alok",   
    "last" : "Agrawal"   
   },   
   "department" : "information",   
   "joineddate" : "2006-07-07"      
  },   
  {   
   "_id" : 3,   
   "name" : {   
    "first" : "Dhruv",   
    "last" : "Sharma"   
   },   
   "department" : "finance",   
   "joineddate" : "2010-09-07"      
  }   

Lets say from above collection I need to search for name which has 'h' in it. So, the query would be:

  db.employee.find({"name" : "/h/"},{name:1})  

The above will fetch 2 results from collection as:

  {    
   "_id" : 1,    
   "name" : {    
   "first" : "Abhi",    
   "last" : "Dev"    
   }  
  },  
  {    
   "_id" : 3,    
   "name" : {    
   "first" : "Dhruv",    
   "last" : "Sharma"    
   }   
  }  

Now, lets say I need to search name that start with 'A', so the query would be:

  db.employee.find({"name" : "/^A/"},{name:1})  


Query Output:

  {    
   "_id" : 1,    
   "name" : {    
   "first" : "Abhi",    
   "last" : "Dev"    
   }   
  },  
  {    
   "_id" : 2,    
   "name" : {    
   "first" : "Alok",    
   "last" : "Agrawal"    
   }   
  }   


There are many regex we can use similarly to filter the search as per the need.




Execute MongoDB queries from command prompt

Execute MongoDB queries from command prompt

we can run mongodb queries from command prompt as well. Run command prompt in administrator mode.

Go to the mongo installation path till bin folder.

In windows, it should be like:

 c:\Program Files\Mongo\server\3.2\bin  

Use below command to execute the mongo query :

 mongo <hostname>:<portnumber>/<dbname> -u <user> -p <pass> db.employee.find("department" : "finance")  

The above command will connect to the given DB details and execute the query.

It will show the data in command prompt itself.

How to print specific data from Collection or document using Javascript in MongoDB

How to print specific data from Collection or document using Javascript in MongoDB

Mongo supports javascript queries to handle the data and process as per the need.

For example,

Employee collection

 {  
   "_id" : 1,  
   "name" : {  
     "first" : "Abhi",  
     "last" : "Dev"  
   },  
   "department" : "finance",  
   "joineddate" : "2000-04-10"       
 },  
 {  
   "_id" : 2,  
   "name" : {  
     "first" : "Alok",  
     "last" : "Agrawal"  
   },  
   "department" : "information",  
   "joineddate" : "2006-07-07"       
 },  
 {  
   "_id" : 3,  
   "name" : {  
     "first" : "Dhruv",  
     "last" : "Sharma"  
   },  
   "department" : "finance",  
   "joineddate" : "2010-09-07"       
 }  

Now, lets say I need to print only the name who are in finance department.

 db.employee.find("department" : "finance").forEach(     
   function(doc) {   
    print(doc.name.first + " " + doc.name.last);   
   );  

The above query will print all the employee first name and last name whose department is finance.
  

Example of bulk updates in MongoDB

Mongo Allows to have bulk updates using following operation:

 db.collection.initializeOrderedBulkOp()  

Initializes and returns a new Bulk() operations builder for a collection.

The builder constructs an ordered list of write operations that MongoDB executes in bulk.

Full Example

In this example we will update name from "ABC One" to "ABC Two" in whole collection

 //initialize Bulk operation  
 var bulk = db.test.initializeOrderedBulkOp();   
 db.test.find({"name": /ABC One/})  
   .forEach(function(doc){  
           print (doc.name)      
     name = doc.name.replace("ABC One", "ABC Two")  
           bulk.find({"_id" :doc._id}).  
       updateOne(  
         { $set:   
         {   
           "name": name  
         }}  
       );  
           print(doc.name)                 
   })  
  bulk.execute();