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
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.