MongoDB : QueryBuilder

QueryBuilder is used to create queries.

In this tutorial, I am going to use below sample data, to write queries using QueryBuilder.

db.employee.insert(
[
  {
    "_id": 1,
    "firstName": "Hari Krishna",
    "lastName": "Gurram",
    "salary": 12345,
    "hobbies": [
      "writing blogs",
      "playing cricket",
      "watching movies",
      "reading books"
    ],
    "address": {
      "office": {
        "street": "Koramangala BDA Complex",
        "city": "Bangalore",
        "state": "Karnataka",
        "country": "India",
        "PIN": "560034"
      },
      "home": {
        "street": "Near panchayat office",
        "city": "Ongole",
        "state": "Andhra Pradesh",
        "country": "India",
        "PIN": "523169"
      }
    }
  },
  {
    "_id": 2,
    "firstName": "Rama Krishna",
    "lastName": "Gurram",
    "salary": 54321,
    "hobbies": [
      "playing cricket",
      "reading books",
      "travelling"
    ],
    "address": {
      "office": {
        "street": "Rupena Agrahara",
        "city": "Bangalore",
        "state": "Karnataka",
        "country": "India",
        "PIN": "560068"
      },
      "home": {
        "street": "Near panchayat office",
        "city": "Ongole",
        "state": "Andhra Pradesh",
        "country": "India",
        "PIN": "523169"
      }
    }
  },
  {
    "_id": 3,
    "firstName": "Jigar",
    "lastName": "Shah",
    "salary": 52456,
    "hobbies": [
      "travelling",
      "watching movies"
    ],
    "address": {
      "office": {
        "street": "TNagar",
        "city": "Chennai",
        "state": "Tamilnadu",
        "country": "India",
        "PIN": "341234"
      },
      "home": {
        "street": "Ganghi Nagar",
        "city": "Delhi",
        "state": "Delhi",
        "country": "India",
        "PIN": "110037"
      }
    }
  },
  {
    "_id": 4,
    "firstName": "Piyush",
    "lastName": "Rai",
    "salary": 65432,
    "hobbies": [
      "travelling",
      "reading philosophy",
      "climbing hills"
    ],
    "address": {
      "office": {
        "street": "Ameerpet",
        "city": "Hyderabad",
        "state": "Andhra Pradesh",
        "country": "India",
        "PIN": "564321"
      },
      "home": {
        "street": "BDA street",
        "city": "Patna",
        "state": "Bihar",
        "country": "India",
        "PIN": "324123"
      }
    }
  },
  {
    "_id": 5,
    "firstName": "Keerthi",
    "lastName": "Parush",
    "salary": 49000,
    "hobbies": [
      "shopping",
      "trecking"
    ],
    "address": {
      "office": {
        "street": "Domlur",
        "city": "Bangalore",
        "state": "Karnataka",
        "country": "India",
        "PIN": "564921"
      },
      "home": {
        "street": "BTM Layout",
        "city": "Bangalore",
        "state": "Karnataka",
        "country": "India",
        "PIN": "234135"
      }
    }
  }
]
)

> db.employee.find({})
{ "_id" : 1, "firstName" : "Hari Krishna", "lastName" : "Gurram", "salary" : 12345, "hobbies" : [ "writing blogs", "playing cricket", "watching movies", "reading books" ], "address" : { "office" : { "
street" : "Koramangala BDA Complex", "city" : "Bangalore", "state" : "Karnataka", "country" : "India", "PIN" : "560034" }, "home" : { "street" : "Near panchayat office", "city" : "Ongole", "state" : "
Andhra Pradesh", "country" : "India", "PIN" : "523169" } } }
{ "_id" : 2, "firstName" : "Rama Krishna", "lastName" : "Gurram", "salary" : 54321, "hobbies" : [ "playing cricket", "reading books", "travelling" ], "address" : { "office" : { "street" : "Rupena Agra
hara", "city" : "Bangalore", "state" : "Karnataka", "country" : "India", "PIN" : "560068" }, "home" : { "street" : "Near panchayat office", "city" : "Ongole", "state" : "Andhra Pradesh", "country" : "
India", "PIN" : "523169" } } }
{ "_id" : 3, "firstName" : "Jigar", "lastName" : "Shah", "salary" : 52456, "hobbies" : [ "travelling", "watching movies" ], "address" : { "office" : { "street" : "TNagar", "city" : "Chennai", "state"
: "Tamilnadu", "country" : "India", "PIN" : "341234" }, "home" : { "street" : "Ganghi Nagar", "city" : "Delhi", "state" : "Delhi", "country" : "India", "PIN" : "110037" } } }
{ "_id" : 4, "firstName" : "Piyush", "lastName" : "Rai", "salary" : 65432, "hobbies" : [ "travelling", "reading philosophy", "climbing hills" ], "address" : { "office" : { "street" : "Ameerpet", "city
" : "Hyderabad", "state" : "Andhra Pradesh", "country" : "India", "PIN" : "564321" }, "home" : { "street" : "BDA street", "city" : "Patna", "state" : "Bihar", "country" : "India", "PIN" : "324123" } }
 }
{ "_id" : 5, "firstName" : "Keerthi", "lastName" : "Parush", "salary" : 49000, "hobbies" : [ "shopping", "trecking" ], "address" : { "office" : { "street" : "Domlur", "city" : "Bangalore", "state" : "
Karnataka", "country" : "India", "PIN" : "564921" }, "home" : { "street" : "BTM Layout", "city" : "Bangalore", "state" : "Karnataka", "country" : "India", "PIN" : "234135" } } }

1.Get Employee firstName and office address where “firstName” is “Rama Krishna”.

DBObject query = QueryBuilder.start().put("firstName").is("Rama Krishna").get();
DBObject project = QueryBuilder.start().put("firstName").is(true).put("_id").is(false).put("address.office").is(true).get();
  
  
DBCursor cursor = collection.find(query, project);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

2.Get Employee id and first, last names where home city is “Bangalore” or “Delhi”.

query = QueryBuilder.start().or(new BasicDBObject().append("address.home.city", "Bangalore"), new BasicDBObject().append("address.home.city", "Delhi")).get();
  
projection = QueryBuilder.start().put("firstName").is(true).put("lastName").is(true).get();
  
System.out.println("Query document is " + query);
System.out.println("projection document is " + projection +"\n");
System.out.println("Result is \n");
cursor = collection.find(query, projection);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

3.Get employee id, firstName and lastName where salary < 20000 and  hobbies include "watching movies".

query = QueryBuilder.start().and(QueryBuilder.start().put("salary").lessThan(60000).get(),QueryBuilder.start().put("hobbies").is("watching movies").get()).get();
  
projection = QueryBuilder.start().put("firstName").is(true).put("lastName").is(true).get();
  
System.out.println("Query document is " + query);
System.out.println("projection document is " + projection +"\n");
System.out.println("Result is \n");
cursor = collection.find(query, projection);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

4. Get employee id, firstName and lastName, where hobbies include  both "watching movies", "reading books".

query = QueryBuilder.start().put("hobbies").all(Arrays.asList(new String[] {"watching movies", "reading books"})).get();
  
projection = QueryBuilder.start().put("firstName").is(true).put("lastName").is(true).get();
  
System.out.println("Query document is " + query);
System.out.println("projection document is " + projection +"\n");
System.out.println("Result is \n");
cursor = collection.find(query, projection);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

5. Get employee id, firstName and lastName, where hobbies include  "travelling", or "trecking".

query = QueryBuilder.start().put("hobbies").in(Arrays.asList(new String[] {"travelling", "trecking"})).get();
  
projection = QueryBuilder.start().put("firstName").is(true).put("lastName").is(true).get();
  
System.out.println("Query document is " + query);
System.out.println("projection document is " + projection +"\n");
System.out.println("Result is \n");
cursor = collection.find(query, projection);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

6. Get employee id, firstName and lastName, where hobbies include  "travelling", or "trecking".

query = QueryBuilder.start().put("hobbies").in(Arrays.asList(new String[] {"travelling", "trecking"})).get();
projection = QueryBuilder.start().put("firstName").is(true).put("lastName").is(true).get();
  
System.out.println("Query document is " + query);
System.out.println("projection document is " + projection +"\n");
System.out.println("Result is \n");
cursor = collection.find(query, projection);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

7. Get employee id, firstName and lastName, where total number of hobbies are 3.

query = QueryBuilder.start().put("hobbies").size(2).get();  
projection = QueryBuilder.start().put("firstName").is(true).put("lastName").is(true).get();
  
System.out.println("Query document is " + query);
System.out.println("projection document is " + projection +"\n");
System.out.println("Result is \n");
cursor = collection.find(query, projection);
while(cursor.hasNext()){
 System.out.println(cursor.next());
}

package com.orient.kalyan.hadoop.training;

import java.util.Arrays;
import com.mongodb.BasicDBObject;
import com.mongodb.DBCursor;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.QueryBuilder;

public class MainTest {

 /* Step 1 : get mongoCLient */
 public static MongoClient getMongoClient(){
  MongoClient mongoClient = null;
   try {
    mongoClient = new MongoClient("localhost", 27017 );
  } catch (Exception e) {
   e.printStackTrace();
  }
   return mongoClient;
 }
 
 public static void main(String[] args) throws Exception {  
  MongoClient mongoClient = getMongoClient();
  
  /* Step 2: Connect to DB */
  DB db = mongoClient.getDB("test");
  
  /*Step 3 : Get collection */
  DBCollection collection = db.getCollection("employee");
  

  System.out.println("Example 1");
  System.out.println("**************************************************");
  DBObject query = QueryBuilder.start().put("firstName").is("Rama Krishna").get();
  DBObject projection = QueryBuilder.start().put("firstName").is(true)
          .put("_id").is(false)
          .put("address.office").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  DBCursor cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
  
  System.out.println("\nExample 2");
  System.out.println("**************************************************");
  query = QueryBuilder.start().or(new BasicDBObject().append("address.home.city", "Bangalore"), 
          new BasicDBObject().append("address.home.city", "Delhi")).get();
  
  projection = QueryBuilder.start().put("firstName").is(true)
          .put("lastName").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
  
  System.out.println("\nExample 3");
  System.out.println("**************************************************");
  query = QueryBuilder.start().and(QueryBuilder.start().put("salary").lessThan(60000).get(),
           QueryBuilder.start().put("hobbies").is("watching movies").get()).get();
  
  projection = QueryBuilder.start().put("firstName").is(true)
          .put("lastName").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
  
  System.out.println("\nExample 4");
  System.out.println("**************************************************");
  query = QueryBuilder.start().put("hobbies").all(Arrays.asList(new String[] {"watching movies", "reading books"})).get();
  
  projection = QueryBuilder.start().put("firstName").is(true)
          .put("lastName").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
  
  System.out.println("\nExample 5");
  System.out.println("**************************************************");
  query = QueryBuilder.start().put("hobbies").in(Arrays.asList(new String[] {"travelling", "trecking"})).get();
  
  projection = QueryBuilder.start().put("firstName").is(true)
          .put("lastName").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
  
  System.out.println("\nExample 6");
  System.out.println("**************************************************");
  query = QueryBuilder.start().put("hobbies").in(Arrays.asList(new String[] {"travelling", "trecking"})).get();
  
  projection = QueryBuilder.start().put("firstName").is(true)
          .put("lastName").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
  
  System.out.println("\nExample 7");
  System.out.println("**************************************************");
  query = QueryBuilder.start().put("hobbies").size(2).get();
  
  projection = QueryBuilder.start().put("firstName").is(true)
          .put("lastName").is(true).get();
  
  System.out.println("Query document is " + query);
  System.out.println("projection document is " + projection +"\n");
  System.out.println("Result is \n");
  cursor = collection.find(query, projection);
  while(cursor.hasNext()){
   System.out.println(cursor.next());
  }
 }
}

Output

Example 1
**************************************************
Query document is { "firstName" : "Rama Krishna"}
projection document is { "firstName" : true , "_id" : false , "address.office" : true}

Result is 

{ "firstName" : "Rama Krishna" , "address" : { "office" : { "street" : "Rupena Agrahara" , "city" : "Bangalore" , "state" : "Karnataka" , "country" : "India" , "PIN" : "560068"}}}

Example 2
**************************************************
Query document is { "$or" : [ { "address.home.city" : "Bangalore"} , { "address.home.city" : "Delhi"}]}
projection document is { "firstName" : true , "lastName" : true}

Result is 

{ "_id" : 3.0 , "firstName" : "Jigar" , "lastName" : "Shah"}
{ "_id" : 5.0 , "firstName" : "Keerthi" , "lastName" : "Parush"}

Example 3
**************************************************
Query document is { "$and" : [ { "salary" : { "$lt" : 60000}} , { "hobbies" : "watching movies"}]}
projection document is { "firstName" : true , "lastName" : true}

Result is 

{ "_id" : 1.0 , "firstName" : "Hari Krishna" , "lastName" : "Gurram"}
{ "_id" : 3.0 , "firstName" : "Jigar" , "lastName" : "Shah"}

Example 4
**************************************************
Query document is { "hobbies" : { "$all" : [ "watching movies" , "reading books"]}}
projection document is { "firstName" : true , "lastName" : true}

Result is 

{ "_id" : 1.0 , "firstName" : "Hari Krishna" , "lastName" : "Gurram"}

Example 5
**************************************************
Query document is { "hobbies" : { "$in" : [ "travelling" , "trecking"]}}
projection document is { "firstName" : true , "lastName" : true}

Result is 

{ "_id" : 2.0 , "firstName" : "Rama Krishna" , "lastName" : "Gurram"}
{ "_id" : 3.0 , "firstName" : "Jigar" , "lastName" : "Shah"}
{ "_id" : 4.0 , "firstName" : "Piyush" , "lastName" : "Rai"}
{ "_id" : 5.0 , "firstName" : "Keerthi" , "lastName" : "Parush"}

Example 6
**************************************************
Query document is { "hobbies" : { "$in" : [ "travelling" , "trecking"]}}
projection document is { "firstName" : true , "lastName" : true}

Result is 

{ "_id" : 2.0 , "firstName" : "Rama Krishna" , "lastName" : "Gurram"}
{ "_id" : 3.0 , "firstName" : "Jigar" , "lastName" : "Shah"}
{ "_id" : 4.0 , "firstName" : "Piyush" , "lastName" : "Rai"}
{ "_id" : 5.0 , "firstName" : "Keerthi" , "lastName" : "Parush"}

Example 7
**************************************************
Query document is { "hobbies" : { "$size" : 2}}
projection document is { "firstName" : true , "lastName" : true}

Result is 

{ "_id" : 3.0 , "firstName" : "Jigar" , "lastName" : "Shah"}
{ "_id" : 5.0 , "firstName" : "Keerthi" , "lastName" : "Parush"}