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()

Insert

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"})

Select

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}}})

3