MongoDB Pagination

How to paginate MongoDB queries#

Curious how pagination might be implemented in MongoDB? There are at aleast three ways to implement pagination in MongoDB, let's explore them.

Pagination using skip()#

Let's have a data set to play around with so that we can learn by examples. Create a collection named companies and add some documents on it.

db.companies.insert({name:'Google'})
db.companies.insert({name:'Facebook'})
db.companies.insert({name:'Apple'})
db.companies.insert({name:'Microsoft'})
db.companies.insert({name:'Oracle'})
db.companies.insert({name:'IBM'})
db.companies.insert({name:'Yahoo'})
db.companies.insert({name:'HP'})

The conventional approach to pagination requires the use of skip() and limit() MongoDB cursor functions.

Familiarize yourself with skip() using these example commands:

db.companies.find().skip(0)
db.companies.find().skip(1)
db.companies.find().skip(5)

Understand how limit() works using these commands:

db.companies.find().limit(1)
db.companies.find().limit(3)

See how skip() and limit() can be coupled to get interesting results. Especially note the last two commands.

db.companies.find().skip(0).limit(1)
db.companies.find().skip(1).limit(1)
db.companies.find().skip(3).limit(1)
db.companies.find().skip(0).limit(3)
db.companies.find().skip(3).limit(3)

By now you probably realize how we might implement pagination in MongoDB. Here is an example code:

db.companies.find().skip(NUMBER_OF_ITEMS * (PAGE_NUMBER - 1)).limit(NUMBER_OF_ITEMS )

• NUMBER_OF_ITEMS is the number of items to be shown on a page
• PAGE_NUMBER is the current page number

Use db.companies.count() to get the number of documents in the collection and implement the pagination navigation links.

This pagination technique works as you expect and is excellent in every way - as long as you have a small set of data. If you have a collection with hundreds of thousands of documents, this method of pagination will fail - it will become slower with every increasing page. That's because the cursor has to start from the beginning of the collection to the skip position for every request, and with increasing page number the skip position becomes farther and farther.

Notes

• Very straightforward and logical
• Will not scale
• Best used for small sized collections

Pagination using $slice#

The next technique of implementing pagination in MongoDB involves the use of $push and $slice. In this method, we store the documents in an array and use the $slice method to accomplish what skip()-and-limit() does, but without the overhead associated with the skip() method.

We will be using a single root document in a collection with two fields:

1. an array to store the sub-documents
2. a numerical key to store the size of the array

// Clear the collection of any previous data and create the root document
db.companies.drop()
db.companies.insert({items:[], count:0})

// Add the sub-documents to the root document
db.companies.update({}, {$push:{items:'Google'}})
db.companies.update({}, {$set:{count:1}})
db.companies.update({}, {$push:{items:'Facebook'}})
db.companies.update({}, {$set:{count:2}})
db.companies.update({}, {$push:{items:'Apple'}})
db.companies.update({}, {$set:{count:3}})
db.companies.update({}, {$push:{items:'Microsoft'}})
db.companies.update({}, {$set:{count:4}})
db.companies.update({}, {$push:{items:'Oracle'}})
db.companies.update({}, {$set:{count:5}})
db.companies.update({}, {$push:{items:'IBM'}})
db.companies.update({}, {$set:{count:6}})
db.companies.update({}, {$push:{items:'Yahoo'}})
db.companies.update({}, {$set:{count:7}})
db.companies.update({}, {$push:{items:'HP'}})
db.companies.update({}, {$set:{count:8}})

Now observe how the $slice operator works.

db.companies.find({}, {items:{$slice:[0, 3]}})
db.companies.find({}, {items:{$slice:[3, 3]}})

From the above commands you can see, you already have pagination in place. It just needs to be made dynamic, which is accomplished thus:

var skip = NUMBER_OF_ITEMS * (PAGE_NUMBER - 1)
db.companies.find({}, {$slice:[skip, NUMBER_OF_ITEMS]})

• NUMBER_OF_ITEMS is the number of items to be shown on a page
• PAGE_NUMBER is the current page number

For creating the pagination navigation links, use the count field to get the number of items and the number of pages.

Notes

• Items are no longer root documents
• Need to maintain a count key
• Data structure clarity and logic is somewhat lost

Pagination using range queries#

This method of MongoDB pagination requires manually setting the _id field in your documents. It uses the min() and max() MongoDB cursor functions to implement the pagination.

We'll be learning by examples on a collection named companies. Make sure there are no documents on it.

db.companies.remove()

Make sure the _id value starts from 0 and increment it with each new document inserted.

db.companies.insert({_id:0, name:'Google'})
db.companies.insert({_id:1, name:'Facebook'})
db.companies.insert({_id:2, name:'Apple'})
db.companies.insert({_id:3, name:'Microsoft'})
db.companies.insert({_id:4, name:'Oracle'})
db.companies.insert({_id:5, name:'IBM'})
db.companies.insert({_id:6, name:'Yahoo'})
db.companies.insert({_id:7, name:'HP'})

In your web app, you will need to set up a system to take care of incrementing the _id value.

min() and max() can be called only on indexed key, since _id is indexed by default, we are good to go.

Observe the results of these range queries.

db.companies.find().min({_id:0}).max({_id:3})
db.companies.find().min({_id:3}).max({_id:6})

Actually run them on a mongo shell and see the results. From those two commands you probably realized we have a working pagination technique in place, it just needs to done programatically now.

The code below is an example of pagination implemented using range query with min() and max().

var min_page = NUMBER_OF_ITEMS * (PAGE_NUMBER - 1)
var max_page = min_page + NUMBER_OF_ITEMS
db.companies.find().min({_id:min_page}).max({_id:max_page})

• NUMBER_OF_ITEMS is the number of items to be shown on a page
• PAGE_NUMBER is the current page number

Use db.companies.count() to get the number of documents in the collection and implement the pagination navigation links.

Notes

• Needs a system to increment _id
• Data structure logic is mostly maintained
• Will fail if any of the root documents are deleted

That's how pagination is implemented in MongoDB using a range query.

References#

1. MongoDB - $skip()
2. MongoDB - $slice()