--> To find documents in a collection we have syntax db.<collection Name>.find();
--> db.books.find(); -- it will provide all documents in collection books. It actually returns a cursor.
--> To make the output look a bit pretty we have following command
db.books.find().pretty();
--> To find a document which meet a specific criteria we need to provide the criteria. In mongodb it is done as below
db.books.find({title:'India Unbound'}).pretty(); -- returns 1 record
db.books.find({author: "Amish Tripathy"}).pretty(); --returns 2 records
--> As we can have arrays also in our documents we can query based on them too.
db.books.find({tags : 'shiva'}).pretty(); -- returns 2 records
--> db.books.findOne({title:'India Unbound'}); -- returns the first document that it find which meet the specific criteria.
--> Till now we were getting all fields of a document. To get a specific field or fields we need to use the second parameter of find/findOne methods.
To get specific fields the query will become
db.books.find({tags : 'shiva'},{title:1}).pretty();
The above query will return _id and title only for all eligible documents.
--> To have all documents except for some specific fields we can write
db.books.find({tags : 'shiva'},{tags:0}).pretty();
--> One point to note is that we cannot have mix and match of select and except in the same query except for 1 condition.
We can write a query to get rid of _id field like this
db.books.find({tags : 'shiva'},{title:1,author:1,_id:0});
--> To move further we need to have some more data. so following insert statements are executed.
> db.blogs.insert({author:{name:{first:'sharad',last:'prasoon'},location: 'India'},post:'http://www.dbversityMongoDB.com',views:200,likes : 20});
> db.blogs.insert({author:{name:{first:'sharad',last:'prasoon'},location: 'India'},post:'http://www.dbversityJava.com',views:100,likes : 30});
> db.blogs.insert({author:{name:{first:'alok',last:'thakur'},location: 'India'},post:'http://www.dbversityPython.com',views:500,likes : 300});
> db.blogs.insert({author:{name:{first:'alok',last:'khanna'},location: 'US'},post:'http://laughtolive.com',views:700,likes : 630});
db.blogs.insert({author:{name:{first:'rajesh',last:'khanna'},location: 'US'},post:'http://grandcanyon.com',views:700,likes : 100});
--> In these queries we have a sub-document inside a document. {name:{first:'sharad',last:'prasoon'} is a sub-document.
--> We can even query based on this sub-document.
> db.blogs.find({'author.name.first':'alok'},{post:1,author:1,_id:0});
--> What if we want to get all blogs where likes are more than 100.
> db.blogs.find({'likes':{$gt : 100 }},{post:1,likes:1,_id:0});
--> For likes less than 100
> db.blogs.find({'likes' : {$lt : 100}},{post:1,likes:1,_id:0});
--> for equal
> db.blogs.find({'likes':{$eq : 100 }},{post:1,likes:1,_id:0});
--> similarly
> db.blogs.find({'likes':{$lte : 100 }},{post:1,likes:1,_id:0});
> db.blogs.find({'likes':{$gte : 100 }},{post:1,likes:1,_id:0});
--> If we want to provide both upper and lower boundry that can be done like this
> db.blogs.find({'likes':{$gt : 50,$lt :200 }},{post:1,likes:1,_id:0});
--> To retrieve all document authored by rajesh or alok we can write
> db.blogs.find({'author.name.first':{$in : ['alok','rajesh'] }},{'author.name':1,post:1,_id:0});
--> To negate the above query we use $nin means "not in". This will return all authors except 'alok' and 'rajesh'
> db.blogs.find({'author.name.first':{$nin : ['alok','rajesh'] }},{'author.name':1,_id:0});
--> As the operators worked with sub documents it also woks with arrays.
> db.books.find({tags : { $in : ['shiva','ganesh']}},{title:1,tags:1,_id:0});
{ "title" : "The immortals of meluha", "tags" : [ "shiva", "mythology" ] }
{ "title" : "Secret of the Nagas", "tags" : [ "shiva", "mythology" ] }
--> We received 2 documents as name 'shiva' is present in both. However if we replace $in with $all it will not retrieve any record as it will expect both the taga to be present in the document.
> db.books.find({tags : { $all : ['shiva','ganesh']}},{title:1,tags:1,_id:0}); -- no records
--> However following query will retrieve records.
> db.books.find({tags : { $all : ['shiva','mythology']}},{title:1,tags:1,_id:0});
--> Note that $in, $nin and $all needs array to operate on.
--> If we want to retrieve documents not authored by sharad we need to execute following command.
> db.blogs.find({'author.name.first' : { $ne : 'sharad'}},{title:1,'author.name.first':1,_id:0});
--> $ne command is for not equals.
--> Now we will discuss about boolean operators
> db.blogs.find({$or : [{views : { $lt : 300 }},{'author.location':'US'}]},{views:1,'author.location':1,_id:0});
--> Here $or operator will take an array of qualifiers. If any of the qualifier is satisfied the document will be displayed. At least one of the qualifier should match. Similarly not or operator is $nor , none of the qualifiers should match.
--> Like logical "or" we have logical "and" too. In this case all the qualifiers should match.
> db.blogs.find({$and : [{views : {$gt : 600}},{'author.location' : 'US' }]},{'author.location':1,views : 1});
--> The above query will return only those records which are having views > 600 and 'author.location' as 'US'.
--> As mongodb is schema-less so some of the documents can have a field which other documents are not having. We have $exists to figure out if a field is present in a document or not.
> db.books.find({author:{$exists : true}},{title:1,author:1,_id:0});
--> The above query returns all the documents that has author field in books collection.
--> $exists takes value as false also.
--> $elemMatch is used if you want to compare against documents present inside an array.
--> $where can also be used for querying but not recommended. A function can also be used as a query parameter.
> var f = function(){return this.author.name.first ==='sharad'};
>db.blogs.find(f);
>db.blogs.find({$where : f});
--> To get distinct tags in books collection we can write following query.
> db.books.find({},{tags:1})
> db.books.distinct('tags');
--> We will try to implement "group by" in mongodb. In below query we will try to use "group by" location on our "blogs" collection.
> db.blogs.find({},{'author.location':1,likes:1,_id:0});
{ "author" : { "location" : "India" }, "likes" : 20 }
{ "author" : { "location" : "India" }, "likes" : 30 }
{ "author" : { "location" : "India" }, "likes" : 300 }
{ "author" : { "location" : "India" }, "likes" : 630 }
{ "author" : { "location" : "US" }, "likes" : 100 }
> db.blogs.group({
key:{'author.location':1}, // select clause
cond:{}, //where clause
reduce : function(curr ,result){
result.totalLikes += curr.likes;
}, //reduce function takes each record as an input in "curr" variable and the aggregates its "likes"
initial:{totalLikes:0} // sum of all "likes" will be added in this variable
}); //query ends here
[
{
"author.location" : "India",
"totalLikes" : 980
},
{
"author.location" : "US",
"totalLikes" : 100
}
]
--> If we want to know the number of records from each location in above case. then the query will go something like this.
> db.blogs.group({
key:{'author.location':1},
cond:{},
reduce:function(curr,result){
result.totalLikes += curr.likes;
result.recordCount++;
},
initial:{totalLikes : 0, recordCount : 0}
});
[
{
"author.location" : "India",
"totalLikes" : 980,
"recordCount" : 4
},
{
"author.location" : "US",
"totalLikes" : 100,
"recordCount" : 1
}
]
--> There is another way to group. This one is using aggregation pipeline. This method has some limitations on value types and result size but complexity is greatly reduced. aggregation pipeline consist of stages. Each stage transform the document as they pass through.
> db.blogs.aggregate([{ $match : {}}, \\ where clause
{ $group : { _id: '$author.location',total : {$sum : '$likes'}}} \\ grouping on likes
]);
{ "_id" : "US", "total" : 100 }
{ "_id" : "India", "total" : 980 }
--> now we will look at some operation to manipulate data we get from database.
> db.blogs.find({'author.name.first':'sharad'}).count();
2
--> The same operation can be done like this.
> db.blogs.count({'author.name.first':'sharad'});
2
--> To sort title field in ascending and descending order following command will be executed
> db.blogs.find({},{post: 1,_id:0}).sort({post:1}); \\ ascending
> db.blogs.find({},{post : 1, _id: 0 }).sort({post : -1}); \\descending
--> To have records in descending order of likes.
> db.blogs.find({},{post: 1,likes:1,_id:0}).sort({likes:-1});
--> To get one having maximum number of likes.
> db.blogs.find({},{post: 1,likes:1,_id:0}).sort({likes:-1}).limit(1);
--> To get one having second maximum number of likes.
> db.blogs.find({},{post: 1,likes:1,_id:0}).sort({likes:-1}).skip(1).limit(1);
--> we can use skip and limits in case of pagination.
Ask Question
