Introduction to MongoDb with .NET part 6: a return to querying with query operators

Introduction

In the previous post we looked at how to import all documents from a JSON file using the mongoimport tool. We created two collections in the process: restaurants and zipcodes. Both are readily available data sources for testing in MongoDb. They are large enough and offer varied data structures for meaningful queries.

In this post we’ll return to exploring querying techniques in MongoDb. In particular we’ll start looking at so-called query operators that help us construct more complex queries than the ones we’ve seen so far. We’ll first demonstrate the usage of the greater-than and less-than operators.

Have the MongoDb server and client ready in two different console applications if you want to try the examples yourself.

Query operators in MongoDb

Query operators are special keywords in the MongoDb query syntax. They can be recognised by a leading dollar sign, such as $gt which stands for “greater than”. E.g. if you’d like to translate the SQL WHERE clause “WHERE population > 60000” you’ll need to use the $gt operator in the query. The syntax for the inclusion of these operators is even more JSON. The operator expresses a condition which will stand as a sub-document within the query document. This probably sounds very scary but it really only means some extended JSON.

Nevertheless query syntax can become quite complex. This is especially true of data aggregation which in MongoDb turns into an orgy of JSON and query operators.

If you’d like to rush ahead and look at some query operators here are two useful links for the most frequently used:

• Logical operators
• Comparison operators

We won’t go into every single query operator in this course, that would be futile. The goal is to get acquainted with the query syntax, you can figure out the rest, it’s not exactly rocket science.

Greater than and less then

Alright, after all this tension build-up we’ll extract all the places from the zipcodes collection that have a population larger than 50000. Here’s the query:

use model
db.zipcodes.find({"pop" : {$gt : 50000}})

We provide the field name which we want to filter, i.e. “pop” in this case. This is followed by an underdocument with the $gt operator and the value for the operator. This statement is equal to “WHERE pop > 50000” in SQL. The query will respond with a number of documents where “pop” is larger than 50000. There will be quite many so you’ll probably see “Type ‘it’ for more” in the console window. You can type ‘it’ if you’d like to look at more results.

You can probably guess that $gt has a counterpart $lt. There are $gte and $lte which stand for “greater than or equal” to and “less than or equal to”. Let’s extract some smaller places with a population of at most 1000:

db.zipcodes.find({"pop" : {$lte : 1000}})

Multiple conditions

Before we look at other operators I’d like to show how we can include multiple search conditions in the query. This is analogous to the “AND” operator in SQL. There are at least two solutions for this. A comma-separated list of filters is one where we just add the operator-value pairs to the sub-document. So if we want to find the places whose population lies within 50000 and 60000 we can write the following:

db.zipcodes.find({"pop" : {$lte : 60000, $gt : 50000}})

That’s multiple conditions on a single field, i.e. “pop”. In order to filter on other fields we can extend the JSON document with other sub-documents. The following query will find the places with populations between 50k and 60k that are located in Massachusetts, i.e. where “state” is equal to MA:

db.zipcodes.find({"pop" : {$lte : 60000, $gt : 50000}, "state" : "MA"})

Another solution is by using the $and logical operator. We’ll look at $or and $and in a separate post but here comes an example anyway. The $and operator is followed by an array of conditions that are all connected with AND. The last example can be rewritten as follows:

db.zipcodes.find({$and : [{"pop": {$lte: 60000,$gt: 50000}},{"state": "MA"}]})

Count

If you don’t want to return the documents of a query but would like to see how many documents there are that match the criteria you can use the count function we saw before. Just attach it to the find function:

db.zipcodes.find({"pop" : {$lte : 60000, $gt : 50000}, "state" : "MA"}).count()

It should return 6.

These operators work for strings as well. String comparisons are based on the UTF-8 code points of the characters in the strings. Consider the following example:

db.zipcodes.find({"city" : {$gt : "B" , $lt: "C"}})

We want to extract the places whose name is greater than B and less than C. What does that mean? If there were a place called “B” then it would be excluded from the results. However, “BA” is returned in the result set simply because “BA” has a higher UTF-8 code point value than “B”. The main thing to keep in mind that currently there’s no feature corresponding to cultures and collations in MongoDb. This may change in the future though.

We’ll continue with more operators in the next post.

You can view all posts related to data storage on this blog here.