MongoDB Cheatsheet 19 — Migration from SQL

SELECT * FROM users WHERE age > 25 LIMIT 10

db.users.find({ age: { $gt: 25 } }).limit(10)

WHERE age = 30 AND status = 'active'

{ age: 30, status: "active" }

WHERE age > 25 OR status = 'admin'

{ $or: [{ age: { $gt: 25 } }, { status: "admin" }] }

ORDER BY created DESC

.sort({ created: -1 })

LIMIT 10 OFFSET 20

.skip(20).limit(10)

SELECT DISTINCT status FROM users

db.users.distinct("status")

SELECT user_id, SUM(amount) FROM orders GROUP BY user_id

db.orders.aggregate([
    { $group: { _id: "$user_id", total: { $sum: "$amount" } } },
])

SELECT u.name, p.title
FROM users u JOIN posts p ON p.user_id = u.id

db.users.aggregate([
    {
        $lookup: {
            from: "posts",
            localField: "_id",
            foreignField: "user_id",
            as: "posts",
        },
    },
    { $unwind: "$posts" },
])

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')

db.users.insertOne({ name: "Alice", email: "[email protected]" })

UPDATE users SET age = 31 WHERE id = 1

db.users.updateOne({ _id: 1 }, { $set: { age: 31 } })

DELETE FROM users WHERE age < 18

db.users.deleteMany({ age: { $lt: 18 } })

CREATE INDEX idx_email ON users(email)

db.users.createIndex({ email: 1 })

WHERE name LIKE 'Al%'

{ name: /^Al/ }

WHERE id IN (1, 2, 3)

{ _id: { $in: [1, 2, 3] } }

SELECT COUNT(*) FROM users

db.users.countDocuments()
db.users.estimatedDocumentCount()   // faster, approximate

WHERE email IS NULL
WHERE email IS NOT NULL

{ email: null }
{ email: { $ne: null } }
{ email: { $exists: true } }

BEGIN;
UPDATE accounts SET bal=bal-100 WHERE id=1;
UPDATE accounts SET bal=bal+100 WHERE id=2;
COMMIT;

session.withTransaction(() => {
    db.accounts.updateOne({_id:1}, {$inc:{bal:-100}}, {session});
    db.accounts.updateOne({_id:2}, {$inc:{bal:100}}, {session});
})

SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END FROM users

{ $project: { status: { $cond: [{ $gte: ["$age", 18] }, "adult", "minor"] } } }