Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Blog
    • Community
    • Testimonials
    • Whitepapers
    • Reports
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Careers
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • Download
  • My 3T
search

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

Posted on: 08/09/2021 (last updated: 22/09/2021) by Dj Walker-Morgan

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?

Welcome to the latest Studio 3T Ask Manatees Anything – #Studio3T_AMA. Want to know how to use Studio 3T to make your MongoDB life better? Just tweet your question using the #Studio3T_AMA hashtag or email your query to [email protected].

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.


How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

Dj Walker-Morgan

Dj has been around since Commodore had Pets and Apples grew everywhere. With a background in Unix and development, he's been around the technology business writing code or writing content ever since.

Related articles

  • SQL Migration: MongoDB and SQL, Coexistence Made Easy
  • Moving from SQL to MongoDB? Studio 3T’s SQL Migration feature makes it simple
  • Lesson 6, Exercise 1: Using the SQL Query tool to run SQL statements
  • SQL Migration: MongoDB and SQL, Coexistence Made Easy
  • MongoDB and SQL Server: Communicating via REST Service

Tags

2022 academy aggregation AMA atlas Certification christmas community connections culture date tags events export features hackolade import intellishell In Use JSON knowledge base migration modelling mongodb mongoodb mongosh My 3T productivity query regex releases schema security SQL Studio 3T tasks time series tips updates webinar windows

Browse by MongoDB topic

  • Connecting to MongoDB
  • Database Administration & Security
  • Getting Started with MongoDB
  • Getting Started with Studio 3T
  • Import/Export
  • Job Automation & Scheduling
  • MongoDB Aggregation Framework
  • MongoDB/Studio 3T Workshops
  • Performance
  • Query & CRUD Operations
  • Reference
  • Schema
  • Studio 3T Licensing
  • Support and other resources
  • Working with MongoDB & SQL
  • Working with MongoDB Atlas

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2023 3T Software Labs Ltd. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum

We value your privacy

With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data.

By clicking “Accept all”, you consent to the storage of cookies and the processing of personal data for these purposes, including any transfers to third countries. By clicking on “Decline all”, you do not give your consent and we will only store cookies that are necessary for our website. You can customize the cookies we store on your device or change your selection at any time - thus also revoking your consent with effect for the future - under “Manage Cookies”, or “Cookie Settings” at the bottom of the page. You can find further information in our Privacy Policy.
Accept all
Decline all
Manage cookies
✕

Privacy Preference Center

With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data. Please choose for which purposes you wish to give us your consent and store your preferences by clicking on “Accept selected”. You can find further information in our Privacy Policy.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Google Analytics

Google Ads

Bing Ads

Facebook

LinkedIn

Quora

Hotjar

Reddit

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

HubSpot

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected