Following with the spirit of the last posts on how to query MongoDB from the perpective of an SQL developer, in this post will see how the concepts of select, top and order by translate into the MongoDB universe.

We will be using the same movies collection we used on the post about how to filter string fields using LIKE style queries.

Using MongoDB collections to select fields to display

Doing a find on a collection does return all the fields of the item, so an SQL query that does a SELECT * translates into mongo as a plain find function, no need to specify anything else.

SQL query:

SELECT * FROM movies

MongoDb query:

db.getCollection('movies').find({})

Now, imagine I want only to retrieve the fields title, and genre.

SQL query:

SELECT title,genre FROM movies

MongoDb query:

db.getCollection('movies').find({},{title:1,genre:1,_id:0})

To project specific fields we add a second parameter to the find function: a coma separated list of the fields marked as 0 for those we want to hide, and those we want to display.

I could say show me only the fields title,and genre:

db.getCollection('movies').find({},{title:1,genre:1})

Or I could say show me all fields except title and genre:

db.getCollection('movies').find({},{title:0,genre:0})

Mixing up exclusion and inclusion operator is not allowed, therefore the following query would result in an error, since mongo wouldn´t know what to do with the rest of non specified fields.

db.getCollection('movies').find({},{title:1,genre:0})

The exception to that rule is the _id field. Unless explicitly specified the _id field always will be displayed. So if – as in the first example – we want to hide it we need to add an ,_id:0 to the list of fields to display.

Order by in MongoDB

Sorting in MOngoDB is quite easy, we only need to use the sort function passing a list of field we want to order by: 1 means order asc, and -1 order desc.

Let´s see some examples; if I want to display a list of movies ordered asc by year, and title I would use this SQL query:

SELECT * FROM movies ORDER BY year, title ASC

In mongoDB we would query the DB like this:

db.getCollection('movies').find({}).sort({year:1,title:1})

To list the the movies descending for year (newest movies at the top), and ascending for title we would do this:

db.getCollection('movies').find({}).sort({year:-1,title:1})

TOP and paging

To limit the number of items the query has returned, and to return only the first N elements in SQL we would do this:

SELECT TOP 10 FROM movies

The MongoDb way is the following:

db.getCollection('movies').find({}).limit(10);

As we saw in this post about exporting a MongoDb collection to multiple files, we have a skip function to skip N documents in a collection. Combining skip and limit we can implement paginated results.

db.getCollection('movies').find({}).skip(10).limit(10);

That´s all for today, thanks for reading!