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