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.
If you’ve already created the pubs database and the welsh_pubs collection from the previous section, skip to Step 3.
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.
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.
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.
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.
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.
Your query results should look similar to those in the following figure.
You’ve finished the exercise! Click on Test your skills to review what you’ve learned.