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