# MongoDB® to SQL Mapping Here are a few conceptual mappings between MongoDB® and SQL. **Databases** Similar to MongoDB®, SingleStore databases serve as a lightweight container for scoping permissions and organizing collections, and they are implicitly created with a default configuration when collections are created. **Collections to Tables** In MongoDB®, collections are containers for documents. They are created either explicitly with the `createCollection` command or implicitly when a write occurs targeting a collection. In SingleStore, collections are represented as SQL tables. If a collection is created implicitly, a table is automatically created. **Documents to Rows** Whenever a BSON document is inserted through SingleStore Kai ("the API"), the document itself is mapped to a row in a target table. All top-level fields in the document are mapped to columns in the target table. Any top-level fields that do not match any columns are grouped together and inserted into a `_more` column. When a document is retrieved through the API, values from all the columns except the `_more` column are added to the document first. The contents of the `_more` column are included afterwards. If a field in the `_more` column has the same name as another column, the value from the existing column takes precedence over the field in the `_more` column. **BSON** Similar to MongoDB®, SingleStore Kai natively supports the BSON data type for storage. ## MongoDB® Terminologies and Concepts Mappings Table The following table maps MongoDB® terminologies and concepts to their SQL equivalents: | **MongoDB®Terms/Concepts** | **SQL Terms/Concepts** | | --------------------------------------- | --------------------------------------------------------- | | Database | Database | | Collection | Table | | View | View | | Document | Row | | Top-level field | Column | | Additional fields not matching a column | `_more`column | | Index | Index | | Primary key: the`_id`field | Primary key: Any unique column (including the`_id`column) | | `$lookup`or embedded documents | `JOIN` | | `$match` | `WHERE`,`HAVING` | | `$group` | `GROUP BY` | | `$sort` | `ORDER BY` | | `$limit` | `LIMIT` | | `$sum` | `SUM()` | | `$sum`,`$sortByCount` | `COUNT()` | ## `CREATE TABLE` and `ALTER TABLE` Statement Examples The following table shows `CREATE TABLE` and `ALTER TABLE` statements written in SQL and their MongoDB® equivalents: | **MongoDB®Statements** | **SQL Statements** | | ---------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- | | `db.createCollection("Stock")` | `CREATE TABLE Stock ( ID INT NOT NULL AUTO_INCREMENT, Code Varchar(30), Qty INT );` | | `db.createCollection("Stock", {rowStore:true} )` | `// Create Rowstore table CREATE ROWSTORE TABLE Stock ();` | | `//Create a new field db.Stock.updateMany( { }, { $set: { Status: "Y" } })` | `//Create a new column ALTER TABLE Stock ADD COLUMN Status CHAR(1); UPDATE Stock Set Status = 'Y';` | | `//Remove a field db.Stock.updateMany( { }, { $unset: { "Status": 1 } } )` | `//Remove a column ALTER TABLE DROP COLUMN Stock;` | | `db.Stock.drop()` | `DROP TABLE Stock;` | | `db.txs.createIndex( {"inventory.Quantity_Inventory": 1})` | ``ALTER TABLE inventory ADD COLUMN Quantity_Inventory AS JSON_EXTRACT_STRING(`_more`,Quantity_Inventory) PERSISTED INT;`` | ## `SELECT` Statement Examples The following table shows `SELECT` statements written in SQL and their MongoDB® equivalents: | **MongoDB®Statements** | **SQL Statements** | | ----------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------- | | `db.Stock.find()` | `SELECT * FROM Stock;` | | `db.Stock.find( { Status = "Y" }, { Code: 1, Qty: 1, _id: 0 } )` | `SELECT Code, Qty FROM Stock WHERE Status = "Y";` | | `db.Stock.find( { Status: "Y", Qty: { $gt: 25 } } )` | `SELECT * FROM Stock WHERE Status = "Y" AND Qty > 25;` | | `db.Stock.find( { $or: [{Status: "Y"}, {Qty: { $gt: 25}} ] } )` | `SELECT * FROM Stock WHERE Status = "Y" OR Qty > 25;` | | `db.Stock.find({ Stock: /^i/ }).sort({ _id: 1}) // Or db.Stock.find({ Stock: { $regex: /^i/ }}).sort({ _id: 1})` | `SELECT * FROM Stock WHERE Code LIKE "i%" ORDER BY ID ASC;` | | `db.Stock.find().sort({ id: -1 })` | `SELECT * FROM Stock ORDER BY ID DESC;` | | `db.Stock.find().limit(5).skip(2)` | `SELECT * FROM Stock LIMIT 5 SKIP 2;` | | `db.Stock.find( { Status: "Y" }).explain()` | `EXPLAIN SELECT * FROM Stock WHERE Code = "Y";` | ## `INSERT` Statement Examples The following table shows `INSERT` statements written in SQL and their MongoDB® equivalents: | **MongoDB®Statements** | **SQL Statements** | | ------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- | | `db.Stock.inserOne( {Stock: "at6h", Qty: 35 })` | `INSERT INTO Stock (Code, Qty) VALUES ( "at6h", 35);` | | `db.Stock.insertMany([ { Stock: "ik7b", Qty: 40 }, { Stock: "ph9x", Qty: 25 }, { Stock: "at6h", Qty: 20 }])` | `INSERT INTO Stock (Code, Qty) VALUES ("ik7b", 40), ("ph9x", 25), ("at6h", 20);` | ## `UPDATE` Statement Example The following table shows `UPDATE` statements written in SQL and their MongoDB® equivalents: | **MongoDB®Statements** | **SQL Statements** | | ------------------------------------------------------------------------------- | ----------------------------------------------- | | `db.Stock.updateMany( { Qty: { $gt: 40 } }, { $set: { Status: "Y" } })` | `UPDATE Stock SET Status = "Y" WHERE Qty > 40;` | ## `DELETE` Statement Examples The following table shows `DELETE` statements written in SQL and their MongoDB® equivalents: | **MongoDB®Statements** | **SQL Statements** | | ------------------------------------------- | --------------------------------------- | | `db.Stock.deleteMany( { Status: "Y" })` | `DELETE FROM Stock WHERE Status = "Y";` | | `db.Stock.deleteMany({})` | `DELETE FROM Stock;` | ## Aggregate Function Examples The following table shows aggregate functions written in MongoDB® and their SQL equivalents: | **MongoDB®Statements** | **SQL Statements** | | -------------------------------------------------------------------------------------------------- | ----------------------------- | | `db.Stock.aggregate([ { $group: { _id: null, count: { sum: 1 }}} ])` | `SELECT COUNT(*) FROM Stock;` | | `db.Stock.aggregate([ { $group: { _id: null, total: { $sum: "$Qty" }}} ])` | `SELECT SUM(Qty) FROM Stock;` | *** Modified at: August 9, 2024 Source: [/cloud/reference/singlestore-kai/mongodb-to-sql-mapping/](https://docs.singlestore.com/cloud/reference/singlestore-kai/mongodb-to-sql-mapping/) (An index of the documentation is available at /llms.txt)