MongoDB for Developers with Python Transcripts
Chapter: MongoDB's shell and native query syntax
Lecture: Advanced queries
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
Now, let's use Robomongo, our shiny new shell
0:04
that I contend is better than just the cli one,
0:07
let's use it to explore some more advanced query filtering and sorting options.
0:12
So here's just a blank find showing me all the records,
0:16
how many are there in the database, there's 271 thousand books,
0:20
so this is the same database we've been playing with for a while now.
0:23
So let's ask some questions about the ratings.
0:31
So we're going to go into the ratings array
0:32
which contains a bunch of objects, which have values,
0:35
so I want to say how many of them have the value nine,
0:40
so what's that actually answering— what question is that answering
0:44
that is answering how many books have been rated
0:47
at some point by somebody with a nine,
0:50
how about with ten— a little bit more,
0:54
so there are some books that were really, really popular
0:56
people loved them, this is a 1 to 10 type of scale,
0:59
I think it might also include zero.
1:01
So that's great, this is our prototypical json object here.
1:04
However, what if I want to say show me all the books
1:08
that have a moderately high rating, what does that mean,
1:12
let's say it has an eight, a nine or a ten as a rating,
1:15
how do I express that as a prototype? You can't do it,
1:19
and so that's why MongoDB has something slightly more complex
1:22
and nuanced than just straight comparison, right,
1:27
so this is like an equality query, so instead of putting a value here
1:29
we can put a little sub search document here
1:33
and into this, we can say I'd like to apply an operator instead of an exact match,
1:39
so the operator might be greater than operator >,
1:42
so the way you know it's an operator is the dollar
1:46
and gte greater than or equal to is going to be the thing
1:50
and then we're going to put the value of eight,
1:52
so show me the books that have a rating of eight or above,
1:55
tell me how many there are because we're doing a count,
1:57
so let's run that, look at that 98 thousand books
2:00
have a rating of eight, a nine or a ten.
2:03
Does it mean their average rating as eight, nine or ten,
2:06
that means somebody somewhere has rated it eight, nine or ten.
2:09
So we also have things like greater than,
2:12
without the equal, just flat up greater than so that's nine or ten right there,
2:16
so we have a number of these operators,
2:19
greater than, greater than or equal to, and so on.
2:22
Another one that's really interesting is in,
2:24
this is super important for really powerful queries,
2:27
so when we have documents that contain sub arrays of other documents
2:33
you can think of those as basically being pre joined
2:36
but when you normalize those, that are not contained within each other,
2:39
then you need a way to still go back and say
2:42
basically do the join, and this in operator is the key to making that happen,
2:47
this is not really what's happening here, because this is a sub document,
2:50
but it's the operator that's involved, so what we can do is
2:53
say I would like to find me the ratings
2:55
that have let's say prime numbers as ratings,
2:58
it's kind of silly, but whatever,
3:02
here we go, so those are the prime numbers between one and ten,
3:05
and we could say I would like to find all the ratings where the value,
3:08
one of the values right, remember they have multiple ratings
3:11
but one of the values is actually in this set,
3:14
so the way this usually manifests is like go to the database
3:17
and maybe I pull back some items, and it's got like a sub array of let's say ids
3:23
and then I can go back to the database
3:26
and say give me all the items in this other collection
3:28
where the idea is in one of this like sub ids,
3:31
so an example might be in the Talk Python Training stuff
3:34
that remember the course contains all the chapter ids
3:36
and I can go back into one single query
3:39
that will give me all the chapters for a course it's this in operator, so let's try that.
3:43
So there we go, apparently 69 thousand have a prime rating at some point
3:49
not that that means anything, but it shows you how these operators work.