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