Aggregation Pipelines

Part of the Databases with MongoDB section of Coddy's Node.js journey. Lesson 24 of 29.

A "top rated" list needs averages, a minimum number of ratings, an order and a cut. Each of those is a stage.

$match keeps the documents that pass a filter, like find. $avg averages a field inside $group. Stages run in the order you write them, and each one sees only what the stage before it passed on. A $match before $group filters the stored documents. A $match after $group filters the groups, by fields the group made, as in { $match: { orders: { $gte: 3 } } }. One $group can make several fields at once, such as spent: { $sum: '$total' } and orders: { $sum: 1 }.

await orders.aggregate([
    { $match: { status: 'paid' } },
    { $group: { _id: '$customer', spent: { $sum: '$total' } } },
    { $sort: { spent: -1 } },
    { $limit: 5 },
]).toArray();

$sort and $limit take the same arguments as on a cursor, but a pipeline runs them in the order you write them. A $limit before $sort keeps any five and sorts only those.

Challenge

Easy

This step opens the course’s prepared bookshelf API project, rather than your project files from Grouping with $group (including server.js). Your earlier work stays in its own lesson. Read the supplied files before editing server.js. This copy contains the bookshelf routes and genre-count summary.

The shelf wants a "top rated" list: the three books with the highest average stars. A book needs at least two ratings to be listed, so one lucky 5-star rating does not put it on top.

In server.js, add a GET /stats/top route. It runs an aggregation on the ratings collection, not on books.
Group the ratings by bookId. Each group gets three fields: _id, which is the book's id; avg, the average of its stars; and count, the number of ratings it has. One $group stage can compute both avg and count.
Keep only the groups whose count is 2 or more. This rule is about a book's group, not about one rating, so this stage comes after the grouping.
Sort the groups by avg, highest first, and keep the first 3.
Respond with status 200 and the array. Each entry has exactly these three fields:
```
[
  { "_id": "6720a1f4c2b9e81d3a5f0c11", "avg": 4.5, "count": 2 },
  { "_id": "6720a1f4c2b9e81d3a5f0c12", "avg": 3, "count": 2 }
]
```
_id is the book's _id, so a client can fetch the book with GET /books/ plus that id. When fewer than three books have two ratings, the array is shorter. Try it: Every Run empties ratings, so send these requests after your last Run. Send GET /books and copy the _id of Dune, Kindred and Emma. Send PUT /books/<Dune's _id>/ratings with { "user": "ada", "stars": 5 }, then again with { "user": "bo", "stars": 4 }. Rate Kindred the same way, with 3 stars from ada and 3 from bo. Rate Emma once, with { "user": "ada", "stars": 5 }. Send GET /stats/top: you get Dune (avg 4.5, count 2), then Kindred (avg 3, count 2). Emma is missing, because she has only one rating. If Emma comes first, your count filter runs before the grouping, or not at all.

Try it yourself

import express from 'express';
import { MongoClient, ObjectId } from 'mongodb';

const client = new MongoClient('mongodb://localhost:27017');
const db = client.db('shelf');
const books = db.collection('books');
const ratings = db.collection('ratings');

await books.createIndex({ isbn: 1 }, { unique: true });
await books.insertMany([
    { title: 'Dune', author: 'Frank Herbert', year: 1965, isbn: '9780441013593', genre: 'scifi' },
    { title: 'Kindred', author: 'Octavia E. Butler', year: 1979, isbn: '9780807083697', genre: 'scifi' },
    { title: 'Emma', author: 'Jane Austen', year: 1815, isbn: '9780141439587', genre: 'classic' },
]);

const app = express();
app.use(express.json());

// Every error answers { error: message }.
function sendError(res, status, message) {
    res.status(status).json({ error: message });
}

// The :id as an ObjectId, or null after answering 400 for a bad id.
function parseId(req, res) {
    if (ObjectId.isValid(req.params.id)) {
        return new ObjectId(req.params.id);
    }
    sendError(res, 400, 'Not a valid id');
    return null;
}

app.get('/', (req, res) => {
    res.send('Bookshelf API');
});

app.get('/books', async (req, res) => {
    const filter = {};
    if (req.query.author) {
        filter.author = req.query.author;
    }
    if (req.query.from || req.query.to) {
        filter.year = {};
        if (req.query.from) {
            filter.year.$gte = Number(req.query.from);
        }
        if (req.query.to) {
            filter.year.$lte = Number(req.query.to);
        }
    }
    const list = await books.find(filter).toArray();
    res.json(list);
});

app.post('/books', async (req, res) => {
    const book = req.body;
    try {
        await books.insertOne(book);
    } catch (err) {
        if (err.code === 11000) {
            return sendError(res, 409, 'That ISBN is already on the shelf');
        }
        throw err;
    }
    res.status(201).json(book);
});

app.get('/books/newest', async (req, res) => {
    const n = Number(req.query.n) || 3;
    const list = await books
        .find({}, { projection: { title: 1, year: 1, _id: 0 } })
        .sort({ year: -1 })
        .limit(n)
        .toArray();
    res.json(list);
});

app.get('/books/:id', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    const book = await books.findOne({ _id: id });
    if (!book) {
        return sendError(res, 404, 'No such book');
    }
    res.json(book);
});

app.patch('/books/:id', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    const result = await books.updateOne({ _id: id }, { $set: req.body });
    if (result.matchedCount === 0) {
        return sendError(res, 404, 'No such book');
    }
    res.json(await books.findOne({ _id: id }));
});

app.post('/books/:id/copies', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    const result = await books.updateOne({ _id: id }, { $inc: { copies: 1 } });
    if (result.matchedCount === 0) {
        return sendError(res, 404, 'No such book');
    }
    res.json(await books.findOne({ _id: id }));
});

app.post('/books/:id/tags', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    const result = await books.updateOne({ _id: id }, { $push: { tags: req.body.tag } });
    if (result.matchedCount === 0) {
        return sendError(res, 404, 'No such book');
    }
    res.json(await books.findOne({ _id: id }));
});

app.delete('/books/:id', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    const result = await books.deleteOne({ _id: id });
    if (result.deletedCount === 0) {
        return sendError(res, 404, 'No such book');
    }
    res.status(204).end();
});

app.put('/books/:id/ratings', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    const { user, stars } = req.body;
    await ratings.updateOne({ bookId: id, user }, { $set: { stars } }, { upsert: true });
    res.json({ user, stars });
});

app.get('/books/:id/ratings', async (req, res) => {
    const id = parseId(req, res);
    if (!id) return;
    res.json(await ratings.find({ bookId: id }).toArray());
});

app.get('/stats', async (req, res) => {
    const filter = req.query.genre ? { genre: req.query.genre } : {};
    res.json({
        books: await books.countDocuments(filter),
        ratings: await ratings.countDocuments(),
    });
});

app.get('/stats/genres', async (req, res) => {
    const genres = await books
        .aggregate([{ $group: { _id: '$genre', count: { $sum: 1 } } }])
        .toArray();
    res.json(genres);
});

app.listen(3000, () => {
    console.log('Bookshelf is running on port 3000');
});

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Databases with MongoDB

Practice on your own: Express playground