MongoDB and MySQL equivalent queries

Hello. In this article, we are going to show you some MongoDB and MySQL equivalent queries. This will help you greatly if you want to convert a MySQL project into MongoDB or vice-versa.

Video tutorial:

Introduction

First, let’s give a small introduction to both of these databases.

MongoDB

MongoDB is schema-less database architecture. Means that the schema or structure of the database does not needs to be defined. Schema will automatically gets created as data comes in. It is used where data needs to be loosely-coupled.

MySQL

MySQL is a structured query language. The structure of the database and its tables needs to be defined before creating them. It is used where data needs to be tightly-coupled.

1. Creating collections/tables

MongoDB

As mentioned above, MongoDB collections does not needs to be created. They will be created automatically once a document is inserted in it.

MySQL

To create a table in MySQL database, you can run the following query:

CREATE TABLE IF NOT EXISTS users(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    age INTEGER DEFAULT 0
);

This will create a table named “users” having 3 columns “id”, “name” and “age”. ID will be a unique key that will automatically be incremented once a new row is inserted. While “name” will be a text string field and “age” will be an integer number.

2. Inserting documents/rows

MongoDB

To insert a document in a MongoDB collection, you can run the following query:

db.users.insertOne({
    name: "Adnan",
    age: 30
});

This will insert a new document in “users” collection. It will automatically assign unique ObjectId to it named “_id”.

{
	"_id": ObjectId("6474680ef3c486d92597e787"),
	"name": "Adnan",
	"age": 30
}

To insert a row in MySQL table, you would do:

INSERT INTO users(name, age) VALUES ("Adnan", 30);

3. Fetching data

MongoDB

To fetch multiple records from MongoDB collection, you can run the following query:

db.users.find({
    name: "Adnan"
}).toArray();

This will return all the documents where “name” is “Adnan”.

MySQL

In MySQL, you can run the following SQL query:

SELECT * FROM users WHERE name = "Adnan";

3.1 AND clause

You can use $and operator in MongoDB to fetch data where all conditions must met.

db.users.find({
    $and: [
        {
            name: "Adnan"
        },
        {
            age: 30
        }
    ]
}).toArray();

This will return all the users whose name is “Adnan” and their age is 30.

Same filter can be applied on MySQL using the following query:

SELECT * FROM users WHERE name = "Adnan" AND age = 30;

3.2 OR clause

You can use $or operator in MongoDB to fetch data where any of the condition met.

db.users.find({
    $or: [
        {
            name: "Adnan"
        },
        {
            age: 30
        }
    ]
}).toArray();

This will return all the users whose name is “Adnan” or if their age is 30.

In MySQL, we would apply the above filter like this:

SELECT * FROM users WHERE name = "Adnan" OR age = 30;

3.3 Limiting, sorting and skipping data

To limit the number of records to fetch, order them by their name in ascending order and skip 1 document, in MongoDB you would do:

db.users.find({
        age: 30
    })
    .sort({
        name: -1
    })
    .skip(1)
    .limit(1)
    .toArray()

This will sort the users documents by name in descending order, skip 1 record and return only 1 record.

Similar query can be run on MySQL in the following way:

SELECT * FROM users WHERE age = 30 ORDER BY id DESC LIMIT 1, 1

LIMIT {skip}, {limit} this is the syntax of LIMIT command in SQL.

3.4 Less or greater than

In MongoDB, you can use $lt (less than) and $gt (greater than) operators like this:

db.users.find({
    age: {
        $lt: 30
    }
}).toArray()

This will return the users whose age is less than 30. Following query will return the users whose age is greater than 30:

db.users.find({
    age: {
        $gt: 30
    }
}).toArray()

You can also use $lte and $gte operators for “less than and equal to” and “greater than and equal to” conditions respectively.

Above are the MongoDB queries, following are their equivalent MySQL queries:

/* less than */
SELECT * FROM users WHERE age < 30;

/* less than and equal to */
SELECT * FROM users WHERE age <= 30;

/* greater than */
SELECT * FROM users WHERE age > 30;

/* greater than and equal to */
SELECT * FROM users WHERE age >= 30;

4. Updating data

To update a document in MongoDB collection, you would do the following:

db.users.updateMany({
    name: "Adnan"
}, {
    $set: {
        age: 31
    }
})

This will set the age to 31 of all users having name “Adnan”. If you want to update only one user then you can use updateOne() function instead.

In MySQL, you can do:

UPDATE users SET age = 31 WHERE name = "Adnan" LIMIT 1

4.1 Incrementing/decrementing values

To increment the value, in MongoDB you can do:

db.users.updateOne({
    name: "Adnan"
}, {
    $inc: {
        age: 3
    }
})

This will increment the value of age by 3 where name is “Adnan”. Same thing can be done for decrementing, you can just set the value in negative.

db.users.updateOne({
    name: "Adnan"
}, {
    $inc: {
        age: -3
    }
})

It’s equivalent MySQL query would be:

UPDATE users SET age = age + 3 WHERE name = "Adnan"

5. Delete data

To delete a document from MongoDB collection, you can run the following query:

db.users.deleteOne({
    name: "Adnan"
})

This will delete one document from users collection whose name is “Adnan”. To delete multiple, you can use deleteMany() function instead.

In MySQL, you can do:

DELETE FROM users WHERE name = "Adnan" LIMIT 1

6. Relationships

MongoDB

MongoDB is not a relational database. Data saved in one collection is not dependent on another collection’s data.

For example, if you want to save job history of each user. You do not have to create a separate collection for that. You can simply push a new job in document’s array.

db.users.findOneAndUpdate({
    name: "Adnan"
}, {
    $push: {
        "jobs": {
            _id: ObjectId(),
            title: "Developer",
            company: "adnan-tech.com",
            period: "3 years"
        }
    }
})

This will create an array “jobs” if not already created and insert a new element in that array.

{
	"_id" : ObjectId("64748227f3c486d92597e78a"),
	"name" : "Adnan",
	"age" : 30,
	"jobs" : [
		{
			"_id" : ObjectId("647490a4f3c486d92597e78e"),
			"title" : "Developer",
			"company" : "adnan-tech.com",
			"period" : "3 years"
		}
	]
}

MySQL

Whereas, MySQL is a relational database. Data saved in one table might be dependent on another table’s data.

If you want to achieve the above in MySQL, you would have to create a separate table for that and create user_id as foreign key and reference it to your “users” table.

CREATE TABLE IF NOT EXISTS jobs(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title TEXT NOT NULL,
    company TEXT NOT NULL,
    period TEXT NOT NULL,
    user_id INTEGER NOT NULL,

    CONSTRAINT fk_user_id_jobs FOREIGN KEY (user_id) REFERENCES users(id)
)

After that, you can insert a new job of that user using the following query:

INSERT INTO jobs (title, company, period, user_id) VALUES ("Developer", "adnan-tech.com", "3 years", 1)

This will insert a new row in “jobs” table and link it with “users” table using its foreign ID “user_id”.

To fetch the data both from users and jobs table, you have to perform a join operation.

SELECT * FROM users INNER JOIN jobs ON users.id = jobs.user_id

This will return all the users along with their jobs.

So you have learned to create many MongoDB queries to their equivalent MySQL queries and vice-versa. If you have any question related to this, feel free to comment it in the section below. You can also check our more tutorials on MongoDB to learn more.