MongoDB for Developers with Python Transcripts
Chapter: MongoDB's shell and native query syntax
Lecture: Advanced queries
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.