Skip to main content

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

_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;
In this section: