MongoDB for Developers with Python Transcripts
Chapter: MongoDB's shell and native query syntax
Lecture: Basic querying
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 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:51
but down at the Javascript and the MongoDB level, this is always the name of the primary key,
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:35
and anything that's truthy in Javascript, so I could put high, I could put one, I could put true, I like to put one, I don't know why
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,
5:56
so json or Javascript you don't technically need to put a name there but this is a string, so it goes like that, right
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:30
and you just say object id, the question is is that valid Javascript, and the answer is no, it is not.
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.