Advanced Queries in MongoDB

Introduction

By the time you have reached this tutorial, you have probably built a few collections using the mongo shell and practiced inserting documents into a database. Now it’s time to take a deeper dive into finding the specific data you need. We will go beyond the basic find() method and learn some qeury kung fu.

Prerequisites

  • You should have MongoDB installed
  • It’s not required but it’s recommended that you have some previous shell experience with MongoDB.
  • You should have access to the MongoDB shell ( Execute mongo )

Projection

Before we get too deep into queries, it’s worth understanding what projection is and how MongoDB uses it. When performing a query, we can either return all the fields in the document or be specific about which fields we want to see in the results. Projection allows you to define what is returned. We will scratch the surface of how this works with a simple example, but realize that there are far more advanced ways to use projection, that will not covered in this tutorial.

> db.classroom.find()
{ "_id" : ObjectId("5d688cb1caacec22eec01cfe"), "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : ObjectId("5d688cb1caacec22eec01cff"), "name" : "John", "age" : 22, "hobby" : "surfing" }
{ "_id" : ObjectId("5d688cb1caacec22eec01d00"), "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : ObjectId("5d688cb1caacec22eec01d01"), "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : ObjectId("5d688cb1caacec22eec01d02"), "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : ObjectId("5d688e0ccaacec22eec01d03"), "name" : "Dave", "age" : 25, "hobby" : "painting" }


> db.classroom.find( {hobby: "painting"}, {_id: 0, name: 1} )
{ "name" : "Lisa" }
{ "name" : "Dave" }

In the example above, the basic find() function will list all the fields in each document. In the second query, we exercised projection by querying on specific criteria and defining what we wanted to see in the output. This query found all the students whose hobby was painting, {hobby: "painting"} and displayed only their names, {_id: 0, name: 1}.

  • Setting a value of 1 means we want to display the contents of the field.
  • Setting a value of 0 means we don’t want to display the contents of the field.

Note: Setting a field to 1 will automatically set other fields to 0, except the _id field. Therefore you don’t have to explicitly set all the other fields to 0.

Advanced Query Functions

Every developer should become familiar with common ways to run queries on a collection. Knowing when to append the right method to your query will help you generate the best results.

Sorting the Results of Query

Using the sort() method, you can query your results in either ascending or descending order. If you want your query results to return in ascending order, use 1. If you want your query results to return in descending order, use -1.

Let’s use the collection from our first example and display the natural sort, then the ascending sort, and finally the decscending sort:

> db.classroom.find()
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }


> db.classroom.find().sort( {age: 1} )
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }

 
> db.classroom.find().sort( {age: -1} )
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }

The sorted results are returned in order by age. We can see from the results that when two fields have the same value, there isn’t anthing that determines which should come first. However, you can sort by multiple fields. The documents will be sorted by the first field, followed by the next field.

> db.classroom.find().sort( {hobby: 1, name: 1} )
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }

In our results above, you can see that both Dave and Lisa enjoy painting. The result set is sorted alphabetically by name.

Sorting with Limits

Using the limit() method specifies the number of documents that are returned. This limted result set provides more flexibility to your query.

In the example below we will sort by name, but will also limit the results to show only 4 documents:

> db.classroom.find().limit(4).sort({ name: 1})
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }

Behind the scenes, the entire list was sorted alphabetically by name, but the results only displayed the first four documents in the sorted list.

Counting Documents

Sometimes you need statiscal information about your collection. The count() method is perfect for finding out how many documents you have in your collection. You can also use this method to count documents that match a specific query:

> db.classroom.find()
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }

> db.classroom.find().count()
  6

Query Operators

A query is just the retreival of data, but operators are used to elevate those results by comparing and evaluating the data. Here is a list of common operators, along with some examples for a few in each category.

Comparison Query Operators $ne – not equal $gt – greater than $gte – greater than or equal to $lt – less than $lte – less than or equal to $in – uses an array; returns fields matching any value specified in the array

Logical Query Operators $and – if all the criteria is satisfied it will return the document $not – returns documents that do not match the criteria $nor – returns documents that fail all the query expressions $or – if either one is true then it will return the document

Below are just a few usage examples. 1. From the Comparison Operators, we used $gt to find all documents where the age was more that 24. 2. From the Logical Operators, we used $or to find all documents where age >= 25 or hobby = painting.

> db.classroom.find()
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 2, "name" : "John", "age" : 22, "hobby" : "surfing" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }


> db.classroom.find({"age": {$gt: 24} })
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }


> db.classroom.find({
... $or: [
...   {age: {$gte: 25} },
...   {hobby: "painting"}
... ]
... })
{ "_id" : 1, "name" : "Jane", "age" : 27, "hobby" : "sewing" }
{ "_id" : 3, "name" : "Dawn", "age" : 26, "hobby" : "comics" }
{ "_id" : 4, "name" : "Sam", "age" : 25, "hobby" : "reading" }
{ "_id" : 5, "name" : "Lisa", "age" : 22, "hobby" : "painting" }
{ "_id" : 6, "name" : "Dave", "age" : 25, "hobby" : "painting" }

Conclusion

In this article, you learned more advanced techniques for finding documents that contain the data you need. This included different sorting methods, as well as some common query operators. In addition, you learned about projection and how it can help the performance of your queries. Continue to sharpend your skills by practicing the syntax and attempting other ways to query the database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.