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