SQL to MongoDB

Every SQL query TableCore runs becomes exactly one MongoDB command, or is refused. This page shows what that command looks like for the shapes people actually write, so you can check the translation instead of trusting it.

Every command below is a golden fixture from the test suite: it is pinned in tests/Core/OfflinePlanGoldenFixtures.cs, and an opt-in suite against a real MongoDB asserts that the connected engine sends exactly this. If the engine changes how it builds a pipeline, the fixture has to change in the same commit.

#Where to see it yourself

Three places show you the command, and they all come from the same capture, so they cannot disagree:

• As code — the query rendered for mongosh, the C# driver and C# LINQ. See Query as code.
• Convert to a MongoDB query — replaces the SQL in your editor with the shell chain, to edit and run.
• Explain — the plan the server produced for that exact command. See Explain plans.

#WHERE, ORDER BY, LIMIT and OFFSET

A SELECT from one collection with no grouping is a find.

SELECT * FROM users WHERE Age > 30 ORDER BY Name DESC LIMIT 10 OFFSET 5

{
  "find": "users",
  "filter": { "Age": { "$gt": 30 } },
  "sort": { "Name": -1 },
  "skip": 5,
  "limit": 10
}

A condition of the shape field against constant stays an ordinary filter that MongoDB can serve from an index. A condition that shape cannot express in full is compiled as $expr instead — correct, but $expr uses no index. The order those two are tried in is what decides whether your query starts from an index or from a scan.

#Projections and aliases

SELECT Name AS FullName, Age FROM users WHERE Active = true

{
  "find": "users",
  "filter": { "Active": true },
  "projection": { "_id": 1, "FullName": "$Name", "Age": 1 }
}

_id is kept because it is what makes the result editable in the grid. An alias becomes a computed projection, which is why an aliased column is read-only — the grid knows which field it came from, but writing back through a rename is a different feature.

#IN

SELECT Name FROM users WHERE Status IN ('active', 'hold')

{
  "find": "users",
  "filter": { "Status": { "$in": ["active", "hold"] } },
  "projection": { "_id": 1, "Name": 1 }
}

#LIKE

LIKE becomes an anchored regular expression, with % and _ translated and ESCAPE honoured.

SELECT Name FROM products WHERE Code LIKE 'A!_%' ESCAPE '!'

{
  "find" : "products",
  "filter" : { "Code" : /^A_.*/ },
  "projection" : { "_id" : 1, "Name" : 1 }
}

The filter carries a BSON regular expression, not a string — which is why this one is shown in shell notation rather than JSON. !_ escaped the _, so it matches a literal underscore; the unescaped % became .*.

#IS NULL

SQL's NULL covers two states MongoDB keeps apart — a field that is null and a field that is not there — so IS NULL has to ask for both.

SELECT Name FROM users WHERE DeletedAt IS NULL

{
  "find": "users",
  "filter": {
    "$or": [
      { "DeletedAt": { "$exists": false } },
      { "DeletedAt": null }
    ]
  },
  "projection": { "_id": 1, "Name": 1 }
}

#GROUP BY

Grouping moves the query to aggregate.

SELECT Status, COUNT(*) AS Cnt, SUM(Total) AS TotalSum FROM orders GROUP BY Status

{
  "aggregate": "orders",
  "pipeline": [
    { "$match": {} },
    { "$group": {
        "_id": "$Status",
        "Cnt": { "$sum": 1 },
        "TotalSum": { "$sum": "$Total" },
        "__tc_sum_seen_TotalSum": { "$sum": { "$cond": [
          { "$and": [
            { "$ne": ["$Total", null] },
            { "$ne": [{ "$type": "$Total" }, "missing"] }
          ] }, 1, 0 ] } }
    } },
    { "$project": {
        "_id": 0,
        "Status": "$_id",
        "Cnt": 1,
        "TotalSum": { "$cond": [
          { "$gt": ["$__tc_sum_seen_TotalSum", 0] }, "$TotalSum", null
        ] }
    } }
  ],
  "cursor": {}
}

That extra __tc_sum_seen_TotalSum accumulator is the witness that makes SUM behave like SQL's: MongoDB's $sum answers 0 for an empty set, SQL answers "unknown". Zero says the values added up to nothing; null says there were none. The witness counts real values and the $project turns a count of zero back into null. It never reaches you — the projection is an including list.

#HAVING

HAVING is a $match after the group and the projection, which is exactly where SQL puts it.

SELECT Status, COUNT(*) AS Cnt FROM orders GROUP BY Status HAVING COUNT(*) > 1

{
  "aggregate": "orders",
  "pipeline": [
    { "$match": {} },
    { "$group": { "_id": "$Status", "Cnt": { "$sum": 1 } } },
    { "$project": { "_id": 0, "Status": "$_id", "Cnt": 1 } },
    { "$match": { "Cnt": { "$gt": 1 } } }
  ],
  "cursor": {}
}

#JOIN

INNER JOIN is $lookup followed by $unwind that drops non-matches.

SELECT u.Name, o.Total FROM Users u INNER JOIN Orders o ON u._id = o.UserId

{
  "aggregate": "Users",
  "pipeline": [
    { "$lookup": {
        "from": "Orders", "localField": "_id", "foreignField": "UserId", "as": "o"
    } },
    { "$unwind": { "path": "$o", "preserveNullAndEmptyArrays": false } },
    { "$project": { "_id": 1, "Name": 1, "Total": "$o.Total" } }
  ],
  "cursor": {}
}

LEFT JOIN is the same pipeline with preserveNullAndEmptyArrays set to true. That one flag is the whole difference, and it is the same difference SQL draws.

A WHERE over the joined side becomes a $match between the $unwind and the $project, because those are the rows SQL says the condition sees.

#DISTINCT

SELECT DISTINCT Status FROM orders

{
  "aggregate": "orders",
  "pipeline": [
    { "$match": {} },
    { "$group": { "_id": "$Status" } },
    { "$project": { "_id": 0, "Status": "$_id" } }
  ],
  "cursor": {}
}

#Computed columns

An expression in the SELECT list becomes a computed projection, and every operand is converted with an explicit onError/onNull.

SELECT Name, Price * Quantity AS LineTotal FROM items

{
  "find": "items",
  "filter": {},
  "projection": {
    "_id": 1,
    "Name": 1,
    "LineTotal": { "$multiply": [
      { "$convert": { "input": "$Price", "to": "double", "onError": null, "onNull": null } },
      { "$convert": { "input": "$Quantity", "to": "double", "onError": null, "onNull": null } }
    ] }
  }
}

Those conversions are not decoration. MongoDB's arithmetic operators abort the whole query when an argument is the wrong type, so a single row with text in a numeric field would otherwise cost you every other row in the result.

#Where this does not reach

A query that is not one command — a set operator, a subquery read while the plan is being built — is not translated to one of its commands. It is named, with the steps listed in the order the engine runs them. See SQL support.

#The same translation in your browser

The converter runs this engine compiled to WebAssembly, entirely client-side, with no account and no database. It has no collection samples, so the handful of rewrites that depend on knowing a collection's shape are not available there — everything on this page is.

#Going deeper

The published articles work through individual shapes in more detail:

• MongoDB GROUP BY for SQL developers
• The MongoDB $lookup equivalent of a SQL JOIN
• The MongoDB equivalent of SQL's WHERE … IN