Migration from Mssqlserver to Mongodb for Developers

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 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 INTO people
(user_id, age,status)     
VALUES ("bcd001",45,"A")
 

                            
db.people.insertOne(
{user_id: "bcd001",
age: 45, status: "A"})

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 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 FROM people
WHERE status = "D"

db.people.deleteMany( 
{ status: "D" })

DELETE FROM people

db.people.deleteMany({})

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

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