Linking & Creating MongoDB Joins Using SQL: Part 1

SQL is the most preferred way of engaging relational databases as far as querying is concerned. It’s understood that users would have worked with relational databases such as MySQL and PostgreSQL that employ the SQL querying feature. Generally, SQL is easy to understand and therefore became widely used especially in relational databases.

However, SQL is quite complex when trying to engage a wide set of documents in a database. In a nutshell, it is not intended for document databases as it comes with a number of setbacks. For instance, you cannot query embedded array documents with ease or rather you will need to design a subprogram to iterate and filter returned data to give required results. Consequently, this will result in an increment of the execution duration. But having a good understanding in SQL will provide a better ground in interacting with MongoDB from some point rather than starting from scratch.

In this blog, we will be using the Studio 3T program to show the various SQL join queries and how you can redesign them into MongoDB queries to achieve better performance. The program can be downloaded from this link.

Connecting SQL to MongoDB

There are several drivers or rather interfaces through which you can use SQL to communicate with MongoDB, for example, ODBC. ODBC stands for Open Database Connectivity. This is simply an interface that allows applications to access data in database management systems using SQL as the standard process of accessing that data. It comes with an added interoperability advantage whereby a single application can access multiple database management systems.

In this blog, we will produce and test code from SQL and then optimize it via an aggregation editor to produce a MongoDB query.

Mapping Chart for SQL to MongoDB

Before we go into much details, we need to understand the basic relations between these 2 databases especially keywords in the querying concept.

Terminology and Concepts

The primary key in SQL defines a unique column that basically arranges the rows in order of record time. On the other hand, the primary key in MongoDB is a unique field for holding a document and ensuring that indexed fields do not store duplicate values.

Correlation Between SQL and MongoDB

Let’s say we have a student data and we want to record this data in both SQL database and MongoDB. We can define a simple student object as:

{
    name: ‘James Washington’,
    age: 15,
    grade: A,
    Score: 10.5
}

In creating an SQL table, we need to define the column names and data type whereas in MongoDB a collection will be automatically be created during the first insertion.

The table below will help us understand how some of the SQL statement can be written in MongoDB.

CREATE TABLE students (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name Varchar (30),
  age Number,
  score Float
)

INSERT INTO students(Name, age, grade,score) VALUES(“James Washington”, 15, “A”, 10.5)

{
  name: String,
  age Number,
  score: Number
}

db.students.insertOne({
    name: ‘James Washington’,
    age: 15,
    grade: ‘A’,
    score: 10.5
})

ALTER TABLE students ADD units 10

db.students.updateMany({}, {$set: {units: 10}})

ALTER TABLE students DROP COLUMN units

db.students.updateMany({}, {$unset: {units: “”}})

DROP TABLE students

db.students.drop()

SELECT * FROM students

db.students.find()

SELECT name, grade FROM students

db.students.find({}, {name: 1, grade: 1, _id: 0})

SELECT * FROM students WHERE grade = “A”

db.students.find({grade: “A”})

SELECT * FROM students WHERE name like  “James%”

db.students.find({grade: {$regex: /^James/}})

SELECT * FROM students ORDER BY id ASC

db.students.find().sort({$natural: 1})

SELECT DISTINCT (grade) FROM students

db.students.aggregate([
{$group: : {_id: “$grade”}}
]

SELECT * FROM students LIMIT 1 SKIP 4

db.students.find.limit(1).skip(4)

EXPLAIN SELECT *  FROM students WHERE grade “A”

db.students.find({grade: “A”}).explain()

UPDATE students SET grade  = “B” WHERE age >= 15

db.students.updateMany({age: {$gte: 15}}, {$set: {status: “B”}})

UPDATE students SET age  = age + 1 WHERE age < 15

db.students.updateMany({
age: {$lt:15}},{$inc: {age: 1}})

DELETE FROM students

db.students.remove({})

DELETE FROM students WHERE age = 15

db.students.remove({age: 15})

This sample mapping table will enable you to get a better understanding of what we are going to learn in our next topic.

SQL and Studio 3T

Studio 3T is one of the available programs that helps to connect SQL and MongoDB. It has a SQL Query feature for enhancing one to manipulate SQL. The query is interpreted into Mongo shell to produce a simple query code in MongoDB language equivalent. Besides doing simple queries, the Studio 3T application can now do joins.

For our sample data above, after connecting your database in Studio 3T, we can use the SQL window to find the document that matches our criteria i.e.:

SELECT * FROM students  WHERE name LIKE  'James%';

If you have a document with name field set to value James, then it will be returned. Likewise, if you click on the query code tab, you will be presented with a window with the equivalent MongoDB code. For the statement above, we will have:

db.getCollection("students").find(
    { 
        "name" : /^James.*$/i
    }
);

Summary

Sometimes you may want a quick way of interacting with MongoDB from the knowledge you have on SQL. We have learnt some basic code similarities between SQL and its equivalent in MongoDB. Further, some programs such as Studio 3T have well established tools for converting the SQL query into MongoDB equivalent language and fine-tune this query for better results. Well, to most of us, this will be a great tool for making our work easy and ensuring whatever code we have in the end is very optimal for the performance of our database. In Part 2 of this blog, we are going to learn about SQL INNER JOIN in MongoDB.