MongoDB Sort, Limit and Indexes
After you find documents, you can sort them, take only some of them, and count them. Indexes make all of this fast.
The examples use the original six students from the insert lesson.
sort()
sort() takes a document of fields: 1 for ascending (A to Z, small to large) and -1 for descending.
Example
db.students.find({}, { _id: 0, name: 1, marks: 1 }).sort({ marks: -1 })[
{ name: 'Fatima Noor', marks: 92 },
{ name: 'Sana Iqbal', marks: 88 },
{ name: 'Ayesha Siddiqui', marks: 85 },
{ name: 'Ali Khan', marks: 78 },
{ name: 'Hamza Raza', marks: 70 },
{ name: 'Bilal Ahmed', marks: 64 }
]Sort by more than one field. Here cities go A to Z, and inside each city the highest marks come first:
Example
db.students.find({}, { _id: 0, name: 1, city: 1, marks: 1 })
.sort({ city: 1, marks: -1 })[
{ name: 'Fatima Noor', city: 'Islamabad', marks: 92 },
{ name: 'Ali Khan', city: 'Karachi', marks: 78 },
{ name: 'Hamza Raza', city: 'Karachi', marks: 70 },
{ name: 'Bilal Ahmed', city: 'Karachi', marks: 64 },
{ name: 'Sana Iqbal', city: 'Lahore', marks: 88 },
{ name: 'Ayesha Siddiqui', city: 'Lahore', marks: 85 }
]limit() and skip()
limit(n) returns at most n documents. The top 3 students:
Example
db.students.find({}, { _id: 0, name: 1, marks: 1 }).sort({ marks: -1 }).limit(3)[
{ name: 'Fatima Noor', marks: 92 },
{ name: 'Sana Iqbal', marks: 88 },
{ name: 'Ayesha Siddiqui', marks: 85 }
]skip(n) jumps over the first n documents. Together they make pages. Page 2, with 3 students per page:
Example
db.students.find({}, { _id: 0, name: 1 }).sort({ marks: -1 }).skip(3).limit(3)[ { name: 'Ali Khan' }, { name: 'Hamza Raza' }, { name: 'Bilal Ahmed' } ]The order you chain them in does not matter: MongoDB always sorts first, then skips, then limits. But always sort when you page through results, or the pages may overlap.
Counting Documents
Example
db.students.countDocuments({ city: "Karachi" })3
countDocuments({}) counts the whole collection. For a quick, approximate total of a very large collection, use estimatedDocumentCount().
distinct()
distinct() returns each different value of a field once:
Example
db.students.distinct("city")[ 'Islamabad', 'Karachi', 'Lahore' ]
It also looks inside arrays:
Example
db.students.distinct("skills")[ 'CSS', 'HTML', 'Illustrator', 'JavaScript', 'Photoshop', 'Python', 'SQL' ]
Indexes
Without an index, MongoDB must check every document in the collection, which is called a collection scan. On a collection with a million students, that is slow. An index keeps a sorted list of one field's values, so MongoDB can jump to the matches.
Every collection already has an index on _id. Create one on city:
Example
db.students.createIndex({ city: 1 })city_1
The result is the index name. An index on several fields is called a compound index:
Example
db.students.createIndex({ city: 1, marks: -1 })This one helps queries that filter by city and sort by marks, like the sort example above.
Unique Indexes
A unique index stops duplicate values, for example two students with the same email:
Example
db.students.createIndex({ email: 1 }, { unique: true })Managing Indexes
| Command | What it does |
|---|---|
db.students.getIndexes() | Lists the indexes |
db.students.dropIndex("city_1") | Removes an index |
db.students.find({ city: "Lahore" }).explain("executionStats") | Shows how the query ran |
In the explain result, IXSCAN means an index was used, and COLLSCAN means every document was read.
Create indexes for the fields your app filters and sorts on most. Each index takes space and makes inserts slightly slower, so do not index everything.
Exercise
Exercise
Which query returns the 5 youngest students?