Introduction to MongoDb with .NET part 4: querying basics with find and findOne

Introduction

In the previous post discussed some details around insertions in MongoDb. We saw that it was quite a painless and straightforward operation. We use the “db” handle, provide the name of the collection where we’d like to insert a document, call the “insert” function on it and pass in a JSON document. All MongoDb documents must have a unique and immutable ID represented by the “_id” field. By default it is of type ObjectId which comes from the BSON specification. We can also provide an ID of integer or string or some other type ourselves but then it is our responsibility that the ID is unique within the collection otherwise we’ll get an exception. Auto-incremented integer IDs are difficult to implement in MongoDb but storing GUID as a string for the ID is a viable option if you’d like to stick to a familiar ID setting strategy from relational databases.

In this post we’ll start looking into querying in MongoDb which will span multiple posts. We’ll mostly see a lot of short examples.

Querying with find

Querying means that we’d like to find some data in a collection. Sometimes we want to find all of them, or just some of them where we provide a filter. Occasionally we want to retrieve only a single document that matches a criteria. In other words we want to achieve something like…

“SELECT [column], [column] FROM [table] WHERE [condition]”

We’ve already seen an example for that in the form of the “find” function:

use loadtest
db.products.find()

If you followed along the examples in the previous post then the find() function without any filter will yield something like this:

{ "_id" : 1, "name" : "radio", "stock_level" : 100 }
{ "_id" : "434b1534-fc4d-47aa-9406-6d48cae713d6", "name" : "tv", "stock_level" : 50 }
{ "_id" : "f96589ff-cac7-4fe0-a486-12c7697de069", "name" : "computer", "stock_level" : 80 }

So that corresponds to a “SELECT * FROM [table]” query with no WHERE clause.

Filtering is provided by way of another JSON document provided to the find function. E.g. if we want to retrieve all products where name is “tv” then we can write the following:

db.products.find({"name" : "tv"})

The above query will find one record, but if there were multiple documents with a field “name” and value “tv” we’d see them here. Soon we’ll import a larger data set into our database so that we can run more interesting queries. However, it’s important to start small where it’s easier to demonstrate the basics.

So that was a single WHERE clause. How do we enter multiple filters, like WHERE name = ‘tv’ AND stock_level = 50? We just need to extend the JSON query document:

db.products.find({"name" : "tv", "stock_level" : 50})

Which returns the same result as above.

What if we want to limit the number of fields returned? I.e. how do we perform a “SELECT name, stock_level FROM …” type of query in MongoDb? With another JSON document of course! We can supply a second JSON document after the JSON filter where we can specify which fields to show and suppress using “true” and “false” boolean values. The _id field is always shown by default so if we want to suppress it then we must specify that as follows:

db.products.find({"name" : "tv"}, {"_id" : false})

This returns…

{ "name" : "tv", "stock_level" : 50 }

…i.e. all fields except for the _id. If we only want to see the stock level and not anything else then we can write the following query:

db.products.find({"name" : "tv"}, {"_id" : false, "stock_level" : true})

…which produces the following:

{ "stock_level" : 50 }

This filter suppresses the “name” and any other fields by default, we don’t need to specify like “name” : false, “some_other_prop” : false in the field selector.

Querying with findOne

The function findOne is similar to find but it only returns a single document even if the filter matches multiple documents. A single random match will be returned by the function. findOne with no parameters will return a single document at random, we cannot tell for sure which, but the very first document is a safe bet. E.g. the query…

db.products.findOne()

…returns

{ "_id" : 1, "name" : "radio", "stock_level" : 100 }

We can provide the exact same parameters to this function as to the find function. Here comes an example:

db.products.findOne({"name" : "tv"}, {"_id" : false, "stock_level" : true})

The only difference is that findOne returns exactly one matching document even if there are many.

findOne() can help you get acquainted with the data set. We mentioned before that there’s no strict schema in MongoDb collections but in practice documents within a collection will follow some well defined structure. After all just because you can mix customers and products in a collection called “things” it doesn’t mean that you’ll ever do so in a real-life project. Wwe prefer well structured data records in our databases, right?

So if you’d like to view the schema of a collection, call findOne on it and you’ll be able to view its general structure, its properties, its types and its embedded documents.

Pretty JSON

I just wanted to mention that we can tell the Mongo client to present the JSON documents in a better format. We can attach the “pretty” function after find or findOne as follows:

db.products.find().pretty()

The result is a more readable JSON:

{ "_id" : 1, "name" : "radio", "stock_level" : 100 }
{
        "_id" : "434b1534-fc4d-47aa-9406-6d48cae713d6",
        "name" : "tv",
        "stock_level" : 50
}
{
        "_id" : "f96589ff-cac7-4fe0-a486-12c7697de069",
        "name" : "computer",
        "stock_level" : 80
}

The benefits are more visible with more complex documents.

The count function

Before we finish this post I want to mention a quick way to find the number of documents in a collection. We can find that number by the count() function as follows:

db.products.count()

…which returns 3 obviously. So in case you’re presented with a new and unknown collection you can use findOne and count to quickly familiarise yourself with its structure and size.

There are far more querying techniques available in MongoDb of course and we’ll keep exploring these features in the next post.

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