MongoDB Cheat Sheet: SQL to NoSQL Mindset Shift

MongoDB is a document-oriented NoSQL database that stores JSON-like documents (BSON) and uses JavaScript syntax in its official shell. For developers coming from MySQL or Oracle, the first step is to establish a mental mapping between relational and document concepts.

Concept Mapping: SQL to MongoDB

Database → Database (concept unchanged)

Table → Collection (not called a table)

Row → Document (one document = one JSON object)

Column → Field (keys inside a document)

Primary Key → _id field (defaults to ObjectId)

Foreign Key / JOIN → Embedded documents or $lookup (MongoDB favors embedding over foreign keys)

Critical difference: MongoDB has no "define columns at table creation" step. Collections are created on first insert, and documents in the same collection need not share the same structure.

Connection and Collection Operations

Connect to the default local instance (port 27017) or a specific URI with authentication:

# Connect to local default (27017)
mongosh

# Connect with URI, user, password, and database
mongosh "mongodb://user:[email protected]:27017/mydb"

# List all databases
show dbs

# Switch to (or create) a database — created on first write
use mydb

# List collections in current database
show collections

Collection operations (analogous to table DDL):

// Explicit creation (rarely used)
db.createCollection("users", { capped: false });

// Common practice: insert a document, collection auto-creates
db.users.insertOne({ name: "张三", age: 18 });

// Drop a collection (≈ DROP TABLE)
db.users.drop();

Daily development almost never uses createCollection; inserting directly is simpler.

Insert Operations

// Insert one document
db.users.insertOne({ name: "张三", age: 18, email: "[email protected]" });

// Insert multiple documents
db.users.insertMany([
  { name: "李四", age: 20 },
  { name: "王五", age: 25, tags: ["a", "b"] }
]);

// _id: auto-generated ObjectId if omitted; can be manually specified
db.users.insertOne({ _id: 1001, name: "特定id" });

The _id field is the document primary key, unique per collection. If not provided, MongoDB generates an ObjectId.

Query Operations (SELECT)

Basic Queries

// Find all documents
db.users.find()

// Conditional query
db.users.find({ age: 18 })

// Projection: 1 = include, 0 = exclude (except _id which defaults to 1)
db.users.find({ age: 18 }, { name: 1, _id: 0 })

// Return first match
db.users.findOne({ age: 18 })

Comparison Operators (≈ WHERE)

db.users.find({ age: { $gt: 18 } })      // age > 18
db.users.find({ age: { $gte: 18 } })     // age >= 18
db.users.find({ age: { $lt: 25 } })      // age < 25
db.users.find({ age: { $ne: 18 } })      // age != 18
db.users.find({ age: { $in: [18, 19, 20] } }) // IN

// Multiple conditions default to AND
db.users.find({ age: { $gte: 18 }, name: "张三" })

// Logical OR with $or
db.users.find({ $or: [{ age: 18 }, { name: "张三" }] })

Regex, Existence, Array Matching

// Fuzzy match: name contains "张"
db.users.find({ name: /张/ })

// Field exists
db.users.find({ age: { $exists: true } })

// Array element match: documents where roles array contains "admin"
db.users.find({ roles: "admin" })

Sorting, Pagination, Counting

// Sort: 1 = ascending, -1 = descending
db.users.find().sort({ age: -1 })

// Pagination: skip 20, limit 10
db.users.find().limit(10).skip(20)

// Count documents matching condition
db.users.countDocuments({ age: { $gte: 18 } })

Update Operations

// Update one document, $set modifies only specified fields
db.users.updateOne({ name: "张三" }, { $set: { age: 19 } })

// Update all matching documents
db.users.updateMany({ age: { $lt: 18 } }, { $set: { status: "未成年" } })

// Remove a field with $unset
db.users.updateOne({ name: "张三" }, { $unset: { email: "" } })

// Increment a numeric field with $inc
db.users.updateOne({ name: "张三" }, { $inc: { age: 1 } })

// Array push / pull
db.users.updateOne({ name: "张三" }, { $push: { tags: "c" } })
db.users.updateOne({ name: "张三" }, { $pull: { tags: "c" } })

Common pitfall: updateOne without $set replaces the entire document (except _id). Always use $set when you only want to change specific fields.

Delete Operations

// Delete one matching document
db.users.deleteOne({ name: "张三" })

// Delete all matching documents
db.users.deleteMany({ age: { $lt: 18 } })

// Empty a collection (≈ TRUNCATE)
db.users.deleteMany({})

// Drop entire collection (≈ DROP TABLE)
db.users.drop()

// Drop database (use with caution)
db.dropDatabase()

Index Management

// Single-field index
db.users.createIndex({ age: 1 })

// Compound index
db.users.createIndex({ name: 1, age: -1 })

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

// List all indexes
db.users.getIndexes()

// Drop an index
db.users.dropIndex({ age: 1 })

Mapping of relational index concepts:

Primary Key → _id (automatic unique index)

Unique Constraint → createIndex({field:1}, {unique:true}) Regular Index → createIndex({field:1}) Note: MongoDB has no auto-increment concept. _id defaults to ObjectId (globally unique). Numeric auto-increment IDs require a manual counter collection.

Aggregation Pipeline (GROUP BY Equivalent)

Aggregation is MongoDB's core for grouping statistics and complex queries, analogous to SQL's GROUP BY plus aggregate functions. A pipeline is a sequence of stages prefixed with $.

// Group by age and count per age
db.users.aggregate([
  { $group: { _id: "$age", count: { $sum: 1 } } }
])

// Multi-stage: filter, group, sort
db.users.aggregate([
  { $match: { age: { $gte: 18 } } },
  { $group: { _id: "$name", avgAge: { $avg: "$age" } } },
  { $sort: { avgAge: -1 } }
])

// Join another collection (similar to JOIN) via $lookup
db.orders.aggregate([
  { $lookup: {
    from: "users",
    localField: "user_id",
    foreignField: "_id",
    as: "userInfo"
  } }
])

SQL to aggregation stage mapping: WHERE →

$match

GROUP BY

→

$group

COUNT/SUM/AVG

→ $sum /

$avg

ORDER BY

→

$sort

JOIN

→

$lookup

Troubleshooting and Execution Plans

Use explain("executionStats") to see the query execution plan (≈ EXPLAIN in SQL):

db.users.find({ email: "[email protected]" }).explain("executionStats")

Compare totalDocsExamined (documents scanned) with nReturned (documents returned). If scanning hundreds or thousands but returning only a few, the query is not using an index — consider creating one.

Key Takeaways

Concepts differ: No tables/rows/columns; collections/documents/fields instead. No schema definition — documents are schema-less.

Primary key is not auto-increment: _id defaults to ObjectId; numeric auto-increment requires a custom counter collection.

Write operations must use $set : updateOne without $set replaces the whole document — the most common mistake.

Statistics rely on aggregation pipelines: Chain $match → $group → $sort to replace SQL's GROUP BY.

Once the mindset shifts from SQL to NoSQL, MongoDB often becomes simpler to work with.