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());
}

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"} 

Prevoius                                                 Next                                                 Home