Migration from Mssqlserver to Mongodb for Developers
Tags: sql, mongodb, database
Published: 2021-09-01 05:44:52

<p>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.</p>
<h2>Terminology and Concepts</h2>
<p>The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.</p>
<h2>Database</h2>
<p>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.</p>
<h2>Collection</h2>
<p>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.</p>
<h2>Document</h2>
<p>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&#39;s documents may hold different types of data.</p>
<p>The following table shows the relationship of RDBMS terminology with MongoDB:</p>
<table cellpadding="0" cellspacing="0" class="table table-hover">
    <thead>
        <tr>
            <th >
                <div>SQL Terms/Concepts</div>
            </th>
            <th>
                <div >MongoDB Terms/Concepts</div>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
                <div>
<pre>database</pre>
</div>
            </td>
            <td>
                <div>
<pre>database</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>table</pre></div>
            </td>
            <td>
                <div><pre>collection</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>row</pre></div>
            </td>
            <td>
                <div><pre>document</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>column</pre></div>
            </td>
            <td>
                <div><pre>field</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>index</pre></div>
            </td>
            <td>
                <div><pre>index</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>table joins</pre></div>
            </td>
            <td>
                <div><pre>$lookup , embedded documents</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div>
<pre>primary key Specify any unique 
   column or column combination 
   as primary key.</pre>
         </div>
            </td>
            <td>
                <div>
<pre>primary key In MongoDB,
the primary key is automatically
set to the field.</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>aggregation (e.g. group by)</pre></div>
            </td>
            <td>
                <div><pre>aggregation pipeline</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>SELECT INTO NEW_TABLE</pre></div>
            </td>
            <td>
                <div><pre>$out</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>MERGE INTO TABLE</pre></div>
            </td>
            <td>
                <div>
<pre>(Available starting in MongoDB 4.2)</pre>
</div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>UNION ALL</pre></div>
            </td>
            <td>
                <div><pre>(Available starting in MongoDB 4.4)</pre></div>
            </td>
        </tr>
        <tr>
            <td>
                <div><pre>transactions</pre></div>
            </td>
            <td>
                <div>


<pre>
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.</pre>
</div>
            </td>
        </tr>
    </tbody>
</table>
<h3>Create and Alter</h3>
<p>The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements :</p>
<table cellpadding="0" cellspacing="0">
    <thead>
        <tr>
            <th >
                <p>SQL Schema Statements</p>
            </th>
            <th >
                <p>MongoDB Schema Statements</p>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
                <div >&nbsp;
                    <table>
                        <tbody>
                            <tr>
<pre>
CREATE TABLE people (
id NOT NULL
IDENTITY(1, 1),
user_id Varchar(30),
age Number,
status char(1),
PRIMARY KEY (id)
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <p>Implicitly created on first <a href="https://docs.mongodb.com/manual/reference/method/db.collection.insertOne/#mongodb-method-db.collection.insertOne">&nbsp;insertOne()&nbsp;</a> or <a href="https://docs.mongodb.com/manual/reference/method/db.collection.insertMany/#mongodb-method-db.collection.insertMany">&nbsp;insertMany()&nbsp;</a> operation. The primary key _id is automatically added if _id field is not specified.</p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.insertOne({
user_id: &quot;abc123&quot;,
age: 55,status: &quot;A&quot;
})</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p>However, you can also explicitly create a collection:</p>
                    <table>
                        <tbody>
                            <tr>
                                <pre>db.createCollection(&quot;people&quot;)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >&nbsp;
                    <table>
                        <tbody>
                            <tr>
<pre>ALTER TABLE people
ADD join_date DATETIME</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <p>Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level.</p>
                    <p>However, at the document level, <a href="https://docs.mongodb.com/manual/reference/method/db.collection.updateMany/#mongodb-method-db.collection.updateMany">&nbsp;updateMany()&nbsp;</a> operations can add fields to existing documents using the <a href="https://docs.mongodb.com/manual/reference/operator/update/set/#mongodb-update-up.-set">&nbsp;$set&nbsp;</a> operator.</p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.updateMany(
{ },
{ $set: { join_date: new Date() } 
})</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>ALTER TABLE people
DROP COLUMN join_date</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <p>Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level.</p>
                    <p>However, at the document level, <a href="https://docs.mongodb.com/manual/reference/method/db.collection.updateMany/#mongodb-method-db.collection.updateMany">&nbsp;updateMany()&nbsp;</a> operations can remove fields from documents using the <a href="https://docs.mongodb.com/manual/reference/operator/update/unset/#mongodb-update-up.-unset">&nbsp;$unset&nbsp;</a> operator.</p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.updateMany(
{ },
{ $unset: { &quot;join_date&quot;:&quot;&quot;}
})</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>CREATE INDEX idx_user_id_asc
ON people(user_id)</pre>
</tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.createIndex(
 { user_id: 1 })</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>CREATE INDEX
idx_user_id_asc_age_desc</td>
ON people(user_id, age DESC)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.createIndex( 
{ user_id: 1, age: -1 }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >&nbsp; &nbsp;&nbsp;
                    <table>
                        <tbody>
                            <tr>
                                <pre>DROP TABLE people</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
                                <pre>db.people.drop()</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
    </tbody>
</table>
<p>
    <br>
</p>
<h3>Insert
    <a href="https://docs.mongodb.com/manual/reference/sql-comparison/#insert" title="Permalink to this headline"></a></h3>
<p>The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.</p>
<table cellpadding="0" cellspacing="0">
    <thead>
        <tr>
            <th >
                <p>SQL INSERT Statements</p>
            </th>
            <th >
                <p>MongoDB insertOne() Statements</p>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
                <div>
   
<pre>                            
INSERT INTO people
(user_id, age,status)     
VALUES (&quot;bcd001&quot;,45,&quot;A&quot;)
 </pre>                           
 </div>
            </td>
            <td>
                <div >

<pre>                            
db.people.insertOne(
{user_id: &quot;bcd001&quot;,
age: 45, status: &quot;A&quot;})</pre>                           

                </div>
            </td>
        </tr>
    </tbody>
</table>
<p>
    <br>
</p>
<h3>Select
    <a href="https://docs.mongodb.com/manual/reference/sql-comparison/#select" title="Permalink to this headline"></a></h3>
<p>The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.</p>
<table cellpadding="0" cellspacing="0">
    <thead>
        <tr>
            <th  >
                <p>SQL SELECT Statements</p>
            </th>
            <th  >
                <p>MongoDB find() Statements</p>
            </th>
        </tr>
    </thead>
<tbody>
<tr>
<td>
<div>
<pre>SELECT * FROM people</pre>
</div>
</td>

<td>
<div>

<pre>db.people.find()</pre>

</div>
</td>        
        </tr>
</tbody>
        <tr>
            <td>
                <div >

<pre>SELECT id,user_id,status
FROM people </pre>


                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
<pre>
db.people.find({ },
{ user_id: 1, status: 1 })
</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
                             <pre>SELECT user_id, status
FROM people </pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find({ },
{ user_id: 1, status: 1, _id: 0 }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE status = &quot;A&quot; </pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ status: &quot;A&quot; }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT user_id, status
FROM people
WHERE status = &quot;A&quot;
</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ status: &quot;A&quot; },
{ user_id: 1, status: 1, _id: 0 }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE status != &quot;A&quot;</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ status: { $ne: &quot;A&quot; } })</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE status = &quot;A&quot;
AND age = 50</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ status: &quot;A&quot;,age: 50 
})</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
<tr>
<pre>SELECT * FROM people
WHERE status = &quot;A&quot;
OR age = 50</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ $or: [ { status: &quot;A&quot; }
,{ age: 50 } ] }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE age &gt; 25</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find({ age: { $gt: 25 } })</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE age &lt; 25</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ age: { $lt: 25 } })</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE age &gt; 25
AND age &lt;= 50</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ age: { $gt: 25, $lte: 50 } }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people</td>
WHERE user_id like &quot;%bc%&quot;</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find( { user_id: /bc/ } )</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p>-or-</p>
                    <table>
                        <tbody>
<tr>
<pre>db.people.find(
 { user_id: { $regex: /bc/ } } )</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE user_id like &quot;bc%&quot;</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >&nbsp; &nbsp; &nbsp;
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find( { user_id: /^bc/ } )</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p>-or-</p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find( 
{ user_id: { $regex: /^bc/ } } )</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE status = &quot;A&quot;
ORDER BY user_id ASC</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find( 
{ status: &quot;A&quot; } )
.sort( { user_id: 1 } )</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE status = &quot;A&quot;
ORDER BY user_id DESC</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find({ status: &quot;A&quot;})
.sort( { user_id: -1 })</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT COUNT(*) FROM people</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.count()</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p><em>or</em></p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find().count()</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT COUNT(user_id) FROM people</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.count(
{ user_id: { $exists: true } 
})</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p><em>or</em></p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find(
{ user_id:{ $exists: true } 
}).count()
</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT COUNT(*) FROM people
WHERE age &gt; 30</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.count( 
{ age:{ $gt: 30 }})</pre>

                            </tr>
                        </tbody>
                    </table>
                    <p><em>or</em></p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find( 
{ age: { $gt: 30 } }).count()</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT DISTINCT(status) FROM people</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.aggregate([ 
{ $group:{ _id : &quot;$status&quot; }} 
])</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p>or, for distinct value sets that do not exceed the <a href="https://docs.mongodb.com/manual/reference/limits/#std-label-limit-bson-document-size">BSON size limit</a></p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.distinct( &quot;status&quot;)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
order by ID
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;
</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.findOne()</pre>
                            </tr>
                        </tbody>
                    </table>
                    <p><em>or</em></p>
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find().limit(1)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
order by ID
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;
</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
                                <pre>db.people.find().limit(5)
.skip(10)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>SELECT * FROM people
WHERE status = &quot;A&quot;</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.find( 
{ status: &quot;A&quot;} 
).explain()</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
    </tbody>
</table>
<p>
    <br>
</p>
<h3>Update Records</h3>
<p>The following table presents the various SQL statements related to updating existing records in tables and the corresponding MongoDB statements :</p>
<p>
    <br>
</p>
<table cellpadding="0" cellspacing="0">
    <thead>
        <tr>
            <th>
                <p>SQL Update Statements</p>
            </th>
            <th>
                <p>MongoDB updateMany() Statements</p>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>UPDATE people
SET status = &quot;C&quot;
WHERE age &gt; 25</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.updateMany(
{ age: { $gt: 25 } },
{ $set: { status: &quot;C&quot;} 
})</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>UPDATE people
SET age = age + 3
WHERE status = &quot;A&quot;</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.updateMany(
{ status: &quot;A&quot; } ,
{ $inc: { age: 3 } }
)</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
    </tbody>
</table>
<p>
    <br>
</p>
<h3>Delete Records
<p>The following table presents the various SQL statements related to deleting records from tables and the corresponding MongoDB statements.</p>
<p>
    <br>
</p>
<table cellpadding="0" cellspacing="0">
    <thead>
        <tr>
            <th>
                <p>SQL Delete Statements</p>
            </th>
            <th>
                <p>MongoDB deleteMany() Statements</p>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>DELETE FROM people
WHERE status = &quot;D&quot;</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.deleteMany( 
{ status: &quot;D&quot; })</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
        <tr>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>DELETE FROM people</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
            <td>
                <div >
                    <table>
                        <tbody>
                            <tr>
<pre>db.people.deleteMany({})</pre>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </td>
        </tr>
    </tbody>
</table>
<h3>Comparison Query Operators</h3>
<p>MongoDB comparison operators can be used to compare values in a document. The following table contains the common comparison operators.:</p>
<table cellpadding="0" cellspacing="0" >
    <thead>
        <tr>
            <th>
                <div style="text-align: center;">SQL&nbsp;</div>
            </th>
            <th>
                <div  style="text-align: center;"><span  >MongoDB</div>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
         
<pre>select * from Employee&nbsp;
where name = &#39;ehsan&#39;</pre>
            </td>
            <td>
<pre>db.Employee.find({},
{&quot;name&quot;:&quot;ehsan&quot;})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where salary &lt; 5000</pre>
            </td>
            <td>
<pre>db.Employee.find({},
{&quot;salary&quot;:{$lt:5000}})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where salary &lt;= 5000</pre>
            </td>
            <td>
<pre>&nbsp;db.Employee.find({},
{&quot;salary&quot;:{$lte:5000}})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where salary &gt; 5000</pre>
            </td>
            <td>
<pre>db.Employee.find({},
{&quot;salary&quot;:{$gt:5000}})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where salary &gt;= 5000</pre>
            </td>
            <td>
<pre>db.Employee.find({},
{&quot;salary&quot;:{$gt:5000}})</pre>
            </td>
        </tr>
        <tr>
<td><pre>select * from Employee
where salary != 5000</pre></td>
            <td>
<pre>db.Employee.find(
{&quot;salary&quot;:{$ne:50}
})</pre>
            </td>
        </tr>
        <tr>
<td><pre>select * from Employee
where salary in
 (&#39;ehsan&#39;,&#39;bahar&#39;,&#39;ali&#39;)</pre></td>
<td>
<pre>db.Employee.find(
{&quot;name&quot;:
{$in:[&quot;ehsan&quot;, &quot;bahar&quot;, &quot;ali&quot;]}
})</pre>
                <br>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where salary not in (&#39;ehsan&#39;,&#39;bahar&#39;,&#39;ali&#39;)</pre>
            </td>
            <td>
<pre>db.Employee.find(
{&quot;name&quot;:{$nin:[&quot;ehsan&quot;, &quot;bahar&quot;, &quot;ali&quot;]}
})</pre>
            </td>
        </tr>
    </tbody>
</table>
<h2>Logical Operators</h2>
<p>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.</p>
<table cellpadding="0" cellspacing="0" >
    <thead>
        <tr>
            <th>
                <div style="text-align: center;">SQL&nbsp;</div>
            </th>
            <th>
                <div  style="text-align: center;"><span  >MongoDB</div>
            </th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>
<pre>select * from Employee&nbsp;
where job_role = &#39;Store Associate&#39; 
and (emp_age &lt;= 20 and&nbsp;
emp_age &gt;= 20 )</pre>
            </td>
            <td>
<pre>db.Employee.find(
{ $and: [{&quot;job_role&quot;:
&quot;Store Associate&quot;}, 
{&quot;emp_age&quot;: {$gte: 20, $lte: 30}}]
})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select &nbsp;* &nbsp;from Employee
where ( job_role =
&nbsp;&#39;Senior Cashier&#39;&nbsp;
or job_role =&nbsp;&#39;Store Manager&#39;)</pre>
            </td>
            <td>
<pre>db.Employee.find({ 
$or:[{&quot;job_role&quot;:
&quot;Senior Cashier&quot;},
{&quot;job_role&quot;:
 &quot;Store Manager&quot;}]
})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where job_role not in</p>
&nbsp;(&#39;Senior Cashier &#39;,
&#39;Store Manager &#39;)</pre>
            </td>
            <td>
<pre>&nbsp;db.employees.find(
{$nor:[{&quot;job_role&quot;:
 &quot;Senior Cashier&quot;},
 {&quot;job_role&quot;:
 &quot;Store Manager&quot;}]
})</pre>
            </td>
        </tr>
        <tr>
            <td>
<pre>select * from Employee
where not
&nbsp;emp_age &gt;= &nbsp;40
</pre>
            </td>
            <td>
<pre>db.Employee.find(
{ &quot;emp_age&quot;:
 { $not: { $gte: 40}}})</pre>
            </td>
        </tr>
    </tbody>
</table>
<p>
    <br>
</p>