Querying MongoDB

Reading a Schema explains the shape of a query. This page is the syntax, worked all the way through on one real question, showing the actual documents at every stage so you can see what each line does to your data.

Read it with Metabase open and run each snippet as you go. Reading queries teaches you very little; running them and being surprised teaches you fast.

Two tools, and when to use which#

Reach for find while you are still learning a collection, and aggregate once you know what you are counting.

find: a filter is just a document#

A Mongo filter is written as a document describing what you want to match. The simplest one is a field and a value.

// every completed consultation
db.consultations.find({ status: "completed" });

Two rules cover most of what you will write:

• A field and a value means equality. No = sign anywhere.
• Several fields together mean AND. They all have to match.

// completed AND paid: no operator needed for "and"
db.consultations.find({ status: "completed", paid: true });

For anything other than equality, the value becomes a document with an operator inside it. This is the shape that trips people up, so look at it closely:

// fee greater than or equal to 100000
db.consultations.find({ fee: { $gte: 100000 } });

The operators you will use constantly:

$exists is the one that matters most for real analysis, because it is how you find out whether a field was being written at all in a given era. If a field was added last year, { thatField: { $exists: true } } quietly shows you only the newer records, which is exactly the trap in Database Design Principles.

Reaching into nested fields#

Documents nest, so you need a way to point at something inside. Use a dotted path in quotes:

// the patient's city, one level down
db.consultations.find({ "patient.address.city": "Jakarta" });

If the nested thing is an array of subdocuments, the same dotted path matches when any element matches:

// any order containing at least one cancelled item
db.orders.find({ "items.status": "cancelled" });

That “any element” behaviour is useful and occasionally wrong: it does not mean every item was cancelled. Knowing which you meant is on you.

aggregate: a pipeline is an array of stages#

An aggregation is a list, in run order, one stage per element. That is the whole syntax:

[
  { $match: { ... } },   // station 1
  { $group: { ... } },   // station 2
  { $sort:  { ... } }    // station 3
]

Before the worked example, one rule that causes more confusion than anything else in MongoDB:

A $ in front of a field name means “the value of that field”. Without it, you have written a literal string.

{ $sum: "$fee" }   // add up the fee of each document
{ $sum: "fee" }    // nonsense: tries to add up the text "fee"
{ $sum: 1 }        // add 1 per document: this is how you count

Getting this wrong produces zeroes and nulls rather than an error. If a total comes back as 0 or null, a missing $ is the first thing to check.

The worked example#

The question: which doctors completed the most consultations in July 2026, and what did they bill?

Here are five documents in a consultations collection. Two of them should not survive: one is cancelled, one is from June.

{ _id: 1, doctorId: "d1", status: "completed", createdAt: ISODate("2026-07-03"), fee: 150000 }
{ _id: 2, doctorId: "d1", status: "completed", createdAt: ISODate("2026-07-18"), fee: 150000 }
{ _id: 3, doctorId: "d2", status: "completed", createdAt: ISODate("2026-07-22"), fee: 200000 }
{ _id: 4, doctorId: "d1", status: "cancelled", createdAt: ISODate("2026-07-05"), fee: 0 }
{ _id: 5, doctorId: "d2", status: "completed", createdAt: ISODate("2026-06-28"), fee: 200000 }

Stage 1, $match: cut to the rows that count#

{ $match: {
    status: "completed",
    createdAt: { $gte: ISODate("2026-07-01"), $lt: ISODate("2026-08-01") }
} }

5 documents in, 3 out. Documents 4 (cancelled) and 5 (June) are gone. Everything downstream now handles 3 instead of 5.

{ _id: 1, doctorId: "d1", status: "completed", createdAt: ISODate("2026-07-03"), fee: 150000 }
{ _id: 2, doctorId: "d1", status: "completed", createdAt: ISODate("2026-07-18"), fee: 150000 }
{ _id: 3, doctorId: "d2", status: "completed", createdAt: ISODate("2026-07-22"), fee: 200000 }

Note the date boundary: $gte the 1st of July and $lt the 1st of August. Do not write $lte the 31st of July, because a timestamp is a moment, not a day, and $lte ISODate("2026-07-31") means “up to midnight at the start of the 31st”. That silently drops a whole day. This is one of the most common real bugs in date filtering.

Stage 2, $group: collapse into buckets#

This is the stage where the shape of your data changes, and where most people lose the thread.

{ $group: {
    _id: "$doctorId",                  // the grouping key
    consultations: { $sum: 1 },        // count: add 1 per document
    revenue: { $sum: "$fee" }          // total: add up each fee
} }

3 documents in, 2 out. One document per distinct doctor.

{ _id: "d1", consultations: 2, revenue: 300000 }
{ _id: "d2", consultations: 1, revenue: 200000 }

Two things to internalise here:

• _id in $group is not an id. It is “the thing I am grouping by”. Naming it _id is a historical wart and it confuses everyone once. Group by month and _id is a month.
• Only what you build survives. status, createdAt and fee are gone, because you did not ask for them. A field you did not name in $group does not exist downstream.

Stage 3 and 4, $sort and $limit: order and cut#

{ $sort: { consultations: -1 } },   // -1 is descending, 1 is ascending
{ $limit: 5 }

2 in, 2 out here, since we only have two doctors. d1 is already first.

{ _id: "d1", consultations: 2, revenue: 300000 }
{ _id: "d2", consultations: 1, revenue: 200000 }

$sort before $limit, always. Reversed, you take an arbitrary five and then sort them, which looks plausible and is wrong.

Stage 5, $lookup: fetch the doctor’s name#

Right now the answer says d1, which no human can read. $lookup is the join.

{ $lookup: {
    from: "doctors",          // the other collection
    localField: "_id",        // the value I have (the grouping key)
    foreignField: "_id",      // the field to match in doctors
    as: "doctor"              // where to put what it finds
} }

2 in, 2 out, each now carrying a doctor field:

{ _id: "d1", consultations: 2, revenue: 300000, doctor: [ { _id: "d1", name: "Dr Sari" } ] }
{ _id: "d2", consultations: 1, revenue: 200000, doctor: [ { _id: "d2", name: "Dr Adi" } ] }

$lookup always returns an array, even when it finds exactly one match. That is why the next stage exists.

Stage 6, $unwind: unpack the array#

{ $unwind: "$doctor" }

{ _id: "d1", consultations: 2, revenue: 300000, doctor: { _id: "d1", name: "Dr Sari" } }
{ _id: "d2", consultations: 1, revenue: 200000, doctor: { _id: "d2", name: "Dr Adi" } }

Here $unwind is harmless because each array holds one element. When an array holds many, $unwind multiplies your documents, one per element, which is the fan-out trap from Reading a Schema. Unwinding an order’s five items gives five documents, and any count afterwards counts items, not orders.

There is also a sharp edge: by default $unwind drops documents whose array is empty. If a doctor record was deleted, that row silently vanishes from your report. To keep it, use { $unwind: { path: "$doctor", preserveNullAndEmptyArrays: true } }.

Stage 7, $project: keep what you want to show#

{ $project: {
    _id: 0,                      // drop it
    doctor: "$doctor.name",      // pull the name up
    consultations: 1,            // 1 means "keep this"
    revenue: 1
} }

{ doctor: "Dr Sari", consultations: 2, revenue: 300000 }
{ doctor: "Dr Adi",  consultations: 1, revenue: 200000 }

That is the answer, and it is now readable by someone who has never seen the database.

The whole thing#

[
  { $match: {
      status: "completed",
      createdAt: { $gte: ISODate("2026-07-01"), $lt: ISODate("2026-08-01") }
  } },
  { $group: {
      _id: "$doctorId",
      consultations: { $sum: 1 },
      revenue: { $sum: "$fee" }
  } },
  { $sort: { consultations: -1 } },
  { $limit: 5 },
  { $lookup: { from: "doctors", localField: "_id", foreignField: "_id", as: "doctor" } },
  { $unwind: "$doctor" },
  { $project: { _id: 0, doctor: "$doctor.name", consultations: 1, revenue: 1 } }
]

Grouping by time#

Most product questions are trends, which means grouping by day, week or month. The readable way is $dateTrunc:

{ $group: {
    _id: { $dateTrunc: { date: "$createdAt", unit: "month" } },
    consultations: { $sum: 1 }
} },
{ $sort: { _id: 1 } }     // oldest first, so the trend reads left to right

unit takes "day", "week", "month", "quarter", "year". Sort ascending on _id afterwards or your chart will be in a random order.

Timezones will bite you here. Timestamps are usually stored in UTC, so “day” means a UTC day, and for Jakarta that boundary sits at 7am local. Daily counts land in the wrong bucket for anything happening late at night. $dateTrunc accepts a timezone option, and for daily numbers that anyone will act on, set it.

The accumulators worth knowing#

Inside $group, these build your output fields:

$addToSet is how you count distinct things: collect the set, then measure it with { $size: ... }. That is the Mongo equivalent of COUNT(DISTINCT ...), and it is how you avoid counting appointments when you meant patients.

Running it in Metabase#

One rule before you start: never write $out or $merge. Every stage on this page only reads. Those two write, and $out replaces the entire target collection. There is no undo and no confirmation prompt.

In Metabase, pick the MongoDB database, start a native query, and you write the pipeline as the array of stages. You choose the collection separately from the pipeline itself; the editor is expecting the stage list, not a full db.collection.aggregate(...) call.

Exact labels shift between Metabase versions, so go by shape: a collection to run against, and an array of stages.

Two habits that make this much less painful:

• Build in the query builder first, then read what it generated. Filter and summarize in the UI, then switch to view the native query. You get correct syntax for a result you already understand, which is the fastest way to learn the language.
• Truncate the pipeline to see inside it. This is how you debug and how you see what a stage does: keep only stage one and look at the output. Add stage two, look again. When a number goes wrong, the stage you just added is the culprit. Working this way turns an invisible transformation into something you can watch.

Errors and what they actually mean#

Notice that only two of these are actual errors. The rest return a confident, plausible, wrong answer, which is why Reading a Schema ends on verifying rather than on syntax.

Practice#

Work these in order against a real collection, in your Personal Collection. Each one adds exactly one idea.

1. find every record with one status value. Read three of them in full.
2. Add a date range with $gte and $lt. Check the count dropped by roughly what you expected.
3. Use $in to accept two statuses at once.
4. Use $exists on a field you suspect is recent, and see how far back it goes.
5. $group with { $sum: 1 } to count per category. Confirm the parts add up to your stage-2 total.
6. Add $sort and $limit to get a top five.
7. Group by month with $dateTrunc and look at the trend as a table, then as a line.
8. $lookup a name onto your ids, then $unwind and $project it into something readable.
9. Break it on purpose: drop a $, then read the error. Put it back.

Step 5 is the important one. If your grouped numbers do not add up to the ungrouped total, you have found a real bug, and finding it that way is the whole skill.

Related: Reading a Schema, Database Design Principles, Using Metabase, Reading Data, PM Apprenticeship