Sign inTry Free

MongoDB® to SQL Mapping

On this page

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;

Last modified: August 9, 2024

Was this article helpful?

On this page

Was this article helpful?