MongoDB’s pipeline aggregation is – like most things in application development these days – confusing as hell (let’s be honest). Writing database queries using JavaScript’s object literal syntax is frightening, and quite frankly ridiculous. But here we are using it anyway, so we might as well know how to do one of the most useful database operations out there: a query across a collection that returns us a COUNT of the total results and a PORTION of the total results. Discovering how to do this in Mongo was crucial for us so that we could get ag-grid‘s virtual pagination to play nice! Now we can share the wisdom to the world in this blog post so that 1) others don’t pull their hair out figuring this out and 2) we don’t forget it.
So here’s a quick run down of how you can lookup (populate in mongoose vernacular), match (aka filter), sort, count, and limit using Mongo’s aggregation pipeline. And…. breathe.
Heads up: function calls in this example are to mongoose.
/**
* Query blog posts by user -> paginated results and a total count.
* @param userId {ObjectId} ID of user to retrieve blog posts for
* @param startRow {Number} First row to return in results
* @param endRow {Number} Last row to return in results
* @param [filter] {Object} Optional extra matching query object
* @param [sort] {Object} Optional sort query object
* @returns {Object} Object -> `{ rows, count }`
*/
function queryBlogPostsByUser (userId, startRow, endRow, filter = {}, sort = false) {
if (!(user instanceof mongoose.Types.ObjectId)) {
throw new Error('userId must be ObjectId')
} else if (typeof startRow !== 'number') {
throw new Error('startRow must be number')
} else if (typeof endRow !== 'number') {
throw new Error('endRow must be number')
}
const query = [
// more lookups go here if you need them
// we have a many-to-one from blogPost -> user
{ $lookup: {
from: 'users',
localField: 'user',
foreignField: '_id',
as: 'user'
} },
// each blog has a single user (author) so flatten it using $unwind
{ $unwind: '$user' },
// filter the results by our userId
{ $match: Object.assign({ 'user._id': userId }, filter) }
]
if (sort) {
// maybe we want to sort by blog title or something
query.push({ $sort: sort })
}
query.push(
{ $group: {
_id: null,
// get a count of every result that matches until now
count: { $sum: 1 },
// keep our results for the next operation
results: { $push: '$$ROOT' }
} },
// and finally trim the results to within the range given by start/endRow
{ $project: {
count: 1,
rows: { $slice: ['$results', startRow, endRow] }
} }
)
return BlogPost
.aggregate(query)
.then(([{ count, rows }]) => ({ count, rows }))
}