Exercise 1: Running a SQL aggregate query in MongoDB

In this tutorial, you’ll use the SQL Query tool to retrieve data from the welsh_pubs collection, which contains document data about pubs in Wales and their regional districts, called local authorities. You’ll run a SQL aggregate query that returns the number of pubs in each local authority.

To run the SQL query

If you’ve already created the pubs database and the welsh_pubs collection from the previous section, skip to Step 3.

1. Launch Studio 3T and connect to MongoDB Atlas.

You will need a connection string or Uniform Resource Identifier (URI) to connect to MongoDB Atlas. If you haven’t generated a URI, go back to the previous exercise Setting Up MongoDB Atlas to set up an Atlas cluster, and refer to Step 8 of Configuring MongoDB Atlas to get the connection string. Be sure to replace the placeholder in the URI with your actual password.

2. Add the pubs database, add the welsh_pubs collection to the database, and add the documents in the welsh_pubs.json file to the collection.

Refer to Exercise 1 of Working with Document Data for information about setting up the database and collection and importing the data from the welsh_pubs.json file.

3. In the Connection tree, right click the welsh_pubs collection node and click Open SQL.

This launches the SQL Query tool, which opens a new tab, as shown in the following figure.

The SQL tab is divided into two sections.

The top section provides a command prompt for running SQL commands. When you first open the SQL tab, the command prompt is prepopulated with a SELECT statement that retrieves all data from the current collection. In this case, the statement is specific to the welsh_pubs collection, as shown in the following code:

select *
from welsh_pubs;

The bottom section of the SQL tab is similar to the bottom section of a collection tab, except that it also includes the SQL Query tab, which displays the SQL query associated with the current results.

4. Click the run button on the toolbar above the command prompt ().

Studio 3T displays the query results in the Result tab in the lower section.

The collection should include 3,122 documents.

The run button icon changes depending on whether you’ve selected any SQL command text. When no text is selected, the button’s tooltip reads Execute SQL statement at cursor, and Studio 3T runs the SQL statement where the cursor is located. When text is selected, the button changes, and the tooltip reads Execute selected SQL statement. Studio 3T runs only the selected command.

5. Copy and paste the following SQL statement to the SQL Query command prompt, on the line below the existing SQL statement:

select local_authority, count(*)
from welsh_pubs
group by local_authority
order by count(*) desc;

The SQL statement groups the document data by the local authorities and then calculates the total number of pubs for each authority. The statement also sorts the results by the number of pubs, with the highest number first.

6. Select the recently-added SQL statement in the command prompt and click the run button.

Your query results should look similar to those in the following figure.

7. Leave the SQL tab open with the query results in place for the next exercise.

You’ve finished the exercise! Click on Test your skills to review what you’ve learned.