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?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In