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