Querying MongoDB with SQL SELECT Statements

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 SELECT statements.

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

An SQL 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:

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

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 statement:

select *
from customers;

The statement includes two SQL clauses: SELECT and FROM.

• The SELECT clause includes only the SELECT keyword and the asterisk (*) wildcard, which indicates that all columns (fields) should be returned.
• The FROM clause 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 first, last, and 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.

Finally, the 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:

• The find method call on the customers collection maps to the SELECT statement’s FROM clause.
• The find statement’s query section maps to the SELECT statement’s WHERE clause.
• The find statement’s projection section maps to the SELECT statement’s SELECT clause.
• The find statement’s sort section maps to the SELECT statement’s ORDER BY clause.

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 SELECT, FROM, WHERE, and 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 sales database