MongoDB to SQL Mapping
Here are a few conceptual mappings between MongoDB and SQL.
Databases
Similar to MongoDB, SingleStoreDB 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 SingleStoreDB, collections are represented as SQL tables. If a collection is created implicitly, a table is automatically created.
Documents to Rows
When a BSON document is inserted through SingleStore Kai for MongoDB ("the API"), top-level fields in the document are mapped to columns in the target table. All 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 columns except the _more
column are added to the document before the contents of the _more
column are included. If a field in the _more
column has the same name as a column, the value from the existing column takes precedence over the field in the _more
column.
Value Conversions
Values passed to and from BSON documents to columns in a SingleStoreDB table undergo a conversion process. Refer to Data Type Mapping for information on BSON-to-SingleStoreDB data type mappings.
BSON to JSON
The API does not support the BSON data type for storage. Hence, arbitrary BSON types are mapped to SingleStoreDB's JSON type using the Relaxed Mode extension of MongoDB Extended JSON (v2). This conversion leads to certain limitations, such as numeric data types may lose their type information. For example, a value stored as an int64
type may be interpreted as an int32
type with the same value.
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 |
|
Index | Index |
Primary key: the | Primary key: Any unique column (including the |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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; |