How does Studio 3T’s SQL Query work? #Studio3T_AMA

Q: Studio 3T’s SQL Query looks very clever, but how does it actually work? Do I need Studio 3T everytime I want to make an SQL Query?

A: One of the great things about using SQL to query MongoDB in Studio 3T is that it isn’t a black box. You can see what goes in – an SQL query. And you can see what comes out – a MongoDB query.

That’s probably the best part of the SQL Query in Studio 3T. Rather than just run your SQL query somewhere and present the results, Studio 3T shows you what your SQL looks like as MongoDB Query Language. And it can prepare that query in a number of different languages. That lets you hone your query in Studio 3T and incorporate the results straight into your application.

In between those two points is where Studio 3T does the magic. It parses the SQL query and translates supported SQL expressions into their MongoDB’s MQL equivalents. That includes working out what field names in SQL mean in a MongoDB context. Let’s look at some examples of that process in action, starting with…

A Simple Search

So say we are looking at a SQL statement like:

SELECT * FROM customers;

Assuming we have a customers collection, Studio 3T will translate this into:

db.getCollection("customers").find({});

Why? Because SELECT * means return all the columns in SQL, with MongoDB a .find() with no project will return all the fields. FROM customers in SQL would mean from the customers table, but MongoDB has collections, not tables. The SQL Query engine in Studio 3T puts that all together. It comes out as “take the collection customer, run a find against it (to return all the fields)”. That’s the simplest case for an an SQL Query. Now take this SQL query:

SELECT title, first, last FROM customers;

It’s asking for specific fields from the table in SQL. In MongoDB, you extract specific fields using the projection option in .find(), so the SQL Query engine will translate that to:

db.getCollection("customers").find(
    { 
        "prio_support" : true
    }, 
    { 
        "title" : "$title", 
        "first" : "$first", 
        "last" : "$last", 
        "_id" : NumberInt(0)
    }
);


Now it is projecting out our selected fields (and removing the MongoDB _id field). For example, a field called "title" will be created using the value of the "$title" field in the data. Let’s go a little deeper and add a query:

SELECT title, first, last FROM customers WHERE prio_support=true;

The WHERE clause in the SQL is translated by the Studio 3T into a MongoDB query. The empty {} filter will be filled in to give query code that looks like this:

db.getCollection("customers").find(
    { 
        "prio_support" : true
    }, 
    { 
        "title" : "$title", 
        "first" : "$first", 
        "last" : "$last", 
        "_id" : NumberInt(0)
    }
).sort(
    { 
        "last" : NumberInt(1)
    }
);


Let’s add a bit of sorting to our SQL query:

SELECT title, first, last FROM customers WHERE prio_support=true ORDER BY last ASC;

Studio 3T sees the ORDER BY clause and translates it into MongoDB query code. Specifically, following the .find with a .sort call.

db.getCollection("customers").find(
    { 
        "prio_support" : true
    }, 
    { 
        "title" : "$title", 
        "first" : "$first", 
        "last" : "$last", 
        "_id" : NumberInt(0)
    }
).sort(
    { 
        "last" : NumberInt(1)
    }
);

Small Change in SQL Query, Big Change in MQL

Now, we’re going to add one common SQL command and see how Studio 3T handles it. We are going to ask it to only return DISTINCT records from our query:

SELECT DISTINCT title, first, last FROM customers WHERE prio_support=true ORDER BY last ASC;

Doesn’t look like a huge change, but there’s no native support for returning distinct records in MongoDB.  Studio 3T knows that and changes its approach to what it generates. Now it will use the Aggregation framework to create a multi stage pipeline:

db.getCollection("customers").aggregate(
    [
        { 
            "$match" : { 
                "prio_support" : true
            }
        }, 
        { 
            "$project" : { 
                "title" : "$title", 
                "first" : "$first", 
                "last" : "$last", 
                "_id" : NumberInt(0)
            }
        }, 
        { 
            "$group" : { 
                "_id" : null, 
                "distinct" : { 
                    "$addToSet" : "$$ROOT"
                }
            }
        }, 
        { 
            "$unwind" : { 
                "path" : "$distinct", 
                "preserveNullAndEmptyArrays" : false
            }
        }, 
        { 
            "$replaceRoot" : { 
                "newRoot" : "$distinct"
            }
        }, 
        { 
            "$sort" : { 
                "last" : NumberInt(1)
            }
        }
    ], 
    { 
        "allowDiskUse" : true
    }
);

Studio 3T creates an aggregation that matches the query part. It then extracts the data and then uses aggregation’s GROUP, UNWIND and REPLACE to create the distinct records. Finally it sorts them.

SQL Query knows from analyzing the query when it needs to stop using find and start creating aggregation pipelines. When you use an SQL JOIN, it knows that it should use aggregation’s $lookup stage to bring in a different collection’s data. 

For day to day SQL users, it’s a magical process which just works. 

Generating Code For Reuse From SQL

Under the covers, Studio 3T is turning SQL into MongoDB queries. Those generated queries can be reused from the Query Code tab. Both find and aggregate queries can be copied into Intellishell for reuse. Aggregate queries can be sent straight to the Aggregation Editor where they can also be saved or integrated into reporting or exporting. All versions of Studio 3T generate Mongo shell code which can run outside Studio 3T.

If you have Studio 3T with Query Code with multiple language support, you can generate code in a number of languages – Java, JavaScript/Node, C#, Python, Ruby or PHP. The code can run completely independently of Studio 3T – no libraries or add-ons required. You’ll need Studio 3T to translate SQL queries for use on MongoDB. The resulting code is independent of Studio 3T after translation. That said, you’ll find Studio 3T’s IntelliShell, Visual Query Builder and the Aggregation Editor invaluable in tuning and extending your newly translated queries.