Mongo's principle design feature is that information associated with a document should be stored with a document rather than spread across tables.
use <database-name>This command creates the db if it does not yet exist and establishes context for subsequent queries (Do not use this command in the sample Mongo DB shell).
Here's an example insert command for a collection of inventory documents:
db.inventory.insertMany( [ { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" }, { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" }, { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" }, { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" }, { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" } ]);
load ("commands.js")
db.collection-name.find(<query>, <projection>).cursor()
db.inventory.find( { status: "D" } )
Here are some examples:
db.inventory.find( { status: "D" } ) db.inventory.find( { status: "D", qty: 75 } )In SQL the second example would be equivalent to the query:
SELECT * FROM inventory WHERE status = "D" AND qty = 75;
{ <field1>: { <operator1>: <value1> }, ... }
db.inventory.find( { status: { $in: [ "A", "D" ] } } ) db.inventory.find( { status: "A", qty: { $gt: 10, $lt: 30 } } )
db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )The equivalent SQL query is:
SELECT * FROM inventory WHERE status = "A" OR qty < 30;
db.inventory.find( { status: { $exists : true } } )This query returns all documents that have a status field
db.inventory.find( { status: { $exists : false } } )This query returns all documents that do not have a status field
db.inventory.find({}).sort({ qty: 1 })sorts the final results in ascending order by qty
db.inventory.find({}).sort({ status: 1, qty: -1 })sorts the final results in ascending order by status and in case of ties in descending order by qty
For the following example queries use the following collection:
db.inventory.insertMany( [ { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" }, { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" }, { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" }, { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" }, { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" } ]);
db.inventory.find( { "size.uom": "in" } )returns all documents whose size document has a "uom" attribute with the value of "in"
db.inventory.find( { "size.h": { $lt: 15 }, "size.uom": "in", status: "D" } )
Use the following collection to execute the example queries in this section:
db.inventory.insertMany([ { item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] }, { item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] }, { item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] }, { item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] }, { item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] } ]);
db.inventory.find( { tags: "red" } )
db.inventory.find( { tags: { $in : ["blank", "blue"] } } )
db.inventory.find( { tags: { $all: ["red", "blank"] } } )All documents except "postcard" get returned.
db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )Returns any documents where one array element is > 15 and one array element is < 20. All of the following arrays would work:
db.inventory.find( { dim_cm: { $elemMatch: { $gt: 15, $lt: 20 } } } )Now [ 10, 17 ] satisfies the query but [10, 25, 40] does not
For the following example queries use the following collection:
db.inventory.insertMany( [ { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] }, { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] }, { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] }, { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] }, { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] } ]);
db.inventory.find( { 'instock.qty': { $lte: 20 } } )This query return all documents where at least one of the instock objects has a qty field ≤ 20.
db.inventory.find( { "instock": { $elemMatch: { qty: 5, warehouse: "A" } } } )This query matches any document where at least one of the instock objects has a qty of 5 and a warehouse equal to "A".
Note that the above query is different than this query:
db.inventory.find( { "instock": { qty: 5, warehouse: "A" } } )The above query only matches instock objects that have exactly these two fields, in exactly this order, and with exactly these values
Another example, this time where we want any documents that have an instock object that matches the condition: 10 < qty ≤ 20
db.inventory.find( { "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20 } } } } )
db.inventory.find( { "instock.qty": { $gt: 10, $lte: 20 } } )This query will be true if there is either one instock object whose qty field is between 10 and 20 or if there are two instock objects such that one's qty field is > 10 and the second's qty field is ≤ 20 (e.g., one whose qty field is 50 and one whose qty field is 5).
Another example that shows how subtle changes to the operators produce much different results:
db.inventory.find( { "instock.qty": 5, "instock.warehouse": "A" } )Again we query on the qty and warehouse fields but now this query is true if there are two instock objects such that one's qty field is 5 and the other's warehouse field is "A". Of course it is also true if one instock object meets both conditions.
db.inventory.find( { status: "A" }, { item: 1, status: 1 } )Returns the specified field and the _id field
db.inventory.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )Suppresses the id field
db.inventory.find( { status: "A" }, { status: 0, instock: 0 } )Prints all fields except the status and instock fields In general:
With the exception of the _id field, you cannot combine inclusion and exclusion statements in projection documents.
db.inventory.find( { status: "A" }, { item: 1, status: 1, "size.uom": 1 } )Returns the item, status, and _id attributes in the outermost document and the uom attribute from the object embedded in the size field.
db.inventory.find( { status: "A" }, { item: 1, status: 1, "instock.qty": 1 } )Prints the instock array as a list of objects but only the qty field appears
The following query uses this schema:
zipcode, [ students { name, school, age } ] db.schools.find( { zipcode: "63109" }, { students: { $elemMatch: { school: 102 } } } )The operation returns documents that have zipcode equal to 63109 and prints the first student from the students array whose school is 102.
The following query uses this schema:
name, school, age, [ grades { mean, grade } ] } ] db.students.find( { grades: { $elemMatch: { mean: { $gt: 70 }, grade: { $gt: 90 } } } }, { "grades.$": 1 } )For each student, $ returns the first object from the grades array whose mean is > 70 and whose grade is > 90
db.students.find( { grades: { $slice: 5 } } )returns the first five documents from each student's grade array. It returns all documents from a student's grade array if there are fewer than 5 documents in that array.
You can do more things with slice, like return a range by skipping some number of entries or return the last documents in an array by using a negative number.
Mongo DB provides three ways to perform aggregation
Mongo DB is designed to return documents, not perform complex computations so the aggregation mechanism is pretty weak unless you become an expert and use some involved syntax that we won't discuss in this course. For example, on the homework assignment I wanted to have you write a query that would compute the average of a quiz array and the average of an exam array. It turns out not to be easy to do with Mongo DB. Often you would do an aggregation computation in a scripting language.
db.orders.aggregate([ { $match: { status: "A" } }, { $group: { _id: "$cust_id", total: { $sum: "$amount" } } } ])
Another example:
db.inventory.insertMany([ { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" }, { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" }, { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" }, { item: "journal", qty: 25, size: { h: 16, w: 21, uom: "cm" }, status: "F" }, { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" }, { item: "postcard", qty: 100, size: { h: 15, w: 15.25, uom: "cm" }, status: "A" }, { item: "postcard", qty: 75, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }, { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" } ]); db.inventory.aggregate([{ $group : {_id: "$item", entries : {$sum : 1}}}]) db.inventory.aggregate([{ $group : {_id: "$item", quantity : {$sum : "$qty"}}}]) db.inventory.aggregate([{ $group : {_id: "$item", "avg_ht" : {$avg : "$size.h"}}}])The first aggregate query counts the number of times each item occurs. Note that we get a count on the number of documents by $summing 1. Don't try to count using the $count function--it does not work with the group operator.
The second aggregate query counts the quantity of each item and the third aggregate query computes the average height of each item.
db.inventory.aggregate([{ $group : {_id: "$item", "avg_ht" : {$avg : "$size.h"}}}, { $sort : { avg_ht : 1 } } ])
db.inventory.aggregate([{ $group : {_id: "$item", item : { $first : "$item" }, quantity : {$sum : "$qty"}}}])This prints the item twice, once as _id and once as item but it's the only field that will be the same for all documents in a group. For example the status, qty, and size fields are different for each document.
For example, create the following document
db.inventory.insertMany([{ _id: 1, "item" : "shirt", sizes: [ 10, 15, 20] }, { _id: 2, "item" : "pant", sizes: [ 32, 34, 36, 40] }])Then the query
db.inventory.aggregate( [ { $unwind : "$sizes" } ] )returns the following results:
{ "_id" : 1, "item" : "shirt", "sizes" : 10 } { "_id" : 1, "item" : "shirt", "sizes" : 15 } { "_id" : 1, "item" : "shirt", "sizes" : 20 } { "_id" : 2, "item" : "pant", "sizes" : 32 } { "_id" : 2, "item" : "pant", "sizes" : 34 } { "_id" : 2, "item" : "pant", "sizes" : 36 } { "_id" : 2, "item" : "pant", "sizes" : 40 }To compute the minimum size for each item, you can write:
db.inventory.aggregate([{ $unwind : "$sizes" }, { $group : { _id : "$item", minSize : { $min : "$sizes" } } } ] )
Map-Reduce allows the user to
db.orders.mapReduce( map → function() { emit( this.cust_id, this.amount ); }, reduce → function(key, values) { return Array.sum( values ); }, { query → query: { status: "A" }, output → out: "order_totals" } )In this query the key parameter matches this.cust_id and the values parameter matches this.amount
For most aggregation operations, the Aggregation Pipeline provides better performance and more coherent interface. However, map-reduce operations provide some flexibility that is not presently available in the aggregation pipeline.
For detailed information on updating documents, go to https://docs.mongodb.com/manual/tutorial/update-documents/
The 3 relevant commands are:
We will use the following schema
{ item, qty, size { h, w, uom }, status }
db.inventory.updateOne( { item: "paper" }, { $set: { "size.uom": "cm", status: "P" }, $currentDate: { lastModified: true } } )Here's an explanation of this update operation:
db.inventory.updateMany( { "qty": { $lt: 50 } }, { $set: { "size.uom": "in", status: "P" }, $currentDate: { lastModified: true } } )
db.inventory.replaceOne( { item: "paper" }, { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 40 } ] } )
Suppose you have the following document for a student:
{ _id: 1, name: "Joe", scores: [50, 99, 73] }The following example appends 89 to the scores array:
db.students.update( { _id: 1 }, { $push: { scores: 89 } } )
Suppose you have the following document for a student:
{ "_id" : 5, "quizzes" : [ { "wk": 1, "score" : 10 }, { "wk": 2, "score" : 8 }, { "wk": 3, "score" : 5 }, { "wk": 4, "score" : 6 } ] }The following command will add a new document to the quizzes array:
db.students.update( { _id: 5 }, { $push: { quizzes: { "wk" : 5, "score" : 7 } } } )
db.students.update( { _id: 5}, { $set : { "quizzes.2" : { wk : 8, score : 10 } } } )
db.students.update( {_id : 5}, { $set : { "quizzes.2.score" : 7 , "quizzes.3.wk" : 6 } } }
db.students.update( {_id : 5, "quizzes.wk" : 3}, { $set : { "quizzes.$.score" : 7 } } )
filter can be any of the filters previously discussed for the find command.