Translating your knowledge from SQL (Relational Databases) to MongoDB (NoSQL Document Databases) requires shifting from tables and rows to collections and BSON/JSON documents.
The structural mappings establish the foundation of query translation: Table becomes Collection Row becomes Document Column becomes Field Joins become Embedded Documents or \(lookup</strong> Basic CRUD Translations</p> <p>For basic read and write commands, MongoDB uses targeted methods rather than a unified text-string query language. SQL Operation MongoDB Equivalent <code>INSERT INTO users (name) VALUES (‘Alice’);</code> <code>db.users.insertOne({ name: ‘Alice’ })</code> MongoDB automatically generates an <code>_id</code>. <code>SELECTFROM users;</code> <code>db.users.find({})</code> An empty object <code>{}</code> returns all documents. <code>SELECT name FROM users WHERE age = 25;</code> <code>db.users.find({ age: 25 }, { name: 1 })</code> The second object specifies <strong>projections</strong> (fields to show). <code>UPDATE users SET status = ‘A’ WHERE age > 30;</code></p> <p><code>db.users.updateMany({ age: { \)gt: 30 } }, { \(set: { status: ‘A’ } })</code></p> <p>Requires an explicit <code>\)set operator to avoid overwriting the document. DELETE FROM users WHERE status = ’D’; db.users.deleteMany({ status: ’D’ }) Clears matching documents from the collection. Advanced Querying & Aggregation
When SQL queries involve filtering, sorting, or grouping, MongoDB leverages either operators inside .find() or handles complex transformations using Aggregation Pipelines (db.collection.aggregate([])). 1. Filtering with WHERE Clauses
Instead of textual keywords like AND, OR, and LIKE, MongoDB relies on query operators.
SQL: SELECT * FROM products WHERE price >= 10 AND status = ‘active’;
MongoDB: db.products.find({ price: { \(gte: 10 }, status: 'active' })</code></p> <p><strong>SQL:</strong> <code>SELECT * FROM products WHERE category = 'Electronics' OR qty < 5;</code></p> <p><strong>MongoDB:</strong> <code>db.products.find({ \)or: [ { category: ‘Electronics’ }, { qty: { \(lt: 5 } } ] })</code> 2. Sorting and Pagination</p> <p>Sorting, limiting, and skipping records are handled via chained methods on the cursor object.</p> <p><strong>SQL:</strong> <code>SELECT * FROM users ORDER BY age DESC LIMIT 10 OFFSET 20;</code></p> <p><strong>MongoDB:</strong> <code>db.users.find({}).sort({ age: -1 }).skip(20).limit(10)</code> 3. Grouping and Aggregating (<code>GROUP BY</code>, <code>HAVING</code>)</p> <p>Complex reporting in SQL maps to MongoDB's <strong>Aggregation Framework</strong>, which passes data through successive pipeline stages. <strong>SQL:</strong></p> <p><code>SELECT department, COUNT(*), AVG(salary) AS avg_sal FROM employees GROUP BY department HAVING AVG(salary) > 50000; </code> Use code with caution. <strong>MongoDB:</strong> javascript</p> <p><code>db.employees.aggregate([ { \)group: { _id: “\(department", count: { \)sum: 1 }, avg_sal: { \(avg: "\)salary” } } }, { \(match: { avg_sal: { \)gt: 50000 } } } ]) Use code with caution. 4. Tables Relationships (JOIN)
While MongoDB favors embedding data natively inside a single document to avoid joins, you can perform relational joins using the \(lookup</code> stage. <strong>SQL:</strong></p> <p><code>SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id; </code> Use code with caution. <strong>MongoDB:</strong> javascript</p> <p><code>db.orders.aggregate([ { \)lookup: { from: “customers”, // Target collection localField: “customer_id”, // Field from the orders collection foreignField: “_id”, // Field from the customers collection as: “customer_details” // Output array field name } } ]) Use code with caution. Automated Native Translation Tools
If you are migrating legacy systems or writing code dynamically, you don’t always have to translate manually: Easily Translate SQL Queries to MongoDB Queries
Leave a Reply