Let's pretend we are in a database tv
that has a collection called shows
. Each show has the
following structure:
{
"id": 1,
"url": "http://www.tvmaze.com/shows/1/under-the-dome",
"name": "Under the Dome",
"type": "Scripted",
"language": "English",
"genres": ["Drama", "Science-Fiction", "Thriller"],
"status": "Ended",
"runtime": 60,
"premiered": "2013-06-24",
"officialSite": "http://www.cbs.com/shows/under-the-dome/",
"schedule": { "time": "22:00", "days": ["Thursday"] },
"rating": { "average": 6.5 },
"weight": 91,
"network": {
"id": 2,
"name": "CBS",
"country": {
"name": "United States",
"code": "US",
"timezone": "America/New_York"
}
},
"webChannel": null,
"externals": { "tvrage": 25988, "thetvdb": 264492, "imdb": "tt1553656" },
"image": {
"medium": "http://static.tvmaze.com/uploads/images/medium_portrait/0/1.jpg",
"original": "http://static.tvmaze.com/uploads/images/original_untouched/0/1.jpg"
},
"summary": "<p><b>Under the Dome</b> is the story of a small town that is suddenly and inexplicably sealed off from the rest of the world by an enormous transparent dome. The town's inhabitants must deal with surviving the post-apocalyptic conditions while searching for answers about the dome, where it came from and if and when it will go away.</p>",
"updated": 1529612668,
"_links": {
"self": { "href": "http://api.tvmaze.com/shows/1" },
"previousepisode": { "href": "http://api.tvmaze.com/episodes/185054" }
}
}
This will search for the first document that matches our criteria.
db.shows.findOne({ name: "Under the Dome" })
We can also specify multiple criteria. Think of this as an implicit and
statement. I say this because mongodb
actually has an operator called $and, which we will cover shortly.
db.shows.findOne({ status: "ended", runtime: 60 })
Think of this as, "give me the first show you can find with "status" of 'ended' AND with a "runtime" of 60". We can also query nested documents as well. Let's say we want to find one document that is on the network "CBS", and has a runtime of 60.
db.shows.findOne({ "network.name": "CBS", runtime: 60 })
This works quite similar to $findOne, but will return multiple documents. Let's say we want to find all of the documents that are "scripted", and have a language of "English".
db.shows.find({ "type": "Scripted", language: "English" })
The syntax is completely identical. Let's do one more before we hop into more advanced queries. This will return shows that have a rating of 6.5
db.shows.find({ "rating.average": 6.5 })
From here on out I will be using the $find method instead of $findOne. Just remember they are nearly identical in syntax.
Comparison operators are quite handy. Here are some of them.
Let's look for shows with a runtime greater than 30.
db.shows.find({ runtime: { $gt: 30 } })
Just to keep it fresh in your mind, you can add additional criteria to this query as well. Below is perfectly acceptable to do.
db.shows.find({ runtime: { $gt: 30 }, "network.name": "CBS" })
Let's look for shows with a runtime greater than or equal to 30.
db.shows.find({ runtime: { $gte: 30 } })
Let's find all documents with a weight less than 80.
db.shows.find({ weight: { $lt: 80 } })
Let's find all documents with a weight less than or equal to 50.
db.shows.find({ runtime: { $lte: 50 } })
We can also list multiple values in an array to match for. The following query will return documents with a network name of HBO or Fox.
db.shows.find({ "network.name": { $in: ['HBO', 'Fox'] } })
We can also even use this to match values in a field that is an array.
db.shows.find({ "genres": { $in: ['Thriller', 'Horror'] } })
In our example document, we have a "genres" array with the following: ["Drama", "Science-Fiction", "Thriller"]
,
our query will match this document because we included "Thriller" in the $in array. More on arrays later.
We can also return documents where a specific value is not equal to a value.
db.shows.find({ language: { $ne: "English" } })
This will return all documents whose weight is equal to or less than 91.
db.shows.find({ $weight: { $not: { $gt: 91 } } })
Since you are smart, you probably are already thinking you could also write this query without $not. We could do:
db.shows.find({ $weight: { $lt: 92 } })
This is just like $in, but works in the opposite manner. This will return documents that do not have a network name of HBO or Fox.
db.shows.find({ "network.name": { $nin: ['HBO', 'Fox'] } })
Let's look for documents where the rating is greater than 3, but less than 8. $and will take an array of objects specifying our criteria. For a document to match, both conditions must be met.
db.shows.find({ $and: [{ "rating.average": { $gt: 3 } }, { "rating.average": { $lt: 8 } }] })
Quite similar to $and, this operator only requires that one of the conditions in the array is met.
db.shows.find({ $or: [{ "rating.average": { $gt: 3 } }, { type: "Scripted" }] })
This was is actually more similar to $and than $or, because this will return documents that fail ALL of the conditions listed in the array.
db.shows.find({ $nor: [{ "rating.average": { $gt: 3 } }, { "rating.average": { $lt: 8 } }] })
This would return documents with rating average less than or equal to 3, and greater than or equal to 8.
Who could forget these? Here are a few of the most important ones.
Let's search for documents that begin with the word "Under".
db.shows.find({ name: { $regex: /^Under /} })
Notice the space after the r, this is necessary because imagine that their was a show that begins with "Understanding", that would match as well. We can get fancier, since we are getting pretty good at querying now.
The following query would look for shows the begin with "Under" or end with "Dome".
db.shows.find({ $or: [{ name: { $regex: /^Under / } }, { name: { $regex: / Dome$/ } }] })
Imagine we want to find all documents whose "runtime" is greater than its "weight".
db.shows.find({ $expr: { $gt: ["$runtime", "$weight"] } })
Arrays are quite powerful, and a data structure you will come across many times.
Each show in our database has a "genres" property which is an array. Our example document looks like this
genres: ["Drama", "Science-Fiction", "Thriller"]
. Let's say we want to fetch all documents who have a genre
of "Science-Fiction".
db.shows.find({ genres: 'Science-Fiction' })
Mongo is smart enough to realize we want to find the word "Science-Fiction" in the genres array. We can even query nested arrays like so:
db.shows.find({ "schedule.days": 'Thursday' })
Using our example genres, let's say we want to find all documents that are "Drama", "Science-Fiction", and "Thriller". Each document MUST have all of these values in their genres array, but ordering doesn't matter. We can do the following:
db.shows.find({ genres: { $all: ['Science-Fiction', 'Drama', 'Thriller'] } })
We can also find documents whose genres array has a specific length. Let's say we do the following:
db.shows.find({ genres: { $size: 2 } })
This will return us documents where the genres array has length of 2. Our example document would not match this query, as it has a length of 3.
For this example, lets pretend we have a new database with a collection called users
. Each user has the following
structure:
{
"name": "Layla",
"hobbies": [
{
"title": "Sports",
"frequency": 3
},
{
"title": "Cooking",
"frequency": 6
}
],
"phone": 131782734
},
What if we wanted to find all documents that have an object like { title: 'Sports', frequency: 3 }
in it's
hobbies array. We can simply do:
db.users.find({ hobbies: { $elemMatch: { title: 'Sports', frequency: 3 } })
That's about it for our MongoDB Queries Cheat Sheet. Hopefully this has helped, and will help you progress towards more advanced queries. As always, if you have any problems understanding any of this, feel free to leave a comment, and I'll make sure it's crystal clear.