MongoDB for Developers with Python Transcripts
Chapter: MongoDB's shell and native query syntax
Lecture: Basic querying

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:01 Now let's see how we do probably the main thing
0:04 that you do in databases and that is query.
0:06 So here we are in the Mongo shell still,
0:09 and I'm using the bookstore database,
0:11 so what I want to do is find some particular books;
0:14 remember, we have book, publisher, test
0:18 we can really remove test, not actually do anything, and then user,
0:21 so those three actually used one.
0:23 Let's go and remove test just so that it is gone.
0:29 Now we have the ones we're actually using.
0:31 Now, when we're getting started, it's probably worthwhile to just say db.Book.find
0:36 as an empty query just like kind of select star if you will,
0:39 you know show all of the things that are in there,
0:42 there, that's totally obvious what that is, right,
0:44 you see the structure, right if you can like kind of exist in the matrix
0:47 you could entirely see the structure there, but let's do that better.
0:50 Notice a certain number of items, I don't know it's 20 or 50 were returned
0:54 there's actually like a quarter million books,
0:57 so we didn't get them all which is good,
0:59 so if we want more, we just type "it" and it will actually get more and so on.
1:03 Okay, so this is not super helpful, let's make this more helpful;
1:06 so here we can go over and say I want this to be like that pretty
1:10 and in fact, if I just want one of them I could just say limit this to the first one,
1:14 or let's just say limited to two so we see a couple of examples.
1:17 There, now we're starting to see the structure.
1:23 Let's go here, ok so now we've got a book,
1:26 right here you can see the top level document,
1:29 it doesn't put the results in arrays,
1:32 like it doesn't print out an array it just prints
1:34 a whole bunch of individual results in this case two,
1:36 so here we have our id, there's always an underscore id in the database
1:40 like this is the name of the primary key,
1:42 you can have it look different in Python,
1:44 you can say this thing maps actually to the primary key
1:47 when you are modeling this with classes and so on,
1:50 but down at the Javascript and the MongoDB level,
1:53 this is always the name of the primary key,
1:55 if you don't give it one when you insert the thing, it's auto generated,
1:58 and so if you don't have a great reason to care about what id looks like
2:02 probably using this object id is the best bet.
2:05 So our books have isbns, they have titles, they have authors,
2:08 I kind of wish it was little more pythonic with lower case ts and as,
2:12 but this database came from somewhere else and it's like this
2:15 so we're just going to roll with it.
2:17 Ok, so we've got dates notice, json doesn't support dates
2:20 nor does it support object ids, but the results here do
2:23 and so dates and object ids are sort of extensions that bson brings to json.
2:28 Alright, and then we have a list of these image url objects
2:32 which have both the size and url, and so on,
2:35 and then they also have ratings, this one has one rating, so not too many,
2:39 let's look at the next one— it has a lot of ratings, right,
2:42 so it has a user id that is foreign key constraint
2:45 a foreign key link soft not enforced by the database,
2:48 but a link over to the user table and then a value here;
2:51 so this is what this database looks like,
2:55 we have a title, we have an isbn, and these are like the flat things,
3:00 and then we have most importantly we'll go play with the ratings a little bit,
3:03 so let's start by asking this question about the books.
3:06 So the way it works is db.Book.find put some space in here
3:11 so the way MongoDB queries it doesn't have a where clause
3:15 basically what you put in here is the where clause,
3:18 and the way we do is we pass what I think of as a prototypical json object
3:22 so the json object that we're going to put here,
3:25 maybe would have something like this, let's say title, case sensitive remember,
3:34 is "From the Corner of His Eye", if I put this in, here we go,
3:39 so "From the Corner of His Eye", now this is a book
3:41 that should be in this database and we'll be able to do some queries for it
3:47 what this says to MongoDB is go to the book collection
3:49 and find every single document that has the title equal to
3:54 "From the Corner of His Eye", and I think that there's more than one, let's see—
3:57 yes, so we can come over here and we can do a .count,
4:00 there's three, alright, so this is nice,
4:03 however, what you saw come back there was even if I did a pretty,
4:07 still because we've got the ratings and the image urls
4:10 and this one has a crazy amount of ratings and so on, we might want to get less,
4:14 so with his find thing, this is like— let's put it here,
4:18 this part where is this title, that is the where clause
4:21 but in SQL, you could say like select title, id, isbn, from this table
4:28 so we can do that in MongoDB as well, we can do this like sub projection
4:31 so I can come down here and say I'm interested in title
4:34 and anything that's truthy in Javascript, so I could put high,
4:38 I could put one, I could put true, I like to put one, I don't know why
4:43 and let's say we want the isbn, this is case sensitive as well
4:47 and watch what comes back now — okay, so there's our three records
4:51 now interestingly, each one has three keys and we specified two.
4:55 So the way it works is Mongo is like
4:57 you're probably going to need that primary key
4:59 so unless you explicitly say you don't want it, you're getting it right,
5:02 so if we want to do this again, and I could come over here
5:05 and I could explicitly suppress id and put something falsy here like zero
5:08 and then I just get isbn and title, okay.
5:15 So let's go back to this. Now suppose I want to find the book with this title
5:19 and this isbn, how do I do an and here?
5:23 Well the way these queries work is everything,
5:26 basically every property of that little subdocument must be
5:29 a subset of the thing it matches for,
5:31 so when I say title is "From the Corner of His Eye",
5:33 that matches the title, but I could equally come up here
5:36 and do this again and say oh also that isbn,
5:43 actually I don't know what it's supposed to be let me run this real quick,
5:46 let's say we're looking for this one, the one that ends in 41,
5:50 so now I could come over here and say that isbn,
5:55 so json or Javascript you don't technically need to put a name there
5:58 but this is a string, so it goes like that, right
6:01 see it starts with zero, it wouldn't just be a number.
6:03 So now, if I run this, I just get the one,
6:05 so this is the and clause, select star from book where title is this and isbn is that
6:11 so you can create these documents
6:14 to basically and together all the pieces that you need.
6:18 So this is all well and good, this looks a lot like a standard database,
6:22 standard relational database type of thing
6:25 but remember when I talked about documents,
6:27 I said their superpower is they get this nested thing
6:31 so let's go over here and just throw this back,
6:34 we'll just get one of them so we can look at it again,
6:38 their super power is that they can reach, let's get the next one
6:43 so per page you would use skip and limit,
6:46 so we can reach into like say the ratings and say
6:49 I'd like to find all of the books that have a rating of let's say eight
6:54 or all the books that have been rated let's do this,
6:58 I don't know how many books that person has rated
7:00 but we can find out in a second, so I want to find all the books
7:02 that have ratings where the user id was that particular id, right there,
7:06 so how do we do that— let's come up here again, we don't need this anymore,
7:10 so in here we kind of want to say something like this
7:14 like rating, and then if this was an object we would navigate it with .syntax
7:18 but it's not going to work out so well here,
7:20 so this would be user id like this, let me just paste this in
7:26 so I can get my little object id out, when you're quering by object id
7:29 and you just say object id,
7:32 the question is is that valid Javascript, and the answer is no, it is not.
7:37 So any time you have this sort of hierarchy thing traversal
7:41 you have to put quotes right, if it's a single item is optional
7:44 if you're doing something funky like an operator or something like this
7:47 then you're going to have to do like this.
7:50 So let's just show, let's select back here
7:53 we're just going to say give me the title is one
7:55 and I don't even care about the id;
7:58 if I can write a query like this, go down into the ratings,
8:01 and show me all the ones that have this user voted,
8:04 that means even though I've kind of pre-joined and embedded this ratings concept,
8:08 I can still query it as if it was a separate table, separate collection
8:12 and that's the document databases superpower,
8:15 let's see if I can get it to work now;
8:17 apparently I did not get it to work what am I missing here?
8:21 Oh, notice I think I said rating and the actual schema is ratings plural,
8:26 I think that's good, it's representing a pluralized thing down there
8:30 so the problem was I did this, now notice MongoDB didn't crash,
8:35 it didn't go oh there's no such thing as a ratings field on this,
8:39 it just said no nothing matches that,
8:41 so it's really powerful, it means it's super easy
8:43 to sort of evolve and work with the data
8:46 and it doesn't break under the tiniest lightest of schema changes, pretty good,
8:50 but you just got to be careful, so let's try it again.
8:53 There we go, so apparently we could even ask
8:55 because that was not all of them, there's a lot of books this person has rated
8:58 so I think this data might be partly just generated
9:01 okay, so here these are the books that that person rated,
9:05 let's find another, let's try to do this again,
9:08 come down here I will get this object id,
9:14 we can say I want to find the books rated by that person
9:18 how many are there— 107.
9:21 And if I actually wanted to see what they are, there's the titles of the first set of them,
9:24 notice that's really, really fast, I think I have indexes set up right now
9:28 we'll talk about indexes when we get to the performance part of this course,
9:31 but we can do these queries down into the ratings embedded part
9:35 the embedded documents into the books
9:38 just as if they were their own table,
9:41 I told you there's about a quarter million books, there's 1.25 million ratings
9:45 so notice the response time here almost instant, in fact it's like milliseconds.
9:51 So not only can we do this query, we can do this query extraordinarily fast.
9:56 All right, so this is one of the things that makes document databases interesting
10:00 and also challenging, how do you define the documents,
10:03 should you embed them, should you not,
10:05 we'll get to that in a whole different chapter,
10:07 but for now, just know it does have this super power
10:09 to reach down in here and do these queries.