Queries & Advanced Queries in MongoDB
In MongoDB, to manipulate data or to perform any specific operations, user can write scripts for the Mongo Shell using JavaScript. For users or developers, who are very much familiar with SQL queries, it might be helpful to use SQL for MongoDB collection as well.
v Querying Using Find(), Skip(), limit(), Sort(),
Count(), group(), match() & Aggregation :
//student find records.
db.student.find().pretty();
-----------------------------------------------------------------------------------------
//find all the students where city=Jamnagar.
db.student.find({city:"Jamnagar"}).pretty();
//Same a above
db.student.aggregate({ $match:{city:"Jamnagar"}}).pretty();
-----------------------------------------------------------------------------------------
// find all the students where
percentage<60
db.student.find({percentage:{$lt:60}}).pretty();
//same as above
db.student.aggregate({$match:{percentage:{$lt:60}}}).pretty();
-----------------------------------------------------------------------------------------
// find all the students where
percentage>60
db.student.find({percentage:{$gt:60}}).pretty();
//same as above
db.student.aggregate({$match:{percentage:{$gt:60}}}).pretty();
-----------------------------------------------------------------------------------------
// find all the students where city!=Jamnagar.
db.student.find({city:{$ne:"Jamnagar"}}).pretty();
//same as above
db.student.aggregate({$match:{city:{$ne:"Jamnagar"}}}).pretty();
-----------------------------------------------------------------------------------------
//count
total no of students.
db.student.count();
//same as above
db.student.find().count();
-----------------------------------------------------------------------------------------
//count no of. students percentage < 60
db.student.count({percentage:{$lt:60}})
//same as above
db.student.find({percentage:{$lt:60}}).count();
-----------------------------------------------------------------------------------------//count
the no of students from Jamnagar
db.student.count({city:"Jamnagar"});
//same as above
db.student.find({city:"Jamnagar"}).count();
-----------------------------------------------------------------------------------------//count
students percentage>90
db.student.count({percentage:{$gt:90}});
db.student.find({percentage:{$gt:90}}).count();
-----------------------------------------------------------------------------------------
//count students percentage>=90
db.student.count({percentage:{$gte:90}});
//same as above
db.student.find({percentage:{$gte:90}}).count();
-----------------------------------------------------------------------------------------
//sorting ascending
db.student.find().sort({percentage:1}).pretty();
//same as above
db.student.aggregate({$sort:{percentage:1}});
-----------------------------------------------------------------------------------------
//sorting descending with skip records
db.student.find().sort({percentage:
-1}).skip(5);
//same as above
db.student.aggregate(
{$sort:{percentage:
-1}},
{$skip:5});
-----------------------------------------------------------------------------------------
//sorting ascending with skip records and
limits.
db.student.find().sort({percentage:1}).skip(5).limit(3);
//same as above
db.student.aggregate({$sort:{percentage:1}},
{$skip:5},
{$limit:3});
-----------------------------------------------------------------------------------------
//sorting descending with skip records and
limits.
db.student.find().sort({percentage:
-1}).skip(5).limit(3);
//same as above
db.student.aggregate(
{$sort:{percentage:
-1}},
{$skip:5},
{$limit:3});
-----------------------------------------------------------------------------------------//sum
of all the failed students' percentage (not logical but just for demo)
db.student.aggregate(
{$match:{result:"FAIL"}},
{$group:{_id:"_id",
total:{$sum:"$percentage"}}});
//group students by city and print the
total sorted by total and then city.
db.student.aggregate(
{$group:{_id:"$city",
total:{$sum:1}} },
{$sort:{total:1,city:1}}
);
-----------------------------------------------------------------------------------------
//group students by state and pring the
total
db.student.aggregate(
{ $group:{_id:"$state",
total:{$sum:1}}}
);
-----------------------------------------------------------------------------------------
//state vise max percentage
db.student.aggregate(
{$group:{_id:"$state",
axPercentage:{$max:"$percentage"}}}
);
-----------------------------------------------------------------------------------------//state
wise minimum percentage
db.student.aggregate(
{$group:{_id:"$state",
minPercentage:{$min:"$percentage"}}}
);
-----------------------------------------------------------------------------------------
//group by multiple fields (state and
city)
db.student.aggregate(
{$group:{_id:{"state":"$state","city":"$city"},
total:{$sum:1}}}
);
-----------------------------------------------------------------------------------------
//get the failed students
db.student.aggregate(
{$match:{result:"FAIL"}}
);
-----------------------------------------------------------------------------------------
//get the field students per state and
sorted
db.student.aggregate(
{$match:{result:"FAIL"}},
{$group:{_id:"$state",
failedStudents:{$sum:1}}},
{$sort:{failedStudents:1,_id:1}}
);
v Understanding MapReduce:
Map-reduce
is a data processing paradigm for condensing large volumes of data into useful
aggregated results. In this map-reduce operation, MongoDB applies the map phase
to each input document (i.e. the documents in the collection that match the
query condition). The map function emits key-value pairs. For those keys that
have multiple values, MongoDB applies the reduce phase, which collects and condenses
the aggregated data. MongoDB then stores the results in a collection.
The
map-reduce operation is composed of many tasks, including reads from the input
collection, execution of the map function, execution of the reduce function,
writes to a temporary collection during processing, and writes to the output
collection.
Consider the following
map-reduce operation:
0 Comments