MongoDB query to TypeScript: carrying a pipeline into the Node.js driver

#The query works. Now it has to live in the codebase

You have a query that returns the right rows. In a shell, or in a GUI, or in a converter. The next step is always the same: it has to become code in the application, and for a large share of MongoDB projects that application is TypeScript on the Node.js driver.

That step is where a working query quietly stops working. Not because the translation is hard — the pipeline is JSON and the driver takes JSON — but because TypeScript will happily type-check a pipeline that returns something other than what you told it to expect.

#The one thing that carries over unchanged

Start with what is genuinely portable. This is the SQL:

SELECT customer_id, SUM(amount) AS total
FROM payments
WHERE status = 'settled'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10

And this is the MongoDB pipeline it becomes:

[
  { $match: { status: "settled" } },
  { $group: { _id: "$customer_id", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
]

That array is the artifact worth keeping. It is valid JSON, the shell takes it, every official driver takes it, and it does not change shape between languages. Whatever tool produced it — a converter, a colleague, an aggregation builder — what you carry into TypeScript is the pipeline, not a language-specific rendering of it.

#Dropping it into the Node.js driver

import { MongoClient } from "mongodb";

const client = new MongoClient(process.env.MONGODB_URI!);
const db = client.db("shop");

const pipeline = [
  { $match: { status: "settled" } },
  { $group: { _id: "$customer_id", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
];

const rows = await db.collection("payments").aggregate(pipeline).toArray();

This runs, and it is also the version that will bite you. rows is typed as Document[] — effectively any per field. Every property access below this line is unchecked, and the compiler has no opinion about whether the pipeline produces total or totalAmount or nothing at all.

#Types do not flow through a pipeline

The driver types the collection, not the aggregation:

type Payment = {
  _id: ObjectId;
  customer_id: string;
  amount: number;
  status: "pending" | "settled" | "refunded";
};

const payments = db.collection<Payment>("payments");

payments.find({ status: "settled" }) is now checked — a typo in status, or comparing it to "setled", is a compile error, because find takes a Filter<Payment>. That is real safety and worth having.

payments.aggregate(...) gives you none of it. A pipeline can rename, reshape and invent fields at every stage, and the driver does not attempt to compute the result type from the stage array. The output type is something you assert:

type CustomerTotal = { _id: string; total: number };

const rows = await payments
  .aggregate<CustomerTotal>(pipeline)
  .toArray();

rows is now CustomerTotal[], and the rest of the file is type-checked against that. But understand exactly what happened: you told TypeScript the shape. Nothing verified it. If the $group stage says totalAmount and CustomerTotal says total, this compiles, runs, and hands you an array of objects whose total is undefined — typed as number.

That is the whole risk of moving a query into TypeScript, and it is worth stating plainly: the type annotation on aggregate is a promise you make to the compiler, not a check the compiler performs.

#Three details that are easy to get wrong

_id is the grouping key, not an id. After a $group, _id holds whatever you grouped by — a string here, and an object when you group by more than one field. Typing it as ObjectId out of habit is a mistake the compiler cannot catch.

WithId<T> is what find returns. The driver adds _id to your document type on read, so find returns WithId<Payment>[] even if Payment omits _id. Declaring the field yourself, as above, keeps the two in step.

A cursor is not an array. aggregate() returns an AggregationCursor. .toArray() pulls the whole result into memory, which is fine for the ten rows above and is not fine for a $match that selects a million documents. Iterate with for await (const row of cursor) when the result is large — the same reason a LIMIT-less SELECT is a bad idea in any database.

#Keeping the SQL as the source

There is a version of this workflow that removes the retyping step entirely: write the query in SQL, read the pipeline the engine generates, and copy that pipeline into the driver call. The pipeline is then something you reviewed rather than something you assembled, and the SQL stays in the pull request as the statement of intent — which is the part a reviewer can actually check.

Two honest notes about what TableCore does here. It shows the generated MongoDB command and it exports the query as mongosh and as C# — driver code always, LINQ when a lambda expresses the query faithfully. There is no TypeScript export button. What you carry across is the pipeline, and the typing work above is yours. The SQL that reaches a pipeline at all is listed in SQL support.

The browser-based SQL to MongoDB converter runs entirely in your browser and gives you that pipeline without installing anything. If your query groups rows, GROUP BY as a pipeline covers the stage above in more depth; for the C# side of the same journey, see SQL query to pipeline to C# code.