Developers and administrators often come to MongoDB with a background in relational database management systems such as SQL Server or Oracle Database.
These systems rely on the Structured Query Language (SQL) for accessing and manipulating data.
If you’re one of these people, you’ll be happy to know that Studio 3T includes the SQL Query tool, which lets you run SQL
SELECT statements directly against a MongoDB collection, using the same syntax you use for a relational database.
In this section, you’ll learn how to use the SQL Query tool to run different types of SQL
You’ll also see how the tool translates a
SELECT statement to a mongo shell
find statement or
aggregate statement, depending on whether the
SELECT statement groups and aggregates data.
In addition, you’ll learn how to export an
aggregate statement to the Aggregation Editor in Studio 3T, where you can modify the query to meet your needs.
Running SQL statements against a MongoDB collection
SELECT statement typically retrieves data from tables in a database, much like a mongo shell
find statement retrieves documents from a collection in a MongoDB database.
When you run a
SELECT statement in SQL Query, the tool automatically translates the statement to a mongo shell
find statement or
aggregate statement and runs it against the target collection.
If you’re well-versed in SQL, the SQL Query tool offers a quick and easy way to create these statements even if you’re new to MongoDB. The tool can also serve as a useful learning aid for familiarizing yourself with the mongo shell language and understanding how to search data in a MongoDB collection.
For example, suppose you want to retrieve all documents in the
customers collection (which you created at the beginning of this course). If you’re using the mongo shell to retrieve the data, you can run the following find statement:
The statement calls the
find method on the
customers collection without passing any arguments into the method. Consequently, the statement will return all documents in the collection. You can achieve the same results in SQL Query by running the following
select * from customers;
The statement includes two SQL clauses:
SELECTclause includes only the
SELECTkeyword and the asterisk (*) wildcard, which indicates that all columns (fields) should be returned.
FROMclause identifies the target data source, in this case, the customers collection.
If you were querying a relational database, you would specify a table name, but the collection name replaces that, allowing you to retrieve data from a collection.
The following table shows how SQL components map to MongoDB document components.
Not surprisingly, SQL Query lets you run more complex
SELECT statements than the simple one you just saw. For example, the following statement refines the query by limiting the fields and documents included in the results:
select first, last, transactions from customers where transactions > 65 order by transactions desc, last asc;
As in the preceding example, the statement starts with the
SELECT clause, but this time, it specifies that only the
transactions fields should be returned.
The statement also includes a
WHERE clause, which limits the results to documents with a
transactions value greater than 65.
ORDER BY clause sorts the results, first by the
transactions values, in descending order, and then by the
last values, in ascending order.
If you were to run this statement and then view the Query Code tab in SQL Query, you would see the statement translated to the mongo shell
find statement shown in the following figure:
The figure also indicates how each section of the mongo shell
find statement maps to the original SQL
SELECT statement clauses:
findmethod call on the customers collection maps to the
findstatement’s query section maps to the
findstatement’s projection section maps to the
findstatement’s sort section maps to the
After SQL Query has generated the
find statement, you can then copy it for other applications or open it in IntelliShell, where you can then modify it further.
If your original
SELECT statement includes an aggregation, SQL Query will instead generate a mongo shell
aggregate statement, which you can then edit in the Aggregation Editor or in IntelliShell.
SQL Query supports many of the basic elements that make up an SQL statement. For example, as the previous example demonstrates, you can include
ORDER BY clauses.
You can also include
GROUP BY and
HAVING clauses, as well as comparison operators, logical operators, built-in functions, and wildcards.
SQL Query also supports SQL joins within the context of a collection. Although we can’t cover all these elements within a single section, you can find more information about which ones are supported and how they work in the Studio 3T Knowledge Base article, SQL Query.
By the end of this section, you will learn how to:
- Use the SQL Query tool to run SQL statements
- Use the SQL Query tool to aggregate collection data
What you will need:
- Access to a MongoDB Atlas cluster
- Access to the customers collection in the