In my previous post, I have discussed about MongoDb, how to install and how to create a database. In this post i am going to discuss about query document(It is like a record or row in relational databases) in MongoDB.
Query Document in MongoDB
We already discussed, to get all the records
|
1 |
db.emp.find(); |
If you worked on MySQL then what you do when you want to retrieve only records with gender ‘M’
|
1 |
select * from emp where gender='M' |
Similarly
|
1 |
db.emp.find({gender:'M'}); |
Let’s search with multiple criteria
Find all the rows with gender ‘M’ and designation ‘TL’
|
1 |
db.emp.find({gender: 'M', $or: [{designation: 'TL'}]}); |
|
1 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e3"), "firstname" : "tang", "lastname" : "sharma", "dob" : "12/04/1986", "designation" : "TL", "nationality" : "India", "gender" : "M" } |
View only firstname and dob
In MySQL
|
1 |
select firstname,dob from emp where gender='M' |
In MongoDB
|
1 |
db.emp.find({gender:'M'},{firstname:1,dob:1}); |
|
1 2 3 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e1"), "firstname" : "raj", "dob" : "09/07/1989" } { "_id" : ObjectId("529e9ecadd5194e25eac38e2"), "firstname" : "robin", "dob" : "10/08/1984" } { "_id" : ObjectId("529e9ecadd5194e25eac38e3"), "firstname" : "tang", "dob" : "12/04/1986" } |
Getting all data except dob
|
1 |
db.emp.find({gender:'M'},{dob:0}); |
|
1 2 3 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e1"), "firstname" : "raj", "lastname" : "kumar", "designation" : "SE", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e2"), "firstname" : "robin", "lastname" : "yadav", "designation" : "Manager", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e3"), "firstname" : "tang", "lastname" : "sharma", "designation" : "TL", "nationality" : "India", "gender" : "M" } |
To view in proper format
|
1 |
db.emp.find({gender:'M'},{dob:0}).forEach(printjson); |
NOTE: you can’t mix the including and excluding thing in same object. Only exception is for _id.
Let’s demonstrate this
I am excluding _id and including dob. It works fine
|
1 |
db.emp.find({gender:'M'},{_id:0,dob:1}); |
|
1 2 3 |
{ "dob" : "09/07/1989" } { "dob" : "10/08/1984" } { "dob" : "12/04/1986" } |
Here check by excluding firstname and including dob. It throws an error.
|
1 |
db.emp.find({gender:'M'},{firstname:0,dob:1}); |
|
1 2 3 4 |
error: { "$err" : "You cannot currently mix including and excluding fields. Contact us if this is an issue.", "code" : 10053 } |
Sorting in MongoDB
MongoDB provides sort command to sort the records.
For Ascending:1
For Descending:-1
|
1 |
db.emp.find().sort({firstname:1}); |
|
1 2 3 4 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e4"), "firstname" : "parul", "lastname" : "iti", "dob" : "13/08/1983", "designation" : "Emp", "nationality" : "India", "gender" : "F" } { "_id" : ObjectId("529e9ecadd5194e25eac38e1"), "firstname" : "raj", "lastname" : "kumar", "dob" : "09/07/1989", "designation" : "SE", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e2"), "firstname" : "robin", "lastname" : "yadav", "dob" : "10/08/1984", "designation" : "Manager", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e3"), "firstname" : "tang", "lastname" : "sharma", "dob" : "12/04/1986", "designation" : "TL", "nationality" : "India", "gender" : "M" } |
|
1 |
db.emp.find().sort({firstname:-1}); |
|
1 2 3 4 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e3"), "firstname" : "tang", "lastname" : "sharma", "dob" : "12/04/1986", "designation" : "TL", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e2"), "firstname" : "robin", "lastname" : "yadav", "dob" : "10/08/1984", "designation" : "Manager", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e1"), "firstname" : "raj", "lastname" : "kumar", "dob" : "09/07/1989", "designation" : "SE", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e4"), "firstname" : "parul", "lastname" : "iti", "dob" : "13/08/1983", "designation" : "Emp", "nationality" : "India", "gender" : "F" } |
To Put Limit on Records
|
1 |
db.emp.find().limit(2); |
|
1 2 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e1"), "firstname" : "raj", "lastname" : "kumar", "dob" : "09/07/1989", "designation" : "SE", "nationality" : "India", "gender" : "M" } { "_id" : ObjectId("529e9ecadd5194e25eac38e2"), "firstname" : "robin", "lastname" : "yadav", "dob" : "10/08/1984", "designation" : "Manager", "nationality" : "India", "gender" : "M" } |
|
1 |
db.emp.find().sort({firstname:-1}).limit(1); |
|
1 |
{ "_id" : ObjectId("529e9ecadd5194e25eac38e3"), "firstname" : "tang", "lastname" : "sharma", "dob" : "12/04/1986", "designation" : "TL", "nationality" : "India", "gender" : "M" } |
Updating Records in MongoDB
|
1 |
db.emp.update({firstname:'parul'},{$set:{designation:'Employee'}}); |
Update employee table with new field salary
|
1 2 3 4 |
db.emp.update({"_id" : ObjectId("529e9ecadd5194e25eac38e1")}, {"$set" : {"salary" : 18000 }}); db.emp.update({"_id" : ObjectId("529e9ecadd5194e25eac38e2")}, {"$set" : {"salary" : 45000 }}); db.emp.update({"_id" : ObjectId("529e9ecadd5194e25eac38e3")}, {"$set" : {"salary" : 20000 }}); db.emp.update({"_id" : ObjectId("529e9ecadd5194e25eac38e4")}, {"$set" : {"salary" : 22000 }}); |
Remove Records
Remove records of an employee whose firstname is raj
|
1 |
db.emp.remove({firstname:'raj'}); |
Remove all record from emp
|
1 |
db.emp.remove(); |
In next tutorial I will discuss about mongodboperator.
