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.

Talk Python's Mastodon Michael Kennedy's Mastodon