So in the last lecture, we had a look at or, let's now have a look at and.
For that again, let's add a find query here and let's pass a document where we describe our filter
and now let's use $and,
just as or, this takes an array of documents.
And now let's say we want to find all documents where we have a rating
and we found that on rating.average remember, a rating that is greater than 9, so $gt
9, so high quality item, high quality series or movie
and and that's the second element
in this array
and just as with or, you can have as many as you want,
you're not limited to two and let's say we're looking for documents that have a genre of drama.
Again I'm querying that index with this syntax to find all items that must have drama but may have additional
genres, so we find high quality dramas here with and. If I hit enter, we got some results and we see the rating
is quite good here and we get drama in the genre and we got another result with a very high rating
and drama in the genre,
so this is working. Now
actually, there is a shorter way of writing this.
We can totally get rid of this,
so this is the old syntax and I'll just count that, we find three results to leave it here on the page
whilst I write the alternative.
The alternative is that we don't use and but we just pass a document, one document here and in there, we
just start writing our conditions,
so rating.average should be and then that's exactly the same condition I have above,
so greater than nine and then just a comma inside of our filter document to add another field to that
filter document, genres drama, if I count that, you'll see I get back three items as well.
So these two ways of getting the data are exactly equal because by default, mongodb ends all key
fields
you add to that filter document and is the default concatenation mechanism of mongodb. That of course
leaves one question, why is there a $and operator if this does exactly the same?
There is a $and operator because you might have multiple conditions on the same field,
let's say on genres, if I want to find the count of items and I'm just using count to not put all the
data on the screen immediately, on genres,
where the genre is drama but where the genre also is horror,
this does work here but this will actually not work in all drivers because in javascript, having repeat
names in the same object is not permitted.
It is for cases like this that you then can use and, so even though it works here, does not have to work
in all drivers,
so then you can simply wrap these queries into separate documents which you passed to that and array
and now you will have an approach that will work fine.
Now you will also notice that the number of results returned is different
and the reason for that is simple,
let me repeat that approach where I mentioned it will not work everywhere and let's look into that data.
Now
in these 23 results, we got drama and horror, drama, crime and horror, drama, horror
again, drama, fantasy and horror
but then if you scroll up,
also an entry with just horror,
now why is that? Because and that is what I meant, even though we don't get an error here,
it essentially, the second genres of horror will essentially just replace that key because in json
documents, you can't have the same key more than once
and if you do specify it more than once, the second one will just override the first one.
So actually the query like this which gives us back 23 results is equivalent to this query where
we get back 23 results,
so if we want to look for both genres, we have to use the and operator and this is why there is an and
operator even though queries are
anded by default. If you look for values on the same field, you need $and.