MongoDB Aggregation: Count Jobs per Status for a Statistics Endpoint

The dashboard will show numbers: how many jobs in total, how many per status, and what share of applications reached an interview. You could fetch every job and count in JavaScript, but that moves all the data across the network to count it. A database is built to count quickly where the data lives. MongoDB's tool for that is the aggregation pipeline.

What a pipeline is

A pipeline is a list of stages. Documents flow through the stages one after another, and each stage changes them: $match keeps some, $group combines them, $sort orders them, $project reshapes them, $limit cuts them. It is like a conveyor belt with a worker at each station, and it is the same idea as chaining filter, map and reduce on an array, but run inside the database.

// Lesson 19: an aggregation pipeline groups documents and counts them inside the database.
import Job, { STATUSES } from '../models/Job.js';

export const jobStats = async (req, res) => {
  const rows = await Job.aggregate([
    { $group: { _id: '$status', count: { $sum: 1 } } },
  ]);

  // Make sure every status appears, even with zero jobs, so the screen never has holes.
  const byStatus = Object.fromEntries(STATUSES.map((s) => [s, 0]));
  for (const r of rows) byStatus[r._id] = r.count;

  const total = Object.values(byStatus).reduce((a, b) => a + b, 0);
  const applied = total - byStatus.saved;
  // Share of applications that reached an interview or better. Only meaningful once there are applications.
  const interviewRate = applied ? Math.round(((byStatus.interview + byStatus.offer) / applied) * 100) : null;

  res.json({ total, byStatus, interviewRate });
};

Reading the controller

• { $group: { _id: '$status', count: { $sum: 1 } } } creates one output document per distinct status and adds 1 for every job that has it. The text '$status' (with a dollar sign) means *the value of the field status*.
• The result only has statuses that exist. If nobody has an offer yet, there is no offer row, and a screen that expects it would show a hole. So the code starts from an object with every status set to 0 and then fills in the counts. Designing the answer so the client never has to guess is good API manners.
• interviewRate is the share of *applications* that reached an interview or an offer. The word is chosen carefully: the denominator excludes jobs that are only saved, because you have not applied to those. And when there are no applications, the rate is null rather than 0%, because zero would claim something false. Showing “–” on screen is more honest than showing a number that means nothing yet.

The route-order bug

import { Router } from 'express';
import { listJobs, getJob, createJob, updateJob, deleteJob } from '../controllers/jobs.js';
import { jobStats } from '../controllers/stats.js';
import { checkId } from '../middleware/validate.js';

const router = Router();
router.param('id', checkId);

router.get('/', listJobs);
router.get('/stats', jobStats); // must come before '/:id', or "stats" would be read as an id
router.post('/', createJob);
router.get('/:id', getJob);
router.put('/:id', updateJob);
router.delete('/:id', deleteJob);

export default router;

Look at the comment next to /stats. Express checks routes in order, and /:id matches any one-segment path. If /stats came after /:id, a request for /api/jobs/stats would be treated as a job whose id is stats, and checkId would answer 404. This bug is easy to create and confusing to see, so remember the rule from lesson 9: put specific routes before general ones.

Other pipeline stages worth knowing

• $match first, as early as possible, so later stages see fewer documents (and can use indexes). In lesson 22 the first stage will be $match: { user: ... }.
• $sort and $limit for “top 5” questions.
• $lookup to join another collection, $unwind to turn an array into one document per item, and $addFields to compute new fields.
• $dateToString and $group together give counts per month: a nice next feature.

Your task

1. Run npm run seed if you have not, then call GET /api/jobs/stats.
2. Move the /stats route below /:id and call it again. Read the answer, then put it back.
3. Add the average number of jobs per week. Hint: use $group on $dateToString or on the week of createdAt.
4. Add byLocation: the three places with the most jobs, using $group, $sort and $limit.

How to check it worked

• byStatus has all five statuses, with zeros where there are none.
• interviewRate is null for an empty tracker and a whole number otherwise.

Summary

• An aggregation pipeline passes documents through stages such as $match, $group and $sort inside the database.
• Fill in missing groups so the client never has holes; use null for undefined ratios.
• Register specific routes (/stats) before /:id.