Severalnines Blog
The automation and management blog for open source databases

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

SQL MongoDB
Table
Row
Column
Table joins
Collection
BSON document
Field
$lookup

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.

Severalnines
 
Become a MongoDB DBA - Bringing MongoDB to Production
Learn about what you need to know to deploy, monitor, manage and scale MongoDB

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.

SQL schema statement MongoDB Schema Statements
CREATE TABLE students (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name Varchar (30),
  age Number,
  score Float
)

To insert a document to the database

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

We can define a schema design using some modules such as mongoose and define the fields like an object rather than inserting a document directly to show the correlation. The primary filed id will be generated automatically during the insertion of a document.

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

Inserting a new document to create the collection

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

Using the ADD statement to add a new column to the existing table.

ALTER TABLE students ADD units 10

The structure of collection documents is not well defined and therefore update documents at document level using the updateMany()

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

To drop a column (units)

ALTER TABLE students DROP COLUMN units

To drop a field (units)

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

To drop a table students

DROP TABLE students

To drop collection students

db.students.drop()
SQL Select statement MongoDB find Statements

Select all rows

SELECT * FROM students

Select all documents

db.students.find()

To return specific columns only.

SELECT name, grade FROM students

To return specific fields only. By default, the _id field is returned unless specified otherwise in the projection process.

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

Setting the _id: 0 mean only the document returned will have the name and the grade object values only.

To select specific row(s) with some matching column value.

SELECT * FROM students WHERE grade = “A”

To select specific document(s) with some matching field value.

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

Selecting rows with a column whose values has some characters as the supplied criteria value

SELECT * FROM students WHERE name like  “James%”

Selecting documents with a field whose values has some characters as the supplied criteria value

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

To return the rows in an ascending order using the primary key.

SELECT * FROM students ORDER BY id ASC

To return the documents in an ascending using the primary key

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

To group returned rows in accordance to some column (grade)

SELECT DISTINCT (grade) FROM students

To group returned documents in accordance to some field (grade)

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

Limiting the number of returned rows and skipping some

SELECT * FROM students LIMIT 1 SKIP 4

Limiting the number of returned documents and skipping rows

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

An essentials option is to know how our query is executed hence use the explain method.

EXPLAIN SELECT *  FROM students WHERE grade “A”
db.students.find({grade: “A”}).explain()
SQL Update statement MongoDB update Statements

Update the grade column for students whose age is equal to 15 or greater

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

Here we use some operators such as $gt, $lt and $lte.

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

Incrementing some column value

UPDATE students SET age  = age + 1 WHERE age < 15
db.students.updateMany({
age: {$lt:15}},{$inc: {age: 1}})
SQL delete statement MongoDB remove Statements

To delete all rows

DELETE FROM students

To delete all documents.

db.students.remove({})

To delete a specific row where some column has a specific value.

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.