What fields exist in a MongoDB collection? Schema inspection how-to

Posted by Kyle Hankinson August 9, 2026

Point a SQL developer at an unfamiliar database and the first keystrokes are automatic: DESCRIBE, \d, or a query against information_schema.columns. Point the same developer at an unfamiliar MongoDB collection and there is nothing to type, because there is no catalog to ask. Each document carries its own structure, so "the schema" is not a definition stored anywhere; it is an emergent property of whatever documents happen to be in the collection right now. That is not a gap you work around once, it is a question you will answer weekly: what fields exist in here, and can I trust them?

The answers below run from a five-second eyeball to a full census with type checking, all executed on MongoDB 8.2.11 and re-checked on 7.0.37. The guinea pig is a contacts collection of six documents seeded with realistic inconsistencies: one missing email, one missing plan, two with createdAt stored as a string instead of a date, two with a nested address object (whose shapes also differ), and stray one-off fields like legacyId and referredBy.

The quick look is findOne(), or a few random documents via $sample (available since 3.2):

db.contacts.aggregate([ { $sample: { size: 2 } } ])
// { name: 'Donald K.', email: 'don@example.com',
//   createdAt: ISODate('2025-07-04T12:00:00.000Z'), plan: 'free', referredBy: 'ada@example.com' }
// { name: 'Alan Turing', email: 'alan@example.com',
//   createdAt: '2023-11-02', plan: 'pro', phone: '+44 20 946 0018' }

Useful, and already suspicious: one createdAt is an ISODate, the other a bare string. But sampling can only show you the fields it happens to draw. To actually enumerate the keys, you turn each document into data about itself. The $objectToArray operator (3.4.4+) converts a document into an array of {k, v} pairs; $unwind gives each pair its own document; $group counts them:

db.contacts.aggregate([
  { $project: { fields: { $objectToArray: "$$ROOT" } } },
  { $unwind: "$fields" },
  { $group: { _id: "$fields.k", count: { $sum: 1 } } },
  { $sort: { count: -1, _id: 1 } }
])
// { _id: '_id', count: 6 }        { _id: 'plan', count: 5 }
// { _id: 'createdAt', count: 6 }  { _id: 'address', count: 2 }
// { _id: 'name', count: 6 }       { _id: 'legacyId', count: 1 }
// { _id: 'email', count: 5 }      { _id: 'phone', count: 1 }
//                                 { _id: 'referredBy', count: 1 }, { _id: 'tags', count: 1 }

This is the closest MongoDB gets to information_schema.columns, and the counts are the payload: email exists on only 5 of 6 documents, and four fields exist exactly once. Every count below the collection total is a null check your application code needs. (The pipeline mechanics here, $group and friends, are covered in the aggregation pipeline primer.) If a 2012-era Stack Overflow answer steers you toward mapReduce for this job instead, skip it; mapReduce has been deprecated since MongoDB 5.0.

Same field, different types

Presence is only half the schema question. The other half is whether a field holds the same BSON type everywhere, and this is where document stores quietly hurt you. Extending the census with the $type operator groups by field and type together; collapsing that with a second $group reports only the drifters:

db.contacts.aggregate([
  { $project: { fields: { $objectToArray: "$$ROOT" } } },
  { $unwind: "$fields" },
  { $group: { _id: { field: "$fields.k", type: { $type: "$fields.v" } },
              count: { $sum: 1 } } },
  { $group: { _id: "$_id.field",
              types: { $push: { type: "$_id.type", count: "$count" } },
              typeCount: { $sum: 1 } } },
  { $match: { typeCount: { $gt: 1 } } }
])
// { _id: 'createdAt',
//   types: [ { type: 'date', count: 4 }, { type: 'string', count: 2 } ],
//   typeCount: 2 }

One field, two types, and the damage is concrete: a date-range filter like { createdAt: { $gte: ISODate("2023-01-01") } } matched 4 of the 6 documents on this collection. The two string-dated contacts are invisible to it, with no error, because BSON compares dates and strings as different types. If your "recent signups" numbers look low, run this detector before doubting the query.

Nested objects need one more hop. Pointing $objectToArray at the subdocument instead of $$ROOT enumerates its keys; on this data it showed address.state exists on only one of the two addresses:

db.contacts.aggregate([
  { $match: { address: { $type: "object" } } },
  { $project: { fields: { $objectToArray: "$address" } } },
  { $unwind: "$fields" },
  { $group: { _id: "$fields.k", count: { $sum: 1 } } }
])
// { _id: 'city', count: 2 }, { _id: 'country', count: 2 }, { _id: 'state', count: 1 }

Two footnotes on the census. It reads every document, so on a big collection either put a $match in front or run it on a $sample first and accept approximate counts. And indexes are free schema documentation: db.contacts.getIndexes() on this collection revealed a unique sparse index on email and a compound { plan: 1, createdAt: -1 }, which tells you which fields the application actually queries and which it insists are unique, before you read a line of its code.

For day-to-day browsing you rarely want to type the census by hand. The collection inspector in SQLPro for MongoDB shows sampled field names with their BSON types alongside the index list and collection stats, which makes it the natural first pass on an unfamiliar collection; because it samples rather than scans, a rare field can escape it, and that is exactly when the census pipeline above earns its keep.

Opting back into a schema

Once inspection shows drift, MongoDB can be told to stop accepting it. A $jsonSchema validator (3.6+) attaches rules to a collection, per the schema validation documentation:

db.createCollection("contacts_v", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["name", "email", "createdAt"],
    properties: {
      name:      { bsonType: "string" },
      email:     { bsonType: "string", pattern: "^.+@.+$" },
      createdAt: { bsonType: "date" },
      plan:      { enum: ["free", "pro", "team"] }
    }
  } }
})

Inserting a document with a string createdAt into that collection now fails with error code 121, Document failed validation, and the error detail names the offender precisely: propertyName: 'createdAt', specifiedAs: { bsonType: 'date' }, consideredValue: '2024-01-20', consideredType: 'string'. The same insert with a real date succeeds. For existing collections, collMod attaches a validator after the fact, with validationLevel: "moderate" available to grandfather old documents while policing new writes.

That is the honest shape of "schemaless": the schema exists whether or not the database enforces it, and the census pipeline, the type-drift detector, and a validator are how you find it, trust it, and keep it. The same discipline applies when translating SQL habits to MongoDB generally: the flexibility is real, but so is the bookkeeping it hands back to you.

Tags: MongoDB

E11000 Duplicate Key Error in MongoDB (and dup key: null)

Posted by Kyle Hankinson August 2, 2026

Somewhere in your logs is a write failure that makes no sense:

E11000 duplicate key error collection: shop.users index: email_1 dup key: { email: null }

You never inserted null into email. You may never have inserted email at all. That is precisely the problem, and it is the most-viewed MongoDB error on Stack Overflow for a reason: the root cause lives in an index you may not remember creating, and the intuitive fixes make it worse. This article reproduces the error on MongoDB 8.2, explains the null trap, and works through the fixes in the order you should try them.

Reading the message

E11000 is MongoDB's unique constraint violation, and everything you need is packed into the text:

collection: shop.users is the namespace (database shop, collection users).
index: email_1 is the index that rejected the write. The name encodes the key pattern: field email, ascending. Compound indexes chain them, like email_1_tenant_1.
dup key: { email: null } is the value that already exists in the index.

The error code is 11000, which matters when you handle it in application code. When the duplicate is a real value, the message is doing its job honestly:

E11000 duplicate key error collection: shop.users index: email_1 dup key: { email: "a@b.com" }

The confusion starts when that value is null and your code never wrote one.

The null trap, in four lines

Unique indexes in MongoDB index every document in the collection, whether or not the document contains the indexed field. A document with no email field is stored in the index under the key null. Unique means at most one document per key, so at most one document in the whole collection may omit the field. The second one collides:

db.users.createIndex({ email: 1 }, { unique: true })

db.users.insertOne({ name: "Ana" })   // fine: occupies the null slot
db.users.insertOne({ name: "Ben" })   // E11000 ... dup key: { email: null }

Verified on MongoDB 8.2.11 and 7.0.37; the error text is identical on both. Very old servers (the 2.x and 3.x era) printed dup key: { : null } without the field name, which is the variant fossilized in the top Stack Overflow answers.

This bites hardest in two situations. First, adding a unique field to an existing collection: every pre-existing document lacks the field, so the second write after the index appears fails. Second, stale indexes: you had unique: true on a field in a Mongoose schema, renamed or removed the field, and the index quietly stayed behind. Mongoose's unique option is not a validator; it is an instruction to create an index once, and deleting it from the schema drops nothing on the server.

Find the index you forgot about

The index named in the error is the whole diagnosis. List them:

db.users.getIndexes()

[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { email: 1 }, name: 'email_1', unique: true }
]

Anything with unique: true on a field your current code no longer populates is your suspect. If you browse your data in a GUI, this check is a glance rather than a shell command; SQLPro for MongoDB shows every collection's index list with unique badges in its collection inspector, right next to the sampled fields and their BSON types, which makes a stale email_1 stand out immediately.

The fixes, ranked

1. If the index is obsolete, drop it. The rename-and-leftover-index case needs nothing cleverer:

db.users.dropIndex("email_1")

2. If you want uniqueness only when the field exists, use a partial index. This is the modern fix (available since MongoDB 3.2). The index only includes documents matching a filter, so documents without the field are simply not indexed and never collide:

db.users.dropIndex("email_1")
db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $exists: true } } }
)

db.users.insertOne({ name: "Ana" })                      // ok
db.users.insertOne({ name: "Ben" })                      // ok now
db.users.insertOne({ name: "Cy", email: "a@b.com" })     // ok
db.users.insertOne({ name: "Di", email: "a@b.com" })     // still E11000

All four results verified on 8.2. Real duplicates still fail, missing fields sail through. One caveat: an explicit email: null value satisfies $exists: true, so if your application writes literal nulls, filter on type instead: partialFilterExpression: { email: { $type: "string" } }.

3. The sparse index is the legacy version of the same idea. { unique: true, sparse: true } also skips documents missing the field (verified: both no-email inserts succeed). It predates partial indexes, only supports "field is absent" as its condition, and the manual recommends partial indexes instead because sparse indexes can be silently ignored for sorts and return incomplete results when used to cover queries. Reach for it only on servers older than 3.2, which in 2026 should be nobody.

4. Or actually populate the field. If every document should have an email, the fix is a backfill, not an index change. The error is doing you a favor.

Before you create a unique index: find existing duplicates

Building a unique index on a collection that already contains duplicates fails mid-build with the same E11000, naming whichever duplicate it hit first. Check first with a $group:

db.contacts.aggregate([
  { $group: { _id: "$email", count: { $sum: 1 }, ids: { $push: "$_id" } } },
  { $match: { count: { $gt: 1 } } }
])

{ _id: null, count: 2, ids: [ ObjectId('...776'), ObjectId('...777') ] }
{ _id: 'a@b.com', count: 2, ids: [ ObjectId('...773'), ObjectId('...774') ] }

Note the first row: documents missing the field group under _id: null, exactly mirroring how the index will treat them. That row predicts a dup key: { email: null } failure before you ever build the index. Decide per group which _ids to keep, delete the rest, then create the index. The same keep-one-per-group reasoning applies in relational databases, where we covered it in how to find and delete duplicate rows in SQL.

The upsert race, briefly

There is one variant where E11000 appears without any modeling mistake. Two concurrent upserts filter on the same key, both find nothing, and both proceed to insert; one wins, the other gets E11000. Since MongoDB 4.2 the server retries most of these internally, but the race can still surface under load or when the filter does not exactly match the unique key. The fix is in application code: treat error code 11000 on an upsert as "someone else inserted first" and retry the operation once. On the second pass the document exists and the upsert takes its update path.

E11000 always tells the truth about one thing: some unique index rejected a key. When the key is a value, find the other document holding it. When the key is null, stop looking at your data and start looking at getIndexes(), because the collision is between two documents that never mention the field at all.

Tags: MongoDB

MongoDB aggregation pipeline basics: $match, $group, $sort, $project

Posted by Kyle Hankinson July 26, 2026

Sooner or later every MongoDB query outgrows find(). The moment you need a GROUP BY, a computed column, or a join, you are writing an aggregation pipeline, and the pipeline rewards one mental model above all others: it is a conveyor belt. Documents enter at the left, each stage transforms or filters what it receives and passes the result to the next stage, and order matters exactly as it does with Unix pipes. That is the whole trick. Most beginner pain comes not from the model but from two syntax rules of $group, and this article reproduces both mistakes on purpose so you recognize the symptoms.

Everything below ran on MongoDB 8.2.11 (official mongo:8 Docker image), with the failure cases re-confirmed on 7.0.37, against 1,000 generated order documents shaped like this:

db.orders.findOne()
// {
//   orderId: 1,
//   category: 'toys',
//   region: 'east',
//   quantity: 5,
//   price: 141.25,
//   placedAt: ISODate('2026-05-16T00:00:00.000Z')
// }

Suppose the question is: for electronics, what is the revenue per region, highest first? In SQL that is one statement; in MongoDB it is four stages, and the productive way to write it is one stage at a time, inspecting output after each addition.

Stage one, $match, is the WHERE. Run it alone first (a trailing $count is a cheap sanity check):

db.orders.aggregate([
  { $match: { category: "electronics" } },
  { $count: "n" }
])
// [ { n: 205 } ]

205 of 1,000 documents survive. Put $match as early as possible: at the beginning of a pipeline it can use an index, per the aggregation pipeline documentation, and every document it discards is work no later stage has to do.

Stage two, $group, is the GROUP BY. The grouping key goes in a field that must be called _id, and accumulators like $sum and $avg compute the aggregates:

db.orders.aggregate([
  { $match: { category: "electronics" } },
  { $group: { _id: "$region",
              revenue: { $sum: { $multiply: ["$price", "$quantity"] } },
              orders:  { $sum: 1 } } }
])
// { _id: 'north', revenue: 15388.880000000001, orders: 61 }
// { _id: 'south', revenue: 13978.19, orders: 48 }
// { _id: 'west',  revenue: 10811.94, orders: 48 }
// { _id: 'east',  revenue: 14458.869999999999, orders: 48 }

Note the $ prefixes: "$region" and "$price" mean "the value of that field in the incoming document." (The floating-point dust on north is ordinary double arithmetic, cleaned up below.) $sum: 1 adds one per document, which is how you spell COUNT(*). Grouping with _id: null collapses everything into one row; that variant returned the grand total, 1,000 orders and 295,749.87 in revenue.

Stage three, $sort, orders what $group emitted (-1 descending), and stage four, $project, shapes the final output: here it renames _id back to something readable and rounds the noise away.

db.orders.aggregate([
  { $match: { category: "electronics" } },
  { $group: { _id: "$region",
              revenue: { $sum: { $multiply: ["$price", "$quantity"] } },
              orders:  { $sum: 1 } } },
  { $sort: { revenue: -1 } },
  { $project: { _id: 0, region: "$_id", orders: 1, revenue: { $round: ["$revenue", 2] } } }
])
// { orders: 61, region: 'north', revenue: 15388.88 }
// { orders: 48, region: 'east',  revenue: 14458.87 }
// { orders: 48, region: 'south', revenue: 13978.19 }
// { orders: 48, region: 'west',  revenue: 10811.94 }

That is the whole pattern: filter, group, sort, shape. Since MongoDB 4.2, $set and $unset exist as friendlier aliases for the add-a-field and drop-a-field uses of $project.

The two ways your $group goes wrong

Both classic $group bugs share a symptom profile: no error, plausible-looking output, wrong numbers. Here is each one, run for real.

Mistake one: dropping the $ prefix on the grouping key. _id: "category" does not mean "group by category"; it means "group by the constant string category", so every document lands in a single bucket:

db.orders.aggregate([ { $group: { _id: "category", n: { $sum: 1 } } } ])
// { _id: 'category', n: 1000 }   <- one bucket, not five

Mistake two: dropping the $ inside an accumulator. $avg: "price" averages the constant string "price", which is not a number, so you get null; the same slip inside $sum quietly produces 0 because $sum ignores non-numeric values:

db.orders.aggregate([ { $group: { _id: "$category", avgPrice: { $avg: "price" } } } ])
// { _id: 'books', avgPrice: null } ...
db.orders.aggregate([ { $group: { _id: "$category", total: { $sum: "price" } } } ])
// { _id: 'books', total: 0 } ...

With the $ restored, avgPrice came back as 92.65 for books and 97.08 for electronics. A $sum column of zeros or an $avg of nulls is almost always this bug.

There is a third stumble worth naming: forgetting that the grouping key now lives in _id. Filtering on the original field name after a $group matches nothing at all:

db.orders.aggregate([
  { $group: { _id: "$category", n: { $sum: 1 } } },
  { $match: { category: "books" } }    // wrong: that field no longer exists
])
// []
db.orders.aggregate([
  { $group: { _id: "$category", n: { $sum: 1 } } },
  { $match: { _id: "books" } }         // right
])
// [ { _id: 'books', n: 208 } ]

That post-$group $match is also precisely how HAVING translates. Categories with more than 200 orders:

db.orders.aggregate([
  { $group: { _id: "$category", n: { $sum: 1 } } },
  { $match: { n: { $gt: 200 } } },
  { $sort: { n: -1 } }
])
// { _id: 'books', n: 208 }, { _id: 'electronics', n: 205 }, { _id: 'kitchen', n: 201 }

And COUNT DISTINCT is a two-stage idiom, group on the field then count the groups:

db.orders.aggregate([ { $group: { _id: "$region" } }, { $count: "distinctRegions" } ])
// [ { distinctRegions: 4 } ]

Memory limits, briefly

Each pipeline stage is capped at 100 MB of RAM. On MongoDB 6.0 and later the allowDiskUseByDefault parameter lets stages that exceed it spill to temporary disk files automatically, per the aggregation pipeline limits documentation, so the once-notorious "Exceeded memory limit for $group" error is mostly a pre-6.0 experience. On 4.x and 5.x you opt in per query with allowDiskUse: true. Either way, an early $match that shrinks the stream is the better fix than a bigger spill file. And if an older Stack Overflow answer suggests mapReduce for any of this, note it has been deprecated since MongoDB 5.0; the pipeline is the replacement.

The stage-at-a-time habit is also the debugging technique: when a pipeline misbehaves, delete stages from the end until the output looks right again, and the last stage you removed is your suspect. A client can shorten that loop considerably. The aggregation builder in SQLPro for MongoDB edits the pipeline as a stage list with insertable $match, $group, $sort, $project, and $lookup (and more) templates, and re-running after each edit puts the intermediate documents in a results grid.

From here, the natural next steps are joins with $lookup, covered in the SQL to MongoDB translation guide, and turning the pipeline loose on schema exploration, which is how you find out what fields a collection actually contains.

Tags: MongoDB

MongoDB Atlas Connection Errors: What Each One Actually Means

Posted by Kyle Hankinson July 19, 2026

First connections to MongoDB Atlas fail for a short, fixed list of reasons: your IP is not on the project's access list, your DNS resolver cannot answer SRV lookups, your credentials are wrong (or aimed at the wrong user system), or a special character in the password broke the URI. The frustrating part is that the error messages map to those causes badly. One of them even names the wrong culprit. This article is a decode table, with every error reproduced against real servers using mongosh 2.9.2.

What the two connection string formats actually do

Atlas hands you an SRV-style string:

mongodb+srv://appuser:secret@cluster0.ab1cd.mongodb.net/mydb

The +srv modifier means two things. First, the driver does not connect to cluster0.ab1cd.mongodb.net at all. It asks DNS for an SRV record listing the real cluster members, plus a TXT record carrying options. You can watch this happen yourself:

$ dig SRV _mongodb._tcp.cluster0.ab1cd.mongodb.net +short
0 0 27017 cluster0-shard-00-00-ab1cd.mongodb.net.
0 0 27017 cluster0-shard-00-01-ab1cd.mongodb.net.
0 0 27017 cluster0-shard-00-02-ab1cd.mongodb.net.

$ dig TXT cluster0.ab1cd.mongodb.net +short
"authSource=admin&replicaSet=Cluster0-shard-0"

Second, +srv automatically sets tls=true. That matters because Atlas refuses non-TLS connections on every tier, including free M0 clusters. SRV strings need drivers and shells from the MongoDB 3.6 era or newer; anything older must use the long form.

The long form is just the SRV answer written out by hand, and it connects to the same cluster identically (verified against a live Atlas cluster):

mongodb://appuser:secret@cluster0-shard-00-00-ab1cd.mongodb.net:27017,cluster0-shard-00-01-ab1cd.mongodb.net:27017,cluster0-shard-00-02-ab1cd.mongodb.net:27017/mydb?tls=true&replicaSet=Cluster0-shard-0&authSource=admin

Keep that equivalence in your pocket. It is the escape hatch for half the failures below.

The decode table

Error you see	What it actually means	Fix
`Error: querySrv ENOTFOUND _mongodb._tcp...` or `ESERVFAIL`	Your DNS resolver cannot answer the SRV lookup, or the hostname is mistyped	Switch DNS to 8.8.8.8 or 1.1.1.1, or use the long-form `mongodb://` string
`MongoServerSelectionError: Server selection timed out after 30000 ms`	Nothing answered on port 27017: IP not on the Atlas access list, or a firewall eats the port	Add your IP under Network Access; test the port directly
`...It looks like this is a MongoDB Atlas cluster. Please ensure that your Network Access List allows connections from your IP.`	Atlas closed the connection during the handshake. Usually the access list, but this hint also appears when TLS is off	Check Network Access first; if your IP is listed, check that TLS is enabled
`MongoServerError: bad auth : authentication failed`	Wrong password, wrong username, or the wrong kind of user entirely	Use a database user, not your Atlas login; keep `authSource=admin`
`MongoParseError: Password contains unescaped characters` / `URI malformed`	A special character in the password broke URI parsing	Percent-encode the password
`Client network socket disconnected before secure TLS connection was established`	You sent `tls=true` to a server that does not speak TLS (typically local, never Atlas)	Drop `tls=true` for plain local servers

Now the details, because several of these lie to you.

querySrv ENOTFOUND: DNS, not MongoDB

Error: querySrv ENOTFOUND _mongodb._tcp.cluster0.ab1cd.mongodb.net

The driver never reached Atlas. This failure happens entirely inside DNS: corporate resolvers, some VPNs, hotel networks, and certain Docker DNS configurations answer A-record queries fine but fail SRV queries. ESERVFAIL is the same story with a less cooperative resolver. Two fixes, in order of preference:

Point your machine (or container) at a public resolver such as 8.8.8.8 and retry.
Sidestep SRV entirely: run the two dig commands above from any network that can resolve them, then build the long-form string. No SRV lookup, no problem.

If dig returns NXDOMAIN from every resolver you try, the hostname itself is wrong. Copy the string fresh from the Atlas UI.

Server selection timed out: the access list, usually

MongoServerSelectionError: Server selection timed out after 30000 ms

DNS worked, but every TCP connection to port 27017 went unanswered until the driver gave up. Atlas only accepts connections from IPs on the project's IP access list, and silence is exactly what a blocked IP gets. Check the Network Access page in the Atlas UI, and remember the ways your egress IP changes underneath you: home connections rotate, joining or leaving a VPN swaps it, and code running in a cloud function has no stable IP at all (that last case needs 0.0.0.0/0 or network peering). Atlas supports temporary entries that expire within seven days, which is the right tool for "let me in from this coffee shop."

If your IP is definitely listed, suspect the network instead. Offices and hotels commonly block outbound 27017. Test the port without any MongoDB machinery:

nc -zv cluster0-shard-00-00-ab1cd.mongodb.net 27017

No route there means no driver setting will save you. This is where tunneling out over SSH through a machine with clean egress earns its keep; the mechanics are covered in our SSH tunnel walkthrough.

The misleading Atlas hint

Newer drivers try to help and sometimes point at the wrong thing. Connecting to a live Atlas cluster with TLS deliberately disabled produced this:

MongoServerSelectionError: connection <monitor> to 198.51.100.7:27017 closed. It looks like this is a MongoDB Atlas cluster. Please ensure that your Network Access List allows connections from your IP.

The access list was fine. TLS was the problem. The driver shows this hint whenever Atlas abruptly closes the connection, and both a blocked IP and a missing TLS handshake look identical from the client side. Older drivers phrased the same situation as "Could not connect to any servers in your MongoDB Atlas cluster," the wording most Stack Overflow threads quote. Either way, read it as "Atlas hung up on me": check the access list first, then confirm TLS is actually on (it always is with +srv; it is easy to lose when hand-building a long-form string).

bad auth: which user, and where

MongoServerError: bad auth : authentication failed

That is Atlas's exact wording; a local mongod says Authentication failed. instead. Three causes worth checking in order:

Wrong password. Obvious, but Atlas never shows the password again after creation, so reset it if unsure.
Wrong user system. Atlas has two: your Atlas account (the web login, often Google SSO) and database users created under Database Access. Connection strings only ever use the second kind.
Wrong authSource. Atlas database users live in the admin database. The SRV TXT record sets authSource=admin for you; hand-built long-form strings must include it. Verified locally: the same correct password fails with Authentication failed. when authSource points at the wrong database.

MongoParseError: the password ate your URI

The connection string manual requires percent-encoding for $ : / ? # [ ] @ in usernames and passwords. Tested with mongosh 2.9.2, the actual behavior is a mixed bag: a lone @ or : in the password happened to parse (the shell split on the last @), but a / produced MongoParseError: Password contains unescaped characters and a % produced MongoParseError: URI malformed. Other drivers are stricter than mongosh, so encode everything anyway:

p@ss:w0rd   becomes   p%40ss%3Aw0rd

After encoding, the same credentials authenticated successfully. A worse variant of this bug parses "successfully" but splits the string in the wrong place, sending the wrong host or password and surfacing as one of the other errors above. Special characters in the password belong on your suspect list for every error in this article.

Connection editors that take host, credentials, and options as separate fields avoid the encoding problem entirely, since there is no URI for the password to break. SQLPro for MongoDB takes that approach on Mac and iOS: a scheme picker for mongodb:// versus mongodb+srv://, a TLS toggle with an allow-invalid-certificates option, an auth source field, and built-in SSH tunneling for the networks where 27017 is simply closed.

The reverse TLS error, for completeness

Local development inverts the Atlas TLS situation. Pointing mongosh at a plain Docker mongod with tls=true fails like this (verified on MongoDB 8.2):

MongoServerSelectionError: Client network socket disconnected before secure TLS connection was established

If you see this against localhost, you copied an Atlas-shaped string at a server that never had certificates. Drop the TLS option. And if a plain mongodb://localhost:27017 string works but your Atlas string does not, you have confirmed the client is fine and the problem lives in one of the rows above.

Tags: MongoDB

SQL to MongoDB: WHERE, ORDER BY, GROUP BY, and JOIN translated

Posted by Kyle Hankinson July 12, 2026

If you come to MongoDB from SQL, the frustrating part is not the concepts. You know exactly what result you want; you just cannot spell it. The good news is that half of SQL maps onto one method, find(), almost mechanically. The catch is the other half: GROUP BY and JOIN have no place in find() at all and require a second API, the aggregation pipeline. Knowing where that line falls is most of the battle.

Every example below was executed on MongoDB 8.2.11 and spot-checked on 7.0.37 (the official mongo:8 and mongo:7 Docker images) using mongosh, which has been the default shell since MongoDB 5.0. Output shown is real.

First, the vocabulary. MongoDB's own SQL comparison chart uses the same mapping:

SQL	MongoDB
database	database
table	collection
row	document
column	field
primary key	`_id` (always present, always unique)

And the clause map, which the rest of this article walks through:

SQL clause	MongoDB equivalent
`WHERE`	the filter document passed to `find()`
`LIKE`	`$regex`
`ORDER BY`	`.sort()`
`LIMIT` / `OFFSET`	`.limit()` / `.skip()`
`GROUP BY`	`$group` stage in `aggregate()`
`HAVING`	`$match` stage placed after `$group`
`JOIN`	`$lookup` stage in `aggregate()`
`COUNT(*)`	`countDocuments()`
`SELECT DISTINCT`	`distinct()`
`UNION ALL`	`$unionWith` stage (MongoDB 4.4+)

The examples use two small collections you can recreate in seconds:

db.customers.insertMany([
  { _id: 1, name: "Alice Zhang",   city: "Toronto", country: "CA" },
  { _id: 2, name: "Bob Smith",     city: "Boston",  country: "US" },
  { _id: 3, name: "Carol Alvarez", city: "Chicago", country: "US" },
  { _id: 4, name: "Dan Smithers",  city: "Halifax", country: "CA" },
  { _id: 5, name: "Eve Laurent",   city: "Paris",   country: "FR" }
]);
db.orders.insertMany([
  { _id: 101, customerId: 1, product: "keyboard", amount: 89.00,  status: "shipped" },
  { _id: 102, customerId: 1, product: "monitor",  amount: 349.99, status: "shipped" },
  { _id: 103, customerId: 2, product: "mouse",    amount: 25.50,  status: "pending" },
  { _id: 104, customerId: 3, product: "monitor",  amount: 349.99, status: "shipped" },
  { _id: 105, customerId: 3, product: "webcam",   amount: 59.00,  status: "cancelled" },
  { _id: 106, customerId: 3, product: "keyboard", amount: 89.00,  status: "shipped" },
  { _id: 107, customerId: 5, product: "dock",     amount: 199.00, status: "pending" }
]);

WHERE is a document, not a string

The filter you would put after WHERE becomes a JSON document. Equality is just field: value; everything else uses $-prefixed operators.

SELECT product, amount FROM orders WHERE status = 'shipped' AND amount >= 300;

db.orders.find({ status: "shipped", amount: { $gte: 300 } },
               { _id: 0, product: 1, amount: 1 })
// { product: 'monitor', amount: 349.99 }  (twice, orders 102 and 104)

Two things to absorb here. The second argument is the projection, MongoDB's SELECT list (1 includes a field, _id: 0 suppresses the key you did not ask for). And multiple conditions in one filter document are implicitly ANDed; OR needs the explicit operator:

db.orders.find({ $or: [ { status: "cancelled" }, { amount: { $lt: 30 } } ] })
// matches the mouse (25.5, pending) and the webcam (59, cancelled)

IN translates directly: { status: { $in: ["pending", "cancelled"] } } returned the mouse, webcam, and dock orders. The comparison operators are $eq, $ne, $gt, $gte, $lt, $lte.

LIKE becomes $regex

There is no LIKE operator; patterns are regular expressions. LIKE 'Bob%' is an anchored regex, and LIKE '%smith%' (case-insensitive) is an unanchored one with the i option:

db.customers.find({ name: { $regex: "^Bob" } })                    // Bob Smith
db.customers.find({ name: { $regex: "smith", $options: "i" } })    // Bob Smith, Dan Smithers

One performance note carries over from SQL intuition: like LIKE 'Bob%', the anchored form ^Bob can use an index on name; a pattern that starts with a wildcard cannot.

ORDER BY, LIMIT, and OFFSET chain onto the cursor

SELECT product, amount FROM orders ORDER BY amount DESC LIMIT 3;

db.orders.find({}, { _id: 0, product: 1, amount: 1 }).sort({ amount: -1 }).limit(3)
// monitor 349.99, monitor 349.99, dock 199

1 is ascending, -1 descending, and OFFSET is .skip(): .sort({ placedAt: 1 }).skip(2).limit(2) is ORDER BY placedAt LIMIT 2 OFFSET 2. The same caveat as SQL's OFFSET applies, since skipped documents are still walked and discarded server-side.

GROUP BY means leaving find() behind

This is the line in the sand. find() cannot aggregate, so GROUP BY switches you to aggregate(), which takes an array of stages:

SELECT status, COUNT(*) AS orderCount, SUM(amount) AS total
FROM orders GROUP BY status;

db.orders.aggregate([
  { $group: { _id: "$status", orderCount: { $sum: 1 }, total: { $sum: "$amount" } } }
])
// { _id: 'cancelled', orderCount: 1, total: 59 }
// { _id: 'pending',   orderCount: 2, total: 224.5 }
// { _id: 'shipped',   orderCount: 4, total: 877.98 }

The grouping key always lands in a field called _id, not under its original name, and field references on the right-hand side need a $ prefix ("$status", "$amount"). Both rules trip up nearly everyone once. HAVING is nothing special in MongoDB: it is just a second filter placed after the $group:

SELECT customerId, SUM(amount) AS total FROM orders
GROUP BY customerId HAVING SUM(amount) > 400;

db.orders.aggregate([
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
  { $match: { total: { $gt: 400 } } }
])
// { _id: 1, total: 438.99 }
// { _id: 3, total: 497.99 }

The pipeline is a deep enough topic to earn its own article; the aggregation pipeline for beginners builds one stage by stage.

JOIN becomes $lookup (but ask if you need it)

Before translating your JOIN, pause: idiomatic MongoDB often embeds related data in one document (order lines inside the order), which makes the join disappear. When the data really lives in two collections, $lookup inside aggregate() does the job:

SELECT o.product, o.amount, c.name
FROM orders o JOIN customers c ON o.customerId = c._id;

db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customerId",
               foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" },
  { $project: { _id: 0, product: 1, amount: 1, customerName: "$customer.name" } }
])
// { product: 'keyboard', amount: 89, customerName: 'Alice Zhang' }  ...7 rows total

$lookup attaches the matches as an array; $unwind flattens it to one document per match, which is what makes the output look like an inner join. Without the $unwind, $lookup behaves like a LEFT OUTER JOIN: running it from customers toward orders kept Dan Smithers with an empty array (orderCount: 0 after a $size). If the join varieties themselves are the fuzzy part, the SQL joins explained walkthrough covers them engine-agnostically. Version notes: $lookup has existed since 3.2, and per the $lookup documentation, the concise correlated-subquery form (localField/foreignField combined with a pipeline) requires MongoDB 5.0+.

COUNT and DISTINCT

SELECT COUNT(*) FROM orders is db.orders.countDocuments(), which returned 7; pass a filter for a WHERE'd count (countDocuments({ status: "shipped" }) returned 4). SELECT DISTINCT status FROM orders is db.orders.distinct("status"), which returned ['cancelled', 'pending', 'shipped'].

The fastest way to make these mappings stick is to run both forms against live data and look at what comes back. Every snippet in this article pastes directly into the shell interface of SQLPro for MongoDB, where returned documents land in a table-style grid, so the row-to-document correspondence stops being abstract and there is a visual find builder when you would rather not type the filter document at all.

Keep the clause table above within reach for the first few weeks. The find-side translations become muscle memory quickly; the real shift is remembering that the moment a query aggregates or joins, you are writing a pipeline.