MongoDB: How to translate SQL to MongoDB commands

Introduction

Hi everybody

If you work with databases, you will be familiar with the two main philosophies used in this domain: The SQL philosophy (RDBMS) and, of course, NoSQL databases.
Some examples of SQL databases are Oracle and Postgres, while Nosql databases are MongoDB and Redis.
How do these two philosophies work?
Is it possible to switch from one language system to the other,for example for a migration?

What are the main corresponding commands form SQL to MongoDB commands ?
Let’s find out!

MongoDB vs SQL concept

MongoDB and traditional relational database share a lot of points and similar keywords, but there is also some differences

Below you have the mapping data model between MongoDB and RDBMS

For that post we will use Postgresql as the SQL database( but for Oracle and other RDBMS it is quite the same philosophy)

We see a lot of similar terms like database or Index but also more specific terms for example:

• Table on SQL equals to Collection
• Row on SQL equals to Document
• Columns on SQL equals to Field

Below the difference between MongoDB data structure and PostgreSQL

• PostgreSQL output

• MongoDB output

We find the unique id and fields in both cases but the structure is different:

• For SQL you have rows and tables whereas in MongoDB it is stored as a field/value pair DOCUMENT.

In both cases all data are here so we will be able to make matching queries.

Note that sometimes even the same terms ( or keywords ) are not built in the same way from RDBMS to MongoDB.

Commands Matching and translation

Now let’s see how the classic commands are used from SQL to MongoDB and how can we make queries

INSERT

• Syntax for PostgreSQL

In PostgreSQL you insert your data by using the INSERT INTO command to add rows in your table

postgres=# insert into SELECAO_CRACKS (selecao_cracks_id,last_name,first_name,nickname,goals) VALUES (8,'ZICO','adriano','el imperator',35) ;

• Syntax for MongoDB

For MongoDB the corresponding command is Insert,insertOne ( for one input) or insertMany (for many inputs )

selecao_cracks> db.selecao_cracks.insertMany([ { "_id": 8, "FirstName": "Adriano", "LastName": "ZICO", "Nickname": 'el imperator', "goals": 35 }])

We see that the insertion works by checking the output, you can perform a find command with the corresponding fields to verify that all is fine.

• Regarding ID field

Note that on MongoDB the primary key is automatically generated as an ObjectID in the case you don’t define it

SELECT

To perform your query on SQL you use the SELECT command,The MongoDB equivalent of SELECT is the find() method.

Example for freshly inserted info:

• Syntax for PostgreSQL

postgres=# select * from SELECAO_CRACKS where last_name='ZICO';

• Syntax for MongDB

selecao_cracks> db.selecao_cracks.find({'LastName':'ZICO'})

Below are a few examples of how the SELECT (for PostgreSQL ) and FIND (for MongoDB ) commands can be used in conjunction with arguments to obtain a precise result.

BETWEEN

In SQL you can query to have a specific range of values using the BETWEEN command

For the below example,we want to sort all the players with a goal number between 18 and 38:

• Syntax for PostgreSQL

postgres=# select last_name,first_name,goals from SELECAO_CRACKS where goals between '18'and '38' ;

• Syntax for MongoDB

selecao_cracks> db.selecao_cracks.find( { goals: { $gte: 18 , $lte:38} } )

For MongoDB we use the comparison operators to display a specific range:

• $lt for less than
• $lte for less or equal than
• $gt for greater than
• $gte for greater or equal

ORDERBY

In SQL, we can specify an order for our results by using ORDER BY.

For example, in the following SQL statement, we’re selecting every Ordered by goals from the best striker to the less performant in the scoring exercise 🙂

• Syntax for PostgreSQL

postgres=# select * from SELECAO_CRACKS ORDER BY goals DESC;

Note: Using DESC will give you the output by descending order

Here it is we how the best scorers in that order ( everybody know that Denilson and Robinho would rather make nutmegs than scoring, that’s also my case I must confess )

• Syntax for MongoDB

selecao_cracks> db.selecao_cracks.find( { goals: { $gte: 18 , $lte:38} } ).sort({goals: -1})

For MongoDB you can order your output by appending the sort() method to our query ( ascending or descending order is performed by using -1 or 1 )

LIMIT

The LIMIT command is the same for SQL and MongoDB lets see for example who are the 3 best strikers

• Syntax for PostgreSQL

postgres=# select * from SELECAO_CRACKS ORDER BY goals DESC LIMIT 3 ;

Here we have the top 3 scorers (I know what football fans think, but Diego is here for a specific purpose 😉 )

• Syntax for MongoDB

selecao_cracks> db.selecao_cracks.find( { goals: { $gte: 18 , $lte:45} } ).sort({goals: -1}).limit(3)

With this command you will limit the output to only 3 results

UPDATE

Regarding the update command there are multiple options to use with PostgreSQL UPDATE command and for MongoDB using the updateOne() and updateMany() method

For the football expert ( not the db experts ) you have probably noticed some mistakes regarding Name of some players, you found it??

Answer:

It’s of course ZICO, altought he is also a selecao crack he is not the one called el imperator.

Let’s update the last name of Adriano:

• Syntax for PostgreSQL

postgres=# select * from SELECAO_CRACKS where SELECAO_CRACKS_ID='8';

Use UPDATE with SET to update the last_name value against another column, for example the id (it is safer to use the id/the primary key to avoid multiple updates or human error)

Syntax for MongoDB

selecao_cracks> db.selecao_cracks.updateOne( { LastName: 'ZICO' }, { $set: { LastName: "RIBEIRO" } });

The output show us how many entries were updated and the modifiedcount number gives us a confirmation that the update worked.

If you want to update a document that may not already exist, you can optionally include it by setting the upsert option to true.

Note:

With updateOne(), MongoDB performs individual updates on records serially. The updateOne() method is also atomic, which means that there won’t be any partial updates written to the database.

For multiple updates we will use the UpdateMany command.

Lets’give an example:

We want to update the goal number of every players by 10 goals up:

selecao_cracks> db.selecao_cracks.updateMany( {}, { $inc : { goals: 10 } });

See now Adriano got 45 goals:

DELETE

To delete a record in SQL, we use DELETE

As many of you noticed, MARADONA is not a Brazilian legend but Argentinian , we will remove it from the selecao players

• PostgreSQL syntax

DELETE FROM DATABASENAME WHERE id = 'ID of ROW to be removed';

postgres=# delete from SELECAO_CRACKS where selecao_cracks_id=3 ;

Here we used the id corresponding to MARADONA to perform the DELETION of the row.

• Delete Multiple Records

To delete multiple records in SQL, we still use a DELETE statement along with a WHERE clause.

For example we can delete from SELECAO_CRACKS Pele and Robinho by using the nickname column

DELETE FROM SELECAO_CRACKS WHERE nickname IN ('Pele', 'Robinho')

• MongoDB syntax

• To remove document

For a single document deletion in MongoDB, we use the deleteOne() command

To delete multiple documents in MongoDB, we use the deleteMany() method along with the $in operator:

• Update or remove a field:

To remove a field use the $unset command combined with the updateOne or Updatemany command

selecao_cracks> db.selecao_cracks.updateMany({}, [ {$unset:"FIELD_TO_DELETE"} ])

SQL JOINs in MongoDB

It will be seen in an incoming dedicated post.

Migration tools

There are existing tools such as Mongo Relational-migrator which will help you to convert your SQL data to Mongo format, and some other tools but we will see that on another blog.

Conclusion

Now you know what are the differences and the similarities between PostgreSQL and MongoDB commands.

You see how to adapt your queries from one to the other tool.

Of course you can find specific tools to perform those translations or migrations, but aim is to know how queries are structured for each database type.

There is a lot more to say about it but it will be maybe for a next time.

Feel free to visit my other blogs and also dbi bloggers !