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

Studio 3T® Knowledge Base

  • Documentation
  • Tutorials
  • Workshops
Take the fastest route to learning MongoDB. Cover the basics in two hours with MongoDB 101, no registration required.
Start the free course

SQL Query

Posted on: 18/02/2020 (last updated: 17/10/2022) by Kathryn Vargas
tl;dr
Shortcuts

MongoDB? SQL? Why not both. SQL Query makes it possible to query MongoDB with SQL.

Download Studio 3T here, view the list of supported SQL expressions and joins, or jump ahead to complete your first SQL query exercise.

Open SQL Query – Shift + Ctrl + L (Shift + ⌘+ L)
Execute SQL statement at cursor – F5
Open existing SQL file – Ctrl + O (⌘+ O)
Save current SQL Query – Ctrl + S (⌘+ S)
Save SQL Query as – Shift + Ctrl + S (Shift + ⌘+ S)‘ id=”” class=”” style=”” ]

SQL Query is just one of four ways to build a MongoDB query in Studio 3T.

Basics

There are three ways to open SQL Query:

Query MongoDB with SQL, with SQL Query
  • Button – Click on the SQL button on the global toolbar
  • Right-click – Right-click on a collection and choose Open SQL
  • Hotkey – Use Shift + Ctrl + L (Shift + ⌘+ L)

SQL Query has two main areas: the Editor where queries are written, and the Result Tab where query results are displayed.

You can query MongoDB with SQL in the Editor area and view results in the Results Tab

The other tabs SQL Query, Query Code and Explain will be covered later in the tutorial.

Download Studio 3T here and write your own SQL queries as you go through the tutorial.

Execute SQL queries

Execute SQL statement at cursor

You can execute a SQL statement in three ways:

  • Button – Click on the Execute SQL statement marked by the cursor (play) button
  • Right-click – Place the cursor on the desired query, right-click, and choose Execute SQL statement at cursor
  • Hotkey – Press F5 to execute SQL statement at cursor

SQL query auto-completion

SQL Query also supports smart auto-completion. Similar to our IntelliShell, Studio 3T’s built-in mongo shell, SQL Editor detects and suggests standard SQL functions as well as fields, collections, and keyword names.

View the executed SQL query

View the executed query under the SQL Query ta

The SQL Query tab shows which SQL query was executed by the text cursor (or “at cursor”).

Why is this useful?

It shows the specific query that was actually run, important in the case of a SQL batch that contains multiple queries.

In the screenshot above, only the first query appears underneath the SQL Query tab because it is the SQL statement executed at cursor.

Edit results inline

Editing documents and data in the SQL tab in Studio 3T is based on a ‘search and edit’ paradigm.

That is, rather than having to search for the documents you want to edit and then issue separate UPDATE, DELETE or INSERT commands, you can simply edit the documents directly inline.

To edit a particular field or value in a document, simply double-click on it and a type-specific editor for that value will be activated.

Pressing ‘Enter’ will write the new value to the database, while pressing ‘Esc’ will return the previous value and exit the editor.

Read the full documentation on In-Place Editing here.

Read-only mode

If you would prefer that results were not directly editable, say perhaps in a production database, you can also enable read-only mode. This disables all editors and prevents documents from being edited inline.

Read-only mode can be activated at any time by clicking on the padlock icon in the Results Tab. Whether read-only mode is activated by default or not can be configured under Preferences.

Read about the two places where you can enable read-only mode in Studio 3T.

Open and save SQL queries

Open and save SQL queries easily in Studio 3T

In case you need to run the same SQL queries, you can conveniently open and save them as .sql files.

To save the current SQL query as a .sql file, click on the Save icon. Alternatively, click on the arrow next to it to find the Save As function.

To open existing .sql files, click on the folder icon.

Generate JavaScript, Java, Python, C# and PHP code from SQL queries

Generate instant code in 5 languages from SQL queries

Query Code is a feature available in Studio 3T that converts SQL queries into JavaScript (Node.js), Java (2.x and 3.x driver API), Python, C#, PHP, and the mongo shell language.

To see a SQL query’s equivalent code:

  1. Execute the SQL query
  2. Click on the Query Code tab
  3. Choose the target language

View the equivalent MongoDB query

After you query MongoDB with SQL, you can also view the equivalent MongoDB query under the Query Code

To view how a SQL query is written in MongoDB query syntax (which is a great learning tool):

  1. Click on the Query Code tab
  2. Choose mongo shell

Older versions of Studio 3T supported a MongoDB Query tab prior to Query Code. Download the latest Studio 3T version here.

Explain data for the query

Click on the Explain tab to reveal Visual Explain, which shows you a visual flowchart of how MongoDB ran your query with the option to view execution statistics – a helpful tool for tuning your query’s performance.

Show runtime statistics with full mode, with Visual Explain

Check out the full Visual Explain documentation and discover how the feature could help you optimize query performance.

Supported SQL expressions

In order to query MongoDB with SQL, Studio 3T supports many SQL-related expressions, functions, and methods to input a query. This tutorial uses the data set Customers to illustrate examples.

Download the Customers collection here and import it into Studio 3T to follow along with the tutorial.

SELECT *

Studio 3T automatically generates a basic SELECT * query by default. This retrieves all of the documents in a collection, similar to selecting all the rows of a table in an SQL database.

The SQL query

select *
from Customers;

shows all documents and fields within the Customers collection.

Return _id field to make result editable

When you have a query

SELECT foo FROM bar

which does not return _id, the result is not editable (no _id means no link to the original document).

To restore _id and thus make the result editable, we ask for this specific field:

SELECT _id, foo FROM bar

JSON objects in WHERE clauses

JSON can be used in SQL WHERE clauses in two ways:

WHERE JSON

or

WHERE identifier <SQL operator> JSON

JSON keys can be quoted or not. As strings, they can be quoted with single-quotes (”)  or double-quotes (“”), which means these two queries are the same:

SELECT * FROM [coordinates]
WHERE location = { "x" : 3 }

SELECT * FROM [coordinates]
WHERE { "location" : { "x" : 3 } }

You can also use a wide array of mongo data type constructors such as NumberInt, NumberLong, NumberDecimal, ObjectId, ISODate, Date, LUUID, CSUUID, JUUID, PYUUID, UUID, Timestamp, Symbol, DBRef, BinDate, and HexData.

Here are a few examples:

SELECT * FROM [binaries]
WHERE { "data" : BinData(3, '0x0') };

SELECT * FROM [table]
WHERE { 'date' : new Date(2019, 0, 2) }

The keyword new is optional in data type constructors.

This means we can also make use of any mongo operator.

SELECT * FROM [places]
WHERE {'$or' : [ { "item" : "foo" }, { 'item' : 'bar' } ] }

SELECT * FROM [words]
WHERE word = { $regex : "foo", "$options" : "i" }

SELECT DISTINCT

With Studio 3T 2019.1, SQL Query now also supports the SELECT DISTINCT clause.

SELECT DISTINCT eliminates any repeated documents from the output.

Consider the query:

SELECT first_name FROM customers

What it returns is a table of names where the names can obviously repeat:

Alice
Bob
Charlie
Bob
Charlie
...

But when you write the query with DISTINCT:

SELECT DISTINCT first_name FROM customers

What you get is a list of distinct names:

Alice
Bob
Charlie
...

So in a DISTINCT query output, each identical document appears only once.

Note that all the SELECT-ed fields are taken into account. The query

SELECT DISTINCT first_name, last_name FROM customers

returns

Alice, Allen
Bob, Brown
Charlie, Clark
Alice, Brown
Bob, Clark
Charlie, Allen
...

First names and last names can repeat, but their pairs will not.

Technical restrictions to SELECT DISTINCT

When you have a query with DISTINCT and ORDER BY, you can only sort by a selected (visible) field:

SELECT DISTINCT first_name FROM customers ORDER BY first_name
– OK

first_name which appears in ORDER BY must also appear in SELECT DISTINCT.

SELECT DISTINCT first_name FROM customers ORDER BY last_name
– FAILS

Compare the above to the regular:

SELECT first_name FROM customers ORDER BY last_name
– OK

Accessing embedded fields using dotted names

Some fields may be contained within an embedded document. You can access such fields using dot notation.

In the Customers collection, the field address has four embedded fields: street, city, state, and zip_code.

To find customers living in the city Berlin, run the following SQL query:

select *
from Customers
where address.city = 'Berlin';

The other embedded fields would be then referenced as address.street, address.state, and address.zip_code, respectively.

Be aware, however, of the differences when quoting names and string values.

Quoting names and string values

String values in a SQL query in Studio 3T can either be single- or double-quoted:

where address.city = 'Berlin'

or:

where address.city = "Berlin"

Names, including collection names and field names, both dotted and un-dotted, may be quoted using either back-ticks or square brackets.

For example, we could write:

where `address.city` = 'Berlin'

or:

where [address.city] = 'Berlin'

WHERE identifier <SQL operator> JSON

(Updated with Studio 3T 2019.1) When writing SQL queries in Studio 3T, the rules are simple:

  • Use single or double quotation marks for string values
  • Use back-ticks or square brackets for names

Querying arrays

When querying MongoDB arrays with SQL, it is important to wrap the collection name and the field name(s) in square brackets, otherwise the query will return a syntax error.

select *
from [Customers]
where [device.0.mobile] = 'foo';

Projection

To see only specific fields (for example, to display only the first name, last name, city, and number of transactions), run the query:

select first, last, address.city, transactions
from Customers;

Comparison operators

Studio 3T supports the standard SQL comparison operators:  =, <>, <, <=, >=, or >.

To find customers with fewer than twenty transactions, execute the SQL query:

select *
from Customers
where transactions < 20;

AND or OR

Expressions can be combined using AND or OR:

select *
from Customers
where transactions < 20
and address.city = 'Berlin'
or address.city = 'New York'
;

ORDER BY

Results can be ordered or sorted by specifying an ORDER BY clause.

By default, ORDER BY sorts results in ascending order, which is the number of transactions in this example:

select *
from Customers
order by transactions;

Add desc to order customers by number of transactions in descending order:

select *
from Customers
order by transactions desc;

Match Boolean values

Use the Boolean values true and false when querying Boolean fields in a MongoDB document, for example:

select *
from docsWithBoolsCollection
where myBoolField = true;

Some dialects of SQL use the integer values 1 and 0 to represent the Boolean values true and false respectively.

MongoDB collections are schema-free, so there’s no schema to indicate that a particular field is of Boolean type. Therefore, a value of 1 really means true in that case.

No match will occur when an attempt to match a field with a Boolean value against 0 or 1 is made. With Studio 3T, remember to use true and false when matching Boolean values.

In MongoDB, integer and Boolean values are of distinct types.

GROUP BY

GROUP BY groups a result set by a particular field, and is often used with other aggregate functions like COUNT, SUM, AVG, MIN and MAX.

For example, to group customers by city, use the following SQL query:

select address.city
from Customers
group by address.city;

The results won’t display the count of customers per city, but the list of unique cities represented in the data.

Using GROUP BY with HAVING and ORDER BY

With Studio 3T 2019.1, it is now also possible to use GROUP BY with HAVING and ORDER BY, even when a field itself is a document, such as in this query:

SELECT customer_record FROM customers GROUP BY customer_record HAVING customer_record.salary > 1000 ORDER BY customer_record.age

In other words, HAVING and ORDER BY clauses can reference internal keywords found in the GROUP BY documents.

COUNT

COUNT shows the numerical count of documents that match the query criteria.

The following SQL query shows the number of customers per city in ascending order:

select count(*), address.city
from Customers
group by address.city
order by count(*);

SUM

SUM shows the total sum of the values in a numeric field.

To see the total number of transactions, execute the query:

select sum(transactions)
from Customers;

AVG

AVG displays the average value of a numeric field across a collection.

The average number of transactions is shown with the query:

select avg(transactions)
from Customers;

MIN

MIN shows the smallest value of a particular field across a collection.

Run the following query to see the individual customer with the lowest total number of transactions:

select min(transactions)
from Customers;

MAX

MAX shows the largest value of a particular field across a collection.

The SQL query

select max(transactions)
from Customers;

shows the individual customer with the highest total number of transactions.

LIMIT

The LIMIT clause limits the number of documents returned in a result set.

Results are limited to show only 12 customers in this query:

select *
from Customers
limit 12;

Studio 3T shows 50 documents by default.

OFFSET

The OFFSET clause skips a certain number of documents in the result set.

To skip the first 25 customers while still limiting the results to 12, use the query:

select *
from Customers
limit 12
offset 25;

LIKE

The LIKE operator is stated to find a pattern in the values of a field, often used with wildcards.

Wildcards

Wildcard characters % and _ are used to substitute characters in a string to find matches.

For example:

select *
from Customers
where address.city like '%New%';

will show customers whose cities contain the substring “New”, e.g. Newark, New York, New Orleans.

To show customers whose cities start with the substring “Lon”, the wildcard character % is only placed at the end:

select *
from Customers
where address.city like 'Lon%';

To find customers whose cities start with any letter but ends with “aris”, the wildcard _is used:

select *
from Customers
where address.city like '_aris';

To find customers whose cities that start with any two letters but end with “ris”, simply add an additional _:

select *
from Customers
where address.city like '__ris';

IN

The IN operator can be used to see if a customer is a member of a set.

select *
from Customers
where address.city in ('Berlin', 'New York', 'Wichita');

BETWEEN and NOT BETWEEN

The BETWEEN operator shows if a value lies within a range. The opposite is the operator NOT BETWEEN.

The query to find customers with transactions between 70 to 100 is:

select *
from Customers
where transactions between 70 and 100;

While the query to show customers whose cities start with a letter not between B and D is:

select *
from Customers
where address.city not between 'B' and 'D';

please keep in mind that numeric values should not be in quotes, as in the example above.

Special BSON Data Types

MongoDB supports special BSON data types, which in the MongoDB shell are represented by ObjectId, NumberDecimal and BinData, for example.

To query values of these types, write out the values as they would be written in the mongo shell:

select *
from specialBSONDataTypesCollection
where _id = ObjectId('16f319f52bead12669d02abc');

select *
from specialBSONDataTypesCollection
where aNumberDecimal = NumberDecimal('9876543210987654321.0');

select *
from specialBSONDataTypesCollection
where aBinDataField = BinData(0, 'QyHcug==');

ISODate values can also be queried this way, but as described in the section above, it can be more convenient to use the date function provided in the Studio 3T SQL tab to specify date values in various common, concise formats.

Ready to try SQL Query? Download the latest version of Studio 3T here.

Supported SQL joins

Studio 3T built upon MongoDB’s native join functionality and recently introduced support for inner joins and left joins in SQL Query.


Users can write SQL joins, then generate the equivalent mongo shell code, using the Query Code feature. They can then use this MongoDB “translation” to query any other appropriate MongoDB database, without additional support or libraries.

However, in doing so, there are one or two considerations to bear in mind, covered below.

Inner joins and left joins

To start, the syntax is simple. To perform an inner join:

select *
from collA
inner join collB on collA.field1 = collB.field2;

And for left joins:

select *
from collA
left join collB on collA.field1 = collB.field2;

If your collection names are written in dot notation (e.g. Pubs.titles), you will need to use aliases – that don’t contain dots – instead.

SQL Query alias

Projections

Projections can be performed on the joined collections. These take the following form:

select collA.field1, collA.field3, collB.field2, collB.field4
from collA
inner join collB on collA.field1 = collB.field2;

Note that fields referenced in the projection must be qualified with the collection name, e.g. ‘collA.field1‘ and not just ‘field1‘.

In a relational ‘schemaful’ setting, it’s enough to only provide the column (field) name if it’s distinct to one of the tables.  However, in the schema-free MongoDB setting, there isn’t a schema to indicate which collection a particular field belongs to, so the field name must be qualified explicitly along with its collection.

By the same token, note that while some ambiguous queries such as ‘select * …‘ are permitted, others like ‘select collA.* …‘ are not.

Multiple joins

Multiple joins are supported, simply write queries such as:

select *
from collA
inner join collB on collA.field1 = collB.field2
left join collC on collB.field3 = collC.field1;

The order in which joins are processed is the same in which they are written, and a join condition can only reference the collections to its left.

Extended queries

SQL aggregate functions such as group by, having, etc. can all be applied to the joined collections as well:

select collA.field3, collB.field3, count(*)
from collA
inner join collB on collA.field1 = collB.field2
where collA.date > date('2018-01-01')
group by collA.field3, collB.field3
having count(*) > 250
order by collA.field3, collB.field3
limit 1
offset 1;

Joining a collection to itself

A collection can be joined to itself through the use of aliases, for example:

select *
from collA as child
inner join collA as parent on parent._id = child.parentId;

Note that after a collection has been aliased, all references to its fields must use that new alias and not its original name.

Cross joins

Studio 3T also supports cross joins, such as:

select *
from collA
cross join collB;

or:

select *
from collA, collB;

It’s important to note however that cross join queries can quickly become processor-intensive to run as the number of documents in the collections grows.

Interested in learning more about joins in MongoDB? Read our MongoDB tutorial, How to Create MongoDB Joins with SQL.

Supported date and time formats

Studio 3T allows dates and times to be expressed in the following formats:

select *
from dates_example
where d > date('2017-03-22T00:00:00.000Z');

select *
from dates_example
where d > date('2017-03-22T00:00:00.000+0000');

select *
from dates_example
where d > date('2017-03-22T00:00:00.000');

select *
from dates_example
where d > date('2017-03-22T00:00:00');

select *
from dates_example
where d > date('2017-03-22T00:00');

select *
from dates_example
where d > date('2017-03-22 00:00:00.000Z');

select *
from dates_example
where d > date('2017-03-22 00:00:00.000+0000');

select *
from dates_example
where d > date('2017-03-22 00:00:00.000');

select *
from dates_example
where d > date('2017-03-22 00:00:00');

select *
from dates_example
where d > date('2017-03-22 00:00');

select *
from dates_example
where d > date('20170322T000000.000Z');

select *
from dates_example
where d > date('20170322T000000.000+0000');

select *
from dates_example
where d > date('20170322T000000.000');

select *
from dates_example
where d > date('20170322T000000');

select *
from dates_example
where d > date('20170322T0000');

select *
from dates_example
where d > date(‘2017-03-22’);

select *
from dates_example
where d > date('20170322');

Exercise

We want to find all customers from our Customers dataset who:

  • have <20 transactions
  • and live in a town called “New York” or “Berlin”

To start building our query, let’s open the SQL Query feature by pressing SQL in the global toolbar or by using the shortcuts : Shift + Ctrl + L (Shift + ⌘+ L).

The query itself has five defining conditions:

  • SELECT – defines the range of documents to be queried
  • FROM – defines where the data should be searched – e.g. what collection(s)
  • WHERE – is the clause that starts filtering the results that fit our query
  • AND – is a conjunctive operator that defines the where clause
  • OR – also a conjunctive operator that is combined with the AND operator to further define our query.

Our query will look like this:

select *
from Customers
where transactions < 20
and (address.city = 'Berlin'
or address.city = 'New York');

Running the query gives us:

The query returns 19 customers who live in either 'Berlin' or 'New York'. Congratulations upon creating your first SQL query in MongoDB!

19 customers – all of whom live in a ‘Berlin’ or ‘New York’. (Don’t be alarmed if your results return different names!)

Congratulations, you’ve now successfully created your first SQL query in MongoDB! If you’re looking for something more advanced, try your hand at using SQL joins to Query MongoDB.

Download Studio 3T here to start using SQL to query MongoDB (in case you haven’t yet).


Take advantage of other Studio 3T features:

  • IntelliShell – The best mongo shell experience available, with autocompletion on JavaScript standard library functions, shell methods, field names, and more
  • Table View – The most powerful spreadsheet view available for MongoDB: expand nested fields, show and hide columns, and step into array-valued columns
  • User Manager and Role Manager – Add and view MongoDB users, and create and grant MongoDB roles without typing a single command

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

Kathryn Vargas

When she's not writing about working with MongoDB, Kathryn spends her free time exploring Berlin's food scene, playing the drums, learning languages (current mission: German), and hiking.

Article navigation

Related articles

  • Lesson 6, Exercise 1: Using the SQL Query tool to run SQL statements
  • SQL Migration: MongoDB and SQL, Coexistence Made Easy
  • Moving from SQL to MongoDB? Studio 3T’s SQL Migration feature makes it simple
  • SQL Migration: MongoDB and SQL, Coexistence Made Easy
  • How does Studio 3T’s SQL Query work? #Studio3T_AMA

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