Mongo DB Crib Sheet

General

Using school as the database and student as the collection.

use school;  // Change to use the school database

show collections;  // Show the collections which are available for this database

db.......pretty();  // Use pretty to make sure the output is formatted well

db.student.drop();  // Drop the collection

db.student.stats();  // Get the collection stats

db.student.totalIndexSize();   // Available from the stats but a short cut.  It is important that the indexes fit into memory

Find

db.students.find();

db.students.findOne();

db.students.find({name:'bob'});

db.students.find({name:'bob'}).count();  // Return the number of matches for this find

db.students.find({age:{$gt:15}});  // Find students with age greater than 15

db.students.find({age:{$gt:15, $lt:18}});  // Find students with age greater than 15 and less than 18

db.students.find({name:{$regex : 'q'}, email : {$exists : true}});   // Find all the students whose name includes a 'q' and who have an email 

db.students.find({$or:[{age:15}, {age:16}]});    // Students with age 15 or 16

db.students.find({pets:{$in:['dog','cat']}});    // Students with a pet dog or cat

db.students.find({pets:{$all:['dog','cat']}});    // Students with a pet dog and cat

db.students.find({age:{$gt:15}}, {"_id":0, name:1, age:1});     // Projection which changes the data fields that are returned.

db.students.find( {_id:4}, {pets: {$slice:2}});    // Return the first two elements of the pet array only

db.students.find().sort({age:1});    // Sort by age ascending

db.students.find().limit(20);    // Return the first 20 records

var cursor = db.students.find();

cursor.next();                               // A cursor is returned if the find query is allocated to a variable

db.students.find({$text : {$search : 'bob fred'}});   // Do a search on a text index for all the documents containing 'bob' or 'fred' (case insensitive)

db.students.find({address:{$near: [x,y]}});    // Returns values based on a 2d index in increasing distance often used with a limit(n)

db.students.find({address:{

        $near: {

            $geometry : {

                type : "Point",

                coordinates : [long, lat]},

            $maxDistance : 2000

        }

    }

});    // Returns values based on a 2dspherical index in increasing distance often used with a limit(n).  Searches for Lat & Long with a max distance

Insert

db.students.insert({name: 'Fred', age:17, results:[78.8, 89.9]});

Delete / Remove

db.students.remove({});  // Removes all documents from the collection

db.students.remove({name:'Fred'});  // Removes all Fred entries

db.students.remove({name:'Fred'}, {justOne:true});   // Removes the first 'Fred' entry found

Update

db.country.update({pop:{$gt:2000000}}, {$set: {large_country:'true'}}, {multi:true});      // Update all countries which have a population greater than 2Mil to have a new flag saying large_country=true

db.country.update({_id:'Japan'},{pop='30000000', lang='Japanese'});      // All other entries in the document are removed.

db.country.update({_id:'Japan'}, {$set : {countryCode:'JP'}});    // Add the countryCode = 'JP' to the document with _id = 'Japan'

db.country.update({_id:'Japan'}, {$unset : {countryCode:1}});    // Remove the countryCode key from the document with _id = 'Japan'

db.country.update({_id:'Japan'}, {$push : { languages: "Japanese"}});   // Adds 'Japanese' to the list of languages for 'Japan'

db.country.update({_id:'Japan'}, {$pop : { languages: -1}});   // Removes the first item from the list of languages for 'Japan'

db.country.update({_id:'Japan'}, {$addToSet : { languages: "English"}});   // Adds 'English' to the list of languages if it doesn't exist already

db.country.update({_id:'Japan'}, {$pushAll : { languages: ["Japanese", "English"]}});   // Adds 'Japanese' and 'English' to the list of languages even if they exist already
db.country.update({_id:'GB'}, {_id:'GB', languages:'English', pop:50000000}, {upsert:true}); // Looks to update the object but will insert it if it can't be found

Indexes

Index creation is run in the foreground by default and will lock the collection that is being indexed so that no read or write request will be published.

db.student.getIndexes();

db.student.createIndex({name:1, class:-1});  (1 = ascending, -1 = descending)

db.student.createIndex({name:1}, {unique:true});  // Create a unique index

db.student.createIndex({name:1}, {unique:true, sparse:true});  // Create a unique index which is also sparse.  Used when there could be null / missing values from a document.

db.student.createIndex({name:1}, {background:true});   // Create this index in the background without locking the collection

db.student.createIndex({'name.result':1});  // Create an index into the array of result embedded documents

db.student.createIndex({'name':'text'});  // Create an index into the array of name embedded documents

db.student.createIndex({'location':'2d'});  // Create an index based on a 2d location where 'location' is an array of two coordinates

db.student.createIndex({'location':'2dsphere'});  // Create an index based on a 2d spherical location where 'location' is an array of two coordinates latitude and longitude.

db.student.dropIndex({name:1});

Explain

Use the explain option to see which indexes are being used and other data.

db.student.explain().find({name:1});     // Explain the information about this find request

db.student.explain("executionStats").find({name:1});

db.student.explain("allPlansExecution").find({name:1});

Aggregation

Use the aggregation frame work to do groupings, counts, sums, averages etc

$group
db.student.aggregate([{$group:{_id : "$surname", count:{$sum:1}}}]);  // Count the number of students with the same surname

db.student.aggregate([{$group:{_id : {name : "$name", surname : "$surname"}, exam_avg : {$avg:"$result"}}};   // Use an _id as a document to identify the student.  Then average the exam marks

db.products.aggregate([{$group:{_id: $manufacturer, categories:{$addToSet:"$category"}}}]); // Build an array of the categories which each manufacturer has.  This builds a set so doesn't create duplicates in the array

db.products.aggregate([{$group:{_id: $manufacturer, categories:{$push:"$category"}}}]); // Build an array of the categories which each manufacturer has. $push allows duplicates in the array.

db.products.aggregate([{$group:{_id: $manufacturer, categories:{$max:"$price"}}}]); // Find the max price of a manufacturer product.

db.products.aggregate([{$group:{_id: $manufacturer, categories:{$min:"$price"}}}]); // Find the min price of a manufacturer product.

db.students.aggregate([
    {$group:{_id:{class_id:"$class_id", student_id:"$student_id"}, student_avg : {$avg:"$score"}}},
    {$group:{_id:"$_id.class_id", class_avg: {$avg:"$student_avg"}}}]);  // Average each students marks in a class, then average all the single

db.student.aggregate([{$sort: {category : 1, price : 1}}, {$group:{ _id:"$category", cheapest :{$first: "$price"}}}]);  // Sort by category & price and return the first value = cheapest per category

$project
Allows you to 'reshape' a document eg add keys, remove keys.  To keep a key put $keyName:1 otherwise the key will be removed - with the exception of _id which needs to be explicitly excluded.

db.products.aggregate([{$project :{
        _id:0,
        'maker':{$toLower:$manufacturer}, // Create a new key with the Lower Case of the manufacturer
        'details':{category:"$category",
                       price:{$multiply : [$price, 10]}},   // Create new document of category and 10xprice
        item: $name      // Keep the name as a new key called 'item'
    }}]);

$match
db.student.aggregate([{$match:{category : "tablet"}}]);  // Match values - very similar to find()

$sort
db.student.aggregate([{$sort:{price: 1}}]);  // Sort by price - very similar to sort()

$skip
db.student.aggregate([{$sort:{price: 1}, $skip : 10}]);  // Skip the first 10 records - only use with a sort first

$limit
db.student.aggregate([{$sort:{price: 1}, $limit : 5 }]);  // Only return 5 records - only use with a sort first

$unwind

db.student.aggregate([{$unwind : "$classes"}]); // Unwind the classes that the students have done

Profiling

With profiling on the db.system collection gets profiling information about queries which take longer than the threshold specified.

db.getProfilingLevel();

db.getProfilingStatus();

db.setProfilingLevel(1, 4);  // 1 is the level and 4 is the threshold ms for queries

db.system.profile.find();    // Find queries which have been profiled because they took too long.

db.system.profile.find({millis:{$gt:1000}}).sort({ts:-1});  // Find all the queries that took longer than 1 second sorted in timestamp descending order

mongostat is useful and is similar to iostat on a linux box.

mongotop # // samples the database every # seconds and prints out an overview of where mongo is slow.

Replication

General
rs.status();
rs.conf();      // Show the current configuration of the replica set
rs.slaveOk();   // Allow queries on a secondary in a replica set
rs.help();   // Get help on the replica set commands

Create
Create a replication set called "rs1" with a particular data path and listening on a particular port.
mongod --replSet rs1 --logpath "1.log" --dbpath /data/rs1 --port 27017 --fork

config = { _id : "rs1" , members:[
                                   {_id : 0, host : "192.168.1.1.27017"},
                                   {_id : 1, host : "192.168.1.2.27017"},                                    ]}
rs.initiate(config);