{"id":106750,"date":"2025-05-30T18:14:41","date_gmt":"2025-05-30T18:14:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106750"},"modified":"2025-05-22T14:42:07","modified_gmt":"2025-05-22T14:42:07","slug":"moving-from-sql-to-mongodbs-mql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/nosql\/mongodb\/moving-from-sql-to-mongodbs-mql\/","title":{"rendered":"Moving from SQL to MongoDB\u2019s MQL"},"content":{"rendered":"\n

This article is part of Robert Sheldon's continuing series on Mongo DB. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n\n\n\n\n

People new to MongoDB often come to the platform with a background in relational database management systems (RDBMSs) such as MySQL or SQL Server. These platforms commonly use some form of the Structured Query Language (SQL) to manage and query the data within their databases. SQL was designed specifically to work with relational databases and manage their structures and data.<\/p>\n\n\n\n

MongoDB is not a relational database system, nor does it use SQL. Instead, it relies on the MongoDB Query Language (MQL), a proprietary language for working with the MongoDB environment. MQL uses a JSON-like syntax to interact with database structures and their data, making possible to create collections, add documents, update data, and do much more.<\/p>\n\n\n\n

Throughout this series, you\u2019ve seen plenty of examples of MQL in action as you learned how to use the language to manage and query MongoDB data. However, some of you who come to MongoDB with a SQL background could find it challenging to transition to MQL because the two languages are conceptually different in many way. In such a case, you might find it helpful to see a comparison between common queries in both a relational database and a MongoDB database.<\/p>\n\n\n\n

In this article, I compare a number basic SQL statements to their MQL counterparts in MongoDB. I\u2019m using MySQL for the SQL side of the comparison, in part because it is a popular platform, but also because I already had it installed on my system.<\/p>\n\n\n\n

Through these comparisons, you\u2019ll be able to see how common operations performed in a relational database can be performed in MongoDB. This might help you better conceptualize how MongoDB works and how to carry out specific tasks. If you have access to both a MySQL instance and MongoDB deployment, you can try out the examples in this article so you can experience first-hand how the two languages compare.<\/p>\n\n\n\n

You can use a RDBMS other than MySQL, but you\u2019ll likely need to tweak the SQL statements to accommodate the differences between platforms. For example, when you create a table in MySQL, you can include the AUTO_INCREMENT<\/code> attribute to generate a unique identity for new rows. In SQL Server, however, you would use the IDENTITY<\/code> property in your column definition. With this in mind, let\u2019s look how SQL and MQL compare.<\/p>\n\n\n\n

Note: For the examples in this article, I used the same MongoDB Atlas environment I used for the previous articles in this series. Refer to the first article for details about setting up these environments. The examples are based on the hr<\/code> database and employees<\/code> collection. If you plan to try out these examples, the database and collection should not exist or, if they do exist, should not contain documents or schema validation rules. The examples in this article also include SQL statements, which were written against a MySQL instance, as I\u2019ve already noted.<\/p>\n\n\n\n

Comparing a relational database to a MongoDB database<\/h2>\n\n\n\n

Moving from a RDBMS to MongoDB requires a shift in thinking about how data is stored, managed, and accessed. In a relational database, the data is highly normalized and very structured to minimize redundancy and ensure data integrity. MongoDB is much more flexible. The schema is defined at the document level and can be different from one document to the next, even within the same collection.<\/p>\n\n\n\n

Despite these differences, the main components within these two systems correspond with each other to some degree, which can help you better understand the differences and similarities between them. The following table shows how these components map to each other across the two systems.<\/p>\n\n\n\n

Relational platform<\/strong><\/p><\/td>

MongoDB<\/strong><\/p><\/td><\/tr>

database<\/p><\/td>

database<\/p><\/td><\/tr>

table<\/p><\/td>

collection<\/p><\/td><\/tr>

row<\/p><\/td>

document<\/p><\/td><\/tr>

column<\/p><\/td>

field<\/p><\/td><\/tr>

primary key<\/p><\/td>

primary key<\/p><\/td><\/tr>

index<\/p><\/td>

index<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

A database in a RDBMS stores data in highly structured tables with fixed schemas. A table\u2019s schema applies to all rows in that table. The schema is typically defined when creating the table, although it can be later modified, as long as the modification doesn\u2019t impact the existing data. Each table contains one or more columns that are defined with specific data types. A table also contains zero or more rows. A row is a collection of related data that describes a specific entity or event. All rows in a table have an identical structure that is based on the columns defined for that table.<\/p>\n\n\n\n

A MongoDB database is a logical container that stores data in collections, with each collection containing zero or more documents. Unlike a relational table, a MongoDB collection does not impose a fixed structure on the individuals documents within that collection. Each document can contain one or more fields, and those fields can be defined without regard to the other documents in the collection. In fact, the documents in a collection do not even need to be related to each other, although they typically are.<\/p>\n\n\n\n

It is possible to define validation rules on a MongoDB collection that enforce specific document schema, but that is an optional choice, not a requirement, as it is with a relational table.<\/p>\n\n\n\n

Both relational tables and MongoDB collections support the use of primary keys. In a relational database, you can define a primary key on one or more columns in any table. The primary key ensures that each row in the table can be uniquely identified. Although the primary key is not required, most tables typically have one.<\/p>\n\n\n\n

In MongoDB, the primary key is required and implemented through the _id<\/code> field of each document. If the _id<\/code> value is not provided when adding a document to a collection, MongoDB automatically generates one. A document cannot be added to a MongoDB collection without the _id<\/code> primary key.<\/p>\n\n\n\n

You can define indexes on both relational tables and MongoDB collections. In a relational database, you can define an index on one or more columns in a table. MongoDB can define an index on one or more fields in a collection. In both cases, indexes are used to help improve query performance, although they need to be used judiciously to avoid unnecessary overhead.<\/p>\n\n\n\n

Creating your database environment<\/h2>\n\n\n\n

When working with a relational database, you typical use some form of SQL to manage your databases and data, as well as query your data. This is similar to how you use MQL in MongoDB. The rest of the article demonstrates how to use the two languages to perform similar operations, such as creating objects or retrieving data. For each example, I\u2019ll first show you how to use SQL to carry out a specific operation in MySQL and then show you how to use MQL to carry out a similar operation in MongoDB.<\/p>\n\n\n\n

We\u2019ll start with creating and populating a simple database in MySQL. The following SQL statements create a database name hr<\/code>, switch the context to that database, and then add three tables to the database:<\/p>\n\n\n\n

CREATE DATABASE hr;\n\nUSE hr;\n\nCREATE TABLE positions (\n  position_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n  job_title VARCHAR(50) NOT NULL,\n  PRIMARY KEY (position_id) )\nAUTO_INCREMENT=101;\n\nCREATE TABLE departments (\n  dept_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n  dept_name VARCHAR(50) NOT NULL,\n  PRIMARY KEY (dept_id) )\nAUTO_INCREMENT=101;\n\nCREATE TABLE employees (\n  emp_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n  emp_name VARCHAR(50) NOT NULL,\n  position_id INT UNSIGNED NOT NULL,\n  dept_id INT UNSIGNED NOT NULL,\n  job_rating DECIMAL(4,1) NULL,\n  hire_date DATE NOT NULL,\n  current_emp BOOL NOT NULL,\n  PRIMARY KEY (emp_id),\n  CONSTRAINT fk_position_id FOREIGN KEY (position_id)\n    REFERENCES positions (position_id),\n  CONSTRAINT fk_dept_id FOREIGN KEY (dept_id)\n    REFERENCES departments (dept_id) )\nAUTO_INCREMENT=1001;<\/pre><\/div>\n\n\n\n
I don\u2019t plan to spend a great deal of time explaining how each SQL statement works. I\u2019m assuming that, if you\u2019re interesting in moving from a RDBMS to MongoDB, you already know how to use SQL. In case you should have forgotten something, however, you can find details about the SQL statements in the MySQL Reference Manual<\/a>. As for the MQL statements, I\u2019ve already described those in the earlier articles in this series.<\/p>\n\n\n\n
All that said, I do want to point out a few things about these statements. First, notice that the code starts with a CREATE<\/code> DATABASE<\/code> statement that creates the hr<\/code> database. This is followed by a USE<\/code> statement that switches the database context to hr<\/code>, after the database has been created.<\/p>\n\n\n\n
Next come the table definitions for the positions<\/code>, departments<\/code>, and employees<\/code> tables. Each one defines the columns that will be added to the table and designates one of those columns as the primary key. The primary key is configured with the AUTO_INCREMENT<\/code> attribute, which is set to a specific starting number. In addition, the employees<\/code> table includes two foreign keys: one that references the positions<\/code> table and one that references the departments<\/code> table.<\/p>\n\n\n\n
After the tables have been created, we can then run the following INSERT<\/code> statements to populate them with sample data:<\/p>\n\n\n\n
INSERT INTO positions (job_title)\nVALUES\n  ('Senior Developer'),\n  ('Developer'),\n  ('Data Scientist'),\n  ('Marketing Manager'),\n  ('SEO Specialist'),\n  ('QA Specialist'),\n  ('Brand Manager'),\n  ('Network Engineer'),\n  ('Security Specialist'),\n  ('Market Analyst');\n\nINSERT INTO departments (dept_name)\nVALUES\n  ('R&D'),\n  ('IT'),\n  ('Marketing'),\n  ('Manufacturing'),\n  ('Finance'),\n  ('Human Resources');\n\nINSERT INTO employees\n  (emp_name, position_id, dept_id, job_rating, hire_date, current_emp)\nVALUES\n  ('Drew', 101, 101, 97.6, '2013-10-31', 1),\n  ('Parker', 103, 101, 94.3, '2017-06-22', 1),\n  ('Harper', 104, 103, 89.9, '2021-11-14', 1),\n  ('Darcy', 101, 101, 98.3, '2015-06-05', 1),\n  ('Carey', 105, 103, 95.7, '2019-08-19', 1),\n  ('Avery', 108, 102, 82.6, '2022-01-29', 1),\n  ('Robin', 109, 102, 91.8, '2016-04-18', 1),\n  ('Koda', 106, 101, 87.7, '2020-10-10', 1),\n  ('Jessie', 107, 103, 72.2, '2023-09-15', 0),\n  ('Dana', 110, 103, 94.4, '2021-02-17', 1);<\/pre><\/div>\n\n\n\n
Each INSERT<\/code> statement specifies the names of specific columns in the target table and then provides the rows of data to be inserted into those columns. The primary key column is not included in the list of names because that data is generated automatically.<\/p>\n\n\n\n
Now let\u2019s look at how to do something similar in MongoDB. In this case, you don\u2019t need to specifically create a database. Instead, you run a use<\/code> command that changes the context to the hr<\/code> database. Once you\u2019ve switched context, you can then create the collection. MongoDB does not create the actual database until you add the first collection.<\/p>\n\n\n\n
To get started, run the following use<\/code> command to change the context to the hr<\/code> database:<\/p>\n\n\n\n
use hr;<\/pre><\/div>\n\n\n\n
You can then use the createCollection<\/code> method available to the database object to create the employees<\/code> collection if it doesn\u2019t already exist, as in the following command:<\/p>\n\n\n\n
db.createCollection(\"employees\");<\/pre><\/div>\n\n\n\n
When you create a collection, you don\u2019t define any schema. You\u2019re simply creating a logical container for the documents that will be added to the collection. Note, however, that you don\u2019t need to create the collection at all. If you try to insert documents into a nonexistent collection, MongoDB will create the collection automatically.<\/p>\n\n\n\n
To add documents to a MongoDB collection, you can use the insertOne<\/code> method to add a single document or use the insertMany<\/code> method to add multiple documents. For example, the following insertMany<\/code> command adds 10 documents to the employees<\/code> collection:<\/p>\n\n\n\n
db.employees.insertMany([\n  { \"emp_name\": \"Drew\",\n    \"position\": { \"job_title\": \"Senior Developer\", \"dept_name\": \"R&D\" },\n    \"job_rating\": 97.6,\n    \"hire_date\": new Date(\"2013-10-31\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Parker\",\n    \"position\": { \"job_title\": \"Data Scientist\", \"dept_name\": \"R&D\" },\n    \"job_rating\": 94.3,\n    \"hire_date\": new Date(\"2017-06-22\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Harper\",\n    \"position\": { \"job_title\": \"Marketing Manager\", \"dept_name\": \"Marketing\" },\n    \"job_rating\": 89.9,\n    \"hire_date\": new Date(\"2021-11-14\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Darcy\",\n    \"position\": { \"job_title\": \"Senior Developer\", \"dept_name\": \"R&D\" },\n    \"job_rating\": 98.3,\n    \"hire_date\": new Date(\"2015-06-05\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Carey\",\n    \"position\": { \"job_title\": \"SEO Specialist\", \"dept_name\": \"Marketing\" },\n    \"job_rating\": 95.7,\n    \"hire_date\": new Date(\"2019-08-19\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Avery\",\n    \"position\": { \"job_title\": \"Network Engineer\", \"dept_name\": \"IT\" },\n    \"job_rating\": 82.6,\n    \"hire_date\": new Date(\"2022-01-29\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Robin\",\n    \"position\": { \"job_title\": \"Security Specialist\", \"dept_name\": \"IT\" },\n    \"job_rating\": 91.8,\n    \"hire_date\": new Date(\"2016-04-18\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Koda\",\n    \"position\": { \"job_title\": \"QA Specialist\", \"dept_name\": \"R&D\" },\n    \"job_rating\": 87.7,\n    \"hire_date\": new Date(\"2020-10-10\"),\n    \"current_emp\": true },\n  { \"emp_name\": \"Jessie\",\n    \"position\": { \"job_title\": \"Brand Manager\", \"dept_name\": \"Marketing\" },\n    \"job_rating\": 72.2,\n    \"hire_date\": new Date(\"2023-09-15\"),\n    \"current_emp\": false },\n  { \"emp_name\": \"Dana\",\n    \"position\": { \"job_title\": \"Market Analyst\", \"dept_name\": \"Marketing\" },\n    \"job_rating\": 94.4,\n    \"hire_date\": new Date(\"2021-02-17\"),\n    \"current_emp\": true }\n]);<\/pre><\/div>\n\n\n\n
The documents contain the same data as what was added to the hr<\/code> database in MySQL, so all the documents have the same structure. However, you could have included different fields, arrays and other embedded documents, or fields with the same name but defined with different types.<\/p>\n\n\n\n
When working with a RDBMS, you must first create the database, add strictly defined tables to the database, and then add data that conforms to the table schema. In MongoDB, you need only switch the database context and add the documents to the collection, whether or not the collection already exists. The documents themselves define the schema, rather than being defined when creating the collection.<\/p>\n\n\n\n
Querying data in your database<\/h2>\n\n\n\n
One of the most important functions of any database management system is to ensure that the data is readily available so users and applications can query it when they need it, assuming they have the proper permissions. In this way, they can retrieve exactly the data they want and control how the data is returned.<\/p>\n\n\n\n
To demonstrate how this works in MySQL, let\u2019s start with a simple SELECT<\/code> statement. The following example retrieves all the rows and columns of data in the employees<\/code> table:<\/p>\n\n\n\n
SELECT * FROM employees;<\/pre><\/div>\n\n\n\n
The statement uses an asterisk in the SELECT<\/code> clause to indicate that all columns should be returned. The statement also returns all rows because it has not been qualified in any other way, which is often done through the addition of a WHERE<\/code> clause. The statement\u2019s results are returned as a grid, similar to a spreadsheet.<\/p>\n\n\n\n
The employees<\/code> table includes two foreign keys that are defined on the position_id<\/code> and dept_id<\/code> columns. The foreign keys point to the positions<\/code> and departments<\/code> tables, respectively. Because they\u2019re foreign keys, the columns contain only integer values that match the primary keys in the referenced tables. If you want to display the actual position and department names, you must join the three tables, as in the following example:<\/p>\n\n\n\n
SELECT *\nFROM employees e INNER JOIN positions p\n  ON e.position_id = p.position_id\nINNER JOIN departments d\n  ON e.dept_id = d.dept_id;<\/pre><\/div>\n\n\n\n
The statement uses inner joins that are based on the position_id<\/code> and dept_id<\/code> columns. Notice that the statement also uses table aliases when referencing the column names.<\/p>\n\n\n\n
As in the previous SELECT<\/code> statement, this one has not been qualified in any way (other than the joins), so it will return all columns and all rows from all three tables. Because inner joins are used, each row returned by the statement links the employee to the correct position and department.<\/p>\n\n\n\n
In most cases, you\u2019ll want to refine the statement further, which we\u2019ll do as we progress through the examples. But first, let\u2019s look at how to return all the data from the employees<\/code> collection in MongoDB. For this, we can use a the find<\/code> method available to the collection object, as in the following example:<\/p>\n\n\n\n
db.employees.find();<\/pre><\/div>\n\n\n\n
The command returns all 10 of the documents in the employees<\/code> collection and returns them in their entirety. In this case, it is not necessary to link to another collection to retrieve additional information about each employee because the documents in this collection are complete in themselves. As with the preceding MySQL statement, we\u2019ll be refining this command as we progress through the examples in order to limit their results.<\/p>\n\n\n\n
Now let\u2019s return to our MySQL examples. This time around, we\u2019ll create a SELECT<\/code> statement that limits the columns we want to include in the results. The following example returns only the emp_id<\/code>, emp_name<\/code>, and job_rating<\/code> columns from the employees<\/code> table and the job_title<\/code> column from the positions<\/code> table:<\/p>\n\n\n\n
SELECT e.emp_id, e.emp_name, p.job_title, e.job_rating\nFROM employees e INNER JOIN positions p\n  ON e.position_id = p.position_id\nINNER JOIN departments d\n  ON e.dept_id = d.dept_id;<\/pre><\/div>\n\n\n\n
The statement limits the results to specific columns by listing them in the SELECT<\/code> clause. The statement still returns all rows in the table, but only the data in the specified columns.<\/p>\n\n\n\n
To achieve similar results in MongoDB, we can refine our find<\/code> command by specifying the target fields in the method\u2019s second argument, as in the following example:<\/p>\n\n\n\n
db.employees.find( { },\n  { \"emp_name\": 1, \"position.job_title\": 1, \"job_rating\": 1 }\n);<\/pre><\/div>\n\n\n\n
In this case, the method takes two arguments. The first argument, which is referred to as the query,<\/em> determines which documents to return. If the argument is an empty set of curly brackets, as in this case, all documents are returned.<\/p>\n\n\n\n
The second argument is referred to as the projection<\/em>. It determines which fields to return by listing the fields and assigning each one a value of 1<\/code>. The argument does not include the _id<\/code> field because it is returned by default. If you don\u2019t want to include the _id<\/code> field, you must explicitly exclude it in the argument by specifying 0<\/code> as the field\u2019s value, rather than 1<\/code>.<\/p>\n\n\n\n
Now let\u2019s refine our queries even further. First, we\u2019ll update the MySQL statement by adding a WHERE<\/code> clause that limits the returned rows to those with a dept_name<\/code> value of R&D<\/code>, as in the following example:<\/p>\n\n\n\n
SELECT e.emp_id, e.emp_name, p.job_title, e.job_rating\nFROM employees e INNER JOIN positions p\n  ON e.position_id = p.position_id\nINNER JOIN departments d\n  ON e.dept_id = d.dept_id\nWHERE d.dept_name = 'R&D';<\/pre><\/div>\n\n\n\n
The SELECT<\/code> statement should now return only four rows, with each row representing an employee in the R&D department.<\/p>\n\n\n\n
To achieve similar results in MongoDB, we can modify the find<\/code> command so the query argument specifies the position.dept_name<\/code> field and R&D<\/code> value in the first argument, as in the following example<\/p>\n\n\n\n
db.employees.find( \n  { \"position.dept_name\": \"R&D\" },\n  { \"emp_name\": 1, \"position.job_title\": 1, \"job_rating\": 1 }\n);<\/pre><\/div>\n\n\n\n
Now the command returns only the expected four documents, each with a dept_name<\/code> field value of R&D<\/code>.<\/p>\n\n\n\n
We can even sort our results in both MySQL and MongoDB. To do this in MySQL, we would use an ORDER<\/code> BY<\/code> clause. For example, the following SELECT<\/code> statement uses the clause to sort the results in descending order, based on the values in the job_rating<\/code> column:<\/p>\n\n\n\n
SELECT e.emp_id, e.emp_name, p.job_title, e.job_rating\nFROM employees e INNER JOIN positions p\n  ON e.position_id = p.position_id\nINNER JOIN departments d\n  ON e.dept_id = d.dept_id\nWHERE d.dept_name = 'R&D'\nORDER BY e.job_rating DESC;<\/pre><\/div>\n\n\n\n
To sort the documents in MongoDB data, we can add the sort<\/code> method to our command, basing the sort operation on the job_rating<\/code> field, as in the following example:<\/p>\n\n\n\n
db.employees.find( \n  { \"position.dept_name\": \"R&D\" },\n  { \"emp_name\": 1, \"position.job_title\": 1, \"job_rating\": 1 }\n).sort( { \"job_rating\": -1 } );<\/pre><\/div>\n\n\n\n
When you include the sort<\/code> method, you need to specify a sort order as the field value, using 1<\/code> for ascending order and -1<\/code> for descending order.<\/p>\n\n\n\n
Aggregating data in your database<\/h2>\n\n\n\n
Another operation we can carry out in both MySQL and MongoDB is to group and aggregate data. In MySQL, we can achieve this by adding a GROUP<\/code> BY<\/code> clause to the SELECT<\/code> statement and using an aggregate function in the SELECT<\/code> clause. For example, the following SELECT<\/code> statement groups the data by the dept_name<\/code> column and then calculates the average job_rating<\/code> value for each group:<\/p>\n\n\n\n
SELECT d.dept_name, ROUND(AVG(e.job_rating), 1) AS avg_rating\nFROM employees e INNER JOIN departments d\n  ON e.dept_id = d.dept_id\nWHERE d.dept_name = 'R&D' OR d.dept_name = 'IT'\nGROUP BY d.dept_name;<\/pre><\/div>\n\n\n\n
Notice that the WHERE<\/code> clause includes two search conditions. Together, they specify that the results should include only those rows whose dept_name<\/code> value is R&D<\/code> or IT<\/code>. In addition, the statement uses the AVG<\/code> function in the SELECT<\/code> clause to find the average job_rating<\/code> values for R&D<\/code> and IT<\/code>. It also uses the ROUND<\/code> function to round that average to one decimal point.<\/p>\n\n\n\n
To achieve similar results in MongoDB, we need to use the aggregate<\/code> method rather than the find<\/code> method. The method\u2019s arguments should include the aggregation stages necessary to filter and group the data, as in the following example:<\/p>\n\n\n\n
db.employees.aggregate([\n  { $match: { $or: [\n      { \"position.dept_name\": \"R&D\" },\n      { \"position.dept_name\": \"IT\" } ] } },\n  { $group: {\n      \"_id\": \"$position.dept_name\",\n      \"avg_rating\": { $avg: \"$job_rating\" } } },\n  { $project: {\n      \"_id\": 0,\n      \"dept_name\": \"$_id\",\n      \"avg_rating\": { $round: [\"$avg_rating\", 1], } } }\n]);<\/pre><\/div>\n\n\n\n
The aggregate<\/code> method defines three stages:<\/p>\n\n\n
\n
    \n
  •  The $match<\/code> stage limits to the returned documents to those whose position.dept_name<\/code> value is either R&D<\/code> or IT<\/code>. <\/li>\n\n\n\n
  •  The $group<\/code> stage groups those documents by the dept_name<\/code> field and finds the average job_rating<\/code> value for each group. <\/li>\n\n\n\n
  •  The $project<\/code> stage rounds the avg_rating<\/code> field returned by the $group<\/code> stage and renames the _id<\/code> field, which is based on the dept_name<\/code> field. <\/li>\n<\/ul>\n<\/div>\n\n\n
    The command returns two documents, each providing the avg_rating<\/code> value for one of the two departments.<\/p>\n\n\n\n
    Updating data in your database<\/h2>\n\n\n\n
    In addition to querying data, it\u2019s also important to be able to update the data in your database, something you can easily achieve with both SQL and MQL. For example, the following SQL statement updates the employees<\/code> table in MySQL by changing the hire_date<\/code> value to 2022-09-21<\/code>:<\/p>\n\n\n\n
    UPDATE employees\nSET hire_date = '2022-09-21'\nWHERE emp_id = 1006;<\/pre><\/div>\n\n\n\n
    Because the WHERE<\/code> clause is included, only the row with an emp_id<\/code> value of 1006<\/code> will be updated. Without the WHERE<\/code> clause, the statement would update every row in the table. The WHERE<\/code> clause ensures that you\u2019re limiting your changes to those rows that satisfy the search condition.<\/p>\n\n\n\n
    You can achieve similar results in MongoDB by running the following updateOne<\/code> command:<\/p>\n\n\n\n
    db.employees.updateOne(\n  { \"_id\" : ObjectId(\"67deec015c3b5e46a254214c\" ) },\n  { $set: { \"hire_date\": new Date(\"2022-09-21\") } }\n);<\/pre><\/div>\n\n\n\n
    In this case, the updateOne<\/code> method takes two arguments. The first one specifies which document to update based on the _id<\/code> value, and the second one uses the $set<\/code> operator to set the hire_date<\/code> value to the new date.<\/p>\n\n\n\n
    The _id<\/code> value I\u2019ve used here is the one that MongoDB generated automatically when I added the document to the collection. If you want to run this command, you will need to first retrieve that value on your system. Because the _id<\/code> value is always unique, only one document can be updated, which is why the updateOne<\/code> method is used.<\/p>\n\n\n\n
    At times, you might want to update multiple records in your database. For example, the following SELECT<\/code> statement updates several rows in the employees<\/code> table:<\/p>\n\n\n\n
    UPDATE employees e\nINNER JOIN departments d\n  ON e.dept_id = d.dept_id\nSET job_rating = job_rating + 1\nWHERE d.dept_name = \"Marketing\"\n  AND e.current_emp = 1;<\/pre><\/div>\n\n\n\n
    The WHERE<\/code> clause includes two search conditions that must both evaluate to true for a row to be returned. The first one specifies that the dept_name<\/code> value must be Marketing<\/code>, and the second condition specifies that the current_emp<\/code> column should be 1<\/code>. The statement joins the employees<\/code> table to the departments<\/code> table, making it possible to reference the dept_name<\/code> column in the WHERE<\/code> clause.<\/p>\n\n\n\n
    If both conditions are met, MySQL will increase the job_rating<\/code> value by 1<\/code> for each row returned by the WHERE<\/code> clause. To achieve the same logic in MongoDB, you need to use an updateMany<\/code> command, as in the following example:<\/p>\n\n\n\n
    db.employees.updateMany(\n  { \"position.dept_name\": \"Marketing\",\n    \"current_emp\": true },\n  { $inc: { \"job_rating\": 1 } }\n);<\/pre><\/div>\n\n\n\n
    The first argument for the updateMany<\/code> method specifies that the position.dept_name<\/code> field value should equal Marketing<\/code> and that the current_emp<\/code> value should be true<\/code>. The second argument uses the $inc<\/code> operator to increment the job_rating<\/code> value by 1<\/code>, but only for those documents that meet the two conditions specified in the first argument.<\/p>\n\n\n\n
    Deleting data in your database<\/h2>\n\n\n\n
    Deleting documents in MySQL and MongoDB works much like updating documents. For example, you can use the following DELETE<\/code> statement in MySQL to delete the row with an emp_id<\/code> value of 1009<\/code>:<\/p>\n\n\n\n
    DELETE FROM employees\nWHERE emp_id = 1009;<\/pre><\/div>\n\n\n\n
    To delete the same document in MongoDB (the document with the emp_name<\/code> value of Jessie<\/code>), you would use a deleteOne<\/code> command and specify the document\u2019s _id<\/code> value, as shown in the following example:<\/p>\n\n\n\n
    db.employees.deleteOne( { \"_id\": ObjectId(\"67e067ce114490669fae5cab\") } );<\/pre><\/div>\n\n\n\n
    Once again, the _id<\/code> value I\u2019ve used here is the one that MongoDB generated automatically. If you want to run this command, you will need to first retrieve that value on your system.<\/p>\n\n\n\n
    Now let\u2019s look how to delete multiple records, without deleting all records. To accomplish this, you must ensure that your query targets only the records you want to remove and nothing more. For example, you might decide to remove all records for employees who are part of the Marketing department, but leave all other employee records in place. You can achieve this in MySQL by running the following DELETE<\/code> statement:<\/p>\n\n\n\n
    DELETE e\nFROM employees e INNER JOIN departments d\n  ON e.dept_id = d.dept_id\nWHERE d.dept_name = \"Marketing\";<\/pre><\/div>\n\n\n\n
    Because the dept_name<\/code> column is not in the employees<\/code> table, you need to join the table to the departments<\/code> table so you can reference the dept_name<\/code> column in the WHERE<\/code> clause. When joining tables in this way, you must reference the target table in both the DELETE<\/code> clause and the FROM<\/code> clause to ensure that there is no ambiguity about which rows should be deleted. If you\u2019re using table aliases, as I\u2019ve done here, you should use the alias in the DELETE<\/code> clause.<\/p>\n\n\n\n
    In MongoDB, we can achieve similar results by using the deleteMany<\/code> method, as in the following example:<\/p>\n\n\n\n
    db.employees.deleteMany( { \"position.dept_name\": \"Marketing\" } );<\/pre><\/div>\n\n\n\n
    In this case, the deleteMany<\/code> method includes only one argument, which specifies that the position.dept_name<\/code> value must equal Marketing<\/code>. Only those documents that meet this condition will be deleted.<\/p>\n\n\n\n
    Altering structures in your database<\/h2>\n\n\n\n
    I also want to show you a couple other differences between SQL and MQL, particularly when it comes to altering schema. As before, we\u2019ll start with MySQL. The following SQL statements add the username<\/code> column to the employees<\/code> table and then populate the column by concatenating the emp_name<\/code> and emp_id<\/code> fields:<\/p>\n\n\n\n
    ALTER TABLE employees\nADD COLUMN username VARCHAR(20) NOT NULL;\n\nUPDATE employees\nSET username = (CONCAT(emp_name, CONVERT(emp_id, CHAR)));<\/pre><\/div>\n\n\n\n
    To concatenate the two fields, the UPDATE<\/code> statement uses the CONCAT<\/code> operator, along with the CONVERT<\/code> operator to convert the emp_id<\/code> value to a string.<\/p>\n\n\n\n
    We can also update the structure of MongoDB documents by using an updateMany<\/code> command, as shown in the following example:<\/p>\n\n\n\n
    db.employees.updateMany( { },\n  [ { $set: { \"username\": { $concat: [ \"$emp_name\",\n    { $substr: [ { $toString: \"$_id\" }, 20, -1 ] } ] } } } ]\n);<\/pre><\/div>\n\n\n\n
    In this case, the updateMany<\/code> method takes two arguments. The first argument is an empty set of curly brackets, which means that all documents in the collection should be the updated. The second argument uses the $set<\/code> operator to set the value of the username<\/code> field. Because the field does not exist, MongoDB will create it automatically.<\/p>\n\n\n\n
    The second argument also includes the $concat<\/code> operator, which sets the field\u2019s value by concatenating the emp_name<\/code> field and the last four digits in the _id<\/code> field. To capture the four digits, the expression uses the $toString<\/code> operator to convert the _id<\/code> value to a string, and then uses the $substr<\/code> operator to return the last for digits.<\/p>\n\n\n\n
    The goal of all this was to create a username<\/code> value that, for our purposes here, is unique for each record. In a real-world setting, you\u2019ll likely take a more sophisticated approach to generating unique usernames for employees, whether working in MongoDB or MySQL. For this article, I wanted to keep things simple.<\/p>\n\n\n\n
    You can also create indexes in both MySQL and MongoDB. For example, you can use the following CREATE<\/code> INDEX<\/code> statement in MySQL to create a unique index named ix_username<\/code> on the username<\/code> column:<\/p>\n\n\n\n
    CREATE UNIQUE INDEX ix_username\nON employees(username);<\/pre><\/div>\n\n\n\n
    To create an index on the username<\/code> field in MongoDB, you can use the following createIndex<\/code> command:<\/p>\n\n\n\n
    db.employees.createIndex(\n { \"username\": 1 },\n { name: \"ix_username\", unique: true }\n);<\/pre><\/div>\n\n\n\n
    The createIndex<\/code> method takes two arguments. The first argument identifies the username<\/code> field as the field on which the index should be based. The argument also includes a value of 1<\/code>, which indicates that the index should be sorted in ascending order. The method\u2019s second argument names the index ix_username<\/code> and specifies that it should be a unique index.<\/p>\n\n\n\n
    You can also drop indexes in MySQL and MongoDB. To drop the ix_username<\/code> index in MySQL, you can run the following DROP<\/code> INDEX<\/code> statement:<\/p>\n\n\n\n
    DROP INDEX ix_username ON employees;<\/pre><\/div>\n\n\n\n
    In MongoDB, you can run a dropIndex<\/code> command to remove the index, as in the following example:<\/p>\n\n\n\n
    db.employees.dropIndex( \"ix_username\" );<\/pre><\/div>\n\n\n\n
    If you want to drop the column you just created in the MySQL database, you can again use the ALTER<\/code> TABLE<\/code> statement:<\/p>\n\n\n\n
    ALTER TABLE employees\nDROP COLUMN username;<\/pre><\/div>\n\n\n\n
    To drop the username<\/code> field in MongoDB, you can use an updateMany<\/code> command, like you did when adding the field, only this time, you should use the $unset<\/code> operator, as in the following example:<\/p>\n\n\n\n
    db.employees.updateMany( { },\n  { $unset: { \"username\": \"\" } }\n);<\/pre><\/div>\n\n\n\n
    The first argument in the updateMany<\/code> method is an empty set of curly brackets, indicating that all documents in the collection should be updated. The second argument uses the $unset<\/code> operator to remove the username<\/code> field, specifying an empty string for the field value.<\/p>\n\n\n\n
    Getting started with your move to MongoDB <\/h2>\n\n\n\n
    In this article, I\u2019ve tried to provide you with an overview of how SQL statements compare to MQL commands when carrying out similar operations. Although the examples I\u2019ve provided are fairly basic, they should help you conceptualize some of the differences between the two approaches and give you a better sense of how to carry out a task in MQL that you\u2019re familiar with SQL.<\/p>\n\n\n\n
    There is, of course, no substitute for digging into MongoDB on your own and learning how to perform different operations. At the same time, you should be careful not to get so locked into the SQL-MQL comparisons that you lose sight of the fact that a RDBMS and MongoDB are two very different approaches to managing and accessing data. However, the information in this article should at least provide you with one more tool for better understanding how MongoDB works so you can interact with it as comfortably as MySQL, PostgreSQL, Oracle, SQL Server, or whatever RDBMS you\u2019ve been using.<\/p>\n","protected":false},"excerpt":{"rendered":"
    People new to MongoDB often come to the platform with a background in relational database management systems (RDBMSs) such as MySQL or SQL Server. These platforms commonly use some form of the Structured Query Language (SQL) to manage and query the data within their databases. SQL was designed specifically to work with relational databases and……<\/p>\n","protected":false},"author":221841,"featured_media":106752,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,159161],"tags":[5618,159226],"coauthors":[6779],"class_list":["post-106750","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-mongodb","tag-mongodb","tag-mongodbseriesrobertsheldon"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106750","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=106750"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106750\/revisions"}],"predecessor-version":[{"id":106754,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106750\/revisions\/106754"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/106752"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=106750"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=106750"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=106750"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=106750"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}