26
loading...
Migration from Mssqlserver to Mongodb for Developers
This article is for those who want to migrate from SQL Server to MongoDB. In this article, I have shown a syntax similar to SqlServer and MongoDB with examples for developers.
Terminology and Concepts
The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.
Database
Database is a physical container for collections. Each database gets its own set of files on the file system. A single MongoDB server typically has multiple databases.
Collection
Collection is a group of MongoDB documents. It is the equivalent of an RDBMS table. A collection exists within a single database. Collections do not enforce a schema. Documents within a collection can have different fields. Typically, all documents in a collection are of similar or related purpose.
Document
A document is a set of key-value pairs. Documents have dynamic schema. Dynamic schema means that documents in the same collection do not need to have the same set of fields or structure, and common fields in a collection's documents may hold different types of data.
The following table shows the relationship of RDBMS terminology with MongoDB:
| SQL Terms/Concepts | MongoDB Terms/Concepts |
|---|---|
database |
database |
table |
collection |
row |
document |
column |
field |
index |
index |
table joins |
$lookup , embedded documents |
primary key Specify any unique column or column combination as primary key. |
primary key In MongoDB, the primary key is automatically set to the field. |
aggregation (e.g. group by) |
aggregation pipeline |
SELECT INTO NEW_TABLE |
$out |
MERGE INTO TABLE |
(Available starting in MongoDB 4.2) |
UNION ALL |
(Available starting in MongoDB 4.4) |
transactions |
Tip For many scenarios, the will continue to be optimal for your data and use cases instead of multi-document transactions. That is, for many scenarios, modeling your data appropriately will minimize the need for multi-document transactions. |
Create and Alter
The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements :
|
SQL Schema Statements |
MongoDB Schema Statements |
|---|---|
CREATE TABLE people ( id NOT NULL IDENTITY(1, 1), user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) ) |
Implicitly created on first insertOne() or insertMany() operation. The primary key _id is automatically added if _id field is not specified. db.people.insertOne({
user_id: "abc123",
age: 55,status: "A"
})However, you can also explicitly create a collection: db.createCollection("people") |
ALTER TABLE people ADD join_date DATETIME |
Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, updateMany() operations can add fields to existing documents using the $set operator. db.people.updateMany(
{ },
{ $set: { join_date: new Date() }
}) |
ALTER TABLE people DROP COLUMN join_date |
Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, updateMany() operations can remove fields from documents using the $unset operator. db.people.updateMany(
{ },
{ $unset: { "join_date":""}
}) |
CREATE INDEX idx_user_id_asc ON people(user_id) |
db.people.createIndex(
{ user_id: 1 }) |
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id, age DESC) |
db.people.createIndex(
{ user_id: 1, age: -1 }
) |
DROP TABLE people |
db.people.drop() |
The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.
|
SQL INSERT Statements |
MongoDB insertOne() Statements |
|---|---|
INSERT INTO people
(user_id, age,status)
VALUES ("bcd001",45,"A")
|
db.people.insertOne(
{user_id: "bcd001",
age: 45, status: "A"})
|
The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.
|
SQL SELECT Statements |
MongoDB find() Statements |
|---|---|
SELECT * FROM people |
db.people.find() |
SELECT id,user_id,status FROM people |
db.people.find({ },
{ user_id: 1, status: 1 })
|
SELECT user_id, status FROM people |
db.people.find({ },
{ user_id: 1, status: 1, _id: 0 }
) |
SELECT * FROM people WHERE status = "A" |
db.people.find(
{ status: "A" }
) |
SELECT user_id, status FROM people WHERE status = "A" |
db.people.find(
{ status: "A" },
{ user_id: 1, status: 1, _id: 0 }
) |
SELECT * FROM people WHERE status != "A" |
db.people.find(
{ status: { $ne: "A" } }) |
SELECT * FROM people WHERE status = "A" AND age = 50 |
db.people.find(
{ status: "A",age: 50
}) |
SELECT * FROM people WHERE status = "A" OR age = 50 |
db.people.find(
{ $or: [ { status: "A" }
,{ age: 50 } ] }
) |
SELECT * FROM people WHERE age > 25 |
db.people.find({ age: { $gt: 25 } }) |
SELECT * FROM people WHERE age < 25 |
db.people.find(
{ age: { $lt: 25 } }) |
SELECT * FROM people WHERE age > 25 AND age <= 50 |
db.people.find(
{ age: { $gt: 25, $lte: 50 } }
) |
SELECT * FROM people WHERE user_id like "%bc%" |
db.people.find( { user_id: /bc/ } )-or- db.people.find(
{ user_id: { $regex: /bc/ } } ) |
SELECT * FROM people WHERE user_id like "bc%" |
db.people.find( { user_id: /^bc/ } )-or- db.people.find(
{ user_id: { $regex: /^bc/ } } ) |
SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC |
db.people.find(
{ status: "A" } )
.sort( { user_id: 1 } ) |
SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC |
db.people.find({ status: "A"})
.sort( { user_id: -1 }) |
SELECT COUNT(*) FROM people |
db.people.count() or db.people.find().count() |
SELECT COUNT(user_id) FROM people |
db.people.count(
{ user_id: { $exists: true }
})or db.people.find(
{ user_id:{ $exists: true }
}).count()
|
SELECT COUNT(*) FROM people WHERE age > 30 |
db.people.count(
{ age:{ $gt: 30 }})or db.people.find(
{ age: { $gt: 30 } }).count() |
SELECT DISTINCT(status) FROM people |
db.people.aggregate([
{ $group:{ _id : "$status" }}
])or, for distinct value sets that do not exceed the BSON size limit db.people.distinct( "status") |
SELECT * FROM people order by ID OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY; |
db.people.findOne() or db.people.find().limit(1) |
SELECT * FROM people order by ID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY; |
db.people.find().limit(5) .skip(10) |
SELECT * FROM people WHERE status = "A" |
db.people.find(
{ status: "A"}
).explain() |
Update Records
The following table presents the various SQL statements related to updating existing records in tables and the corresponding MongoDB statements :
|
SQL Update Statements |
MongoDB updateMany() Statements |
|---|---|
UPDATE people SET status = "C" WHERE age > 25 |
db.people.updateMany(
{ age: { $gt: 25 } },
{ $set: { status: "C"}
}) |
UPDATE people SET age = age + 3 WHERE status = "A" |
db.people.updateMany(
{ status: "A" } ,
{ $inc: { age: 3 } }
) |
Delete Records
The following table presents the various SQL statements related to deleting records from tables and the corresponding MongoDB statements.
|
SQL Delete Statements |
MongoDB deleteMany() Statements |
|---|---|
DELETE FROM people WHERE status = "D" |
db.people.deleteMany(
{ status: "D" }) |
DELETE FROM people |
db.people.deleteMany({}) |
Comparison Query Operators
MongoDB comparison operators can be used to compare values in a document. The following table contains the common comparison operators.:
| SQL | MongoDB |
|---|---|
select * from Employee where name = 'ehsan' |
db.Employee.find({},
{"name":"ehsan"})
|
select * from Employee where salary < 5000 |
db.Employee.find({},
{"salary":{$lt:5000}})
|
select * from Employee where salary <= 5000 |
db.Employee.find({},
{"salary":{$lte:5000}})
|
select * from Employee where salary > 5000 |
db.Employee.find({},
{"salary":{$gt:5000}})
|
select * from Employee where salary >= 5000 |
db.Employee.find({},
{"salary":{$gt:5000}})
|
select * from Employee where salary != 5000 |
db.Employee.find(
{"salary":{$ne:50}
})
|
select * from Employee
where salary in
('ehsan','bahar','ali') |
db.Employee.find(
{"name":
{$in:["ehsan", "bahar", "ali"]}
})
|
select * from Employee
where salary not in ('ehsan','bahar','ali')
|
db.Employee.find(
{"name":{$nin:["ehsan", "bahar", "ali"]}
})
|
Logical Operators
MongoDB logical operators can be used to filter data based on given conditions. These operators provide a way to combine multiple conditions. Each operator equates the given condition to a true or false value.
| SQL | MongoDB |
|---|---|
select * from Employee where job_role = 'Store Associate' and (emp_age <= 20 and emp_age >= 20 ) |
db.Employee.find(
{ $and: [{"job_role":
"Store Associate"},
{"emp_age": {$gte: 20, $lte: 30}}]
})
|
select * from Employee where ( job_role = 'Senior Cashier' or job_role = 'Store Manager') |
db.Employee.find({
$or:[{"job_role":
"Senior Cashier"},
{"job_role":
"Store Manager"}]
})
|
select * from Employee
where job_role not in
('Senior Cashier ',
'Store Manager ')
|
db.employees.find(
{$nor:[{"job_role":
"Senior Cashier"},
{"job_role":
"Store Manager"}]
})
|
select * from Employee where not emp_age >= 40 |
db.Employee.find(
{ "emp_age":
{ $not: { $gte: 40}}})
|
26