Lesson 7, Exercise 2: Querying a MongoDB view

In this exercise, you’ll use several Studio 3T tools to query the package_totals view you created in the previous exercise.

Each query will return the same rows, although not necessarily the same fields. This will give you a sense of the different methods you can use to access data through a view, while demonstrating how the various approaches compare.

To query the view

1. Return to the package_totals view tab for the customers collection. The tab should still be open from the previous exercise.

2. In the query form at the top of the package_totals view tab, replace the curly braces in the Query text box with the following script:

{ "package" : "Basic" }

The code defines a simple search condition specifying that a document’s package value must equal Basic for the document to be included in the results.

3. Click the Run query button to the right of the Query text box. Studio 3T returns only two rows, which are shown in the following figure. As you can see, the package value for each row is Basic.

4. Close the package_totals view tab.

5. In the Connection Tree, expand the sales database node and then the database’s Views node, if necessary. The package_totals view should be listed beneath the Views node.

6. Right-click the package_totals view node, and then click Open SQL. Studio 3T launches SQL Query in its own tab. 

7. At the SQL Query command prompt, replace the existing code with the following SELECT statement:

select prio_support, transactions
from package_totals
where package = 'Basic';

The statement returns the prio_support and transactions fields from the package_totals view, but limits the results to those documents whose package value is Basic.

8. Press F5 to run the SQL query. Studio 3T should return the same two rows as the previous query, but without the package field.

9. Close the SQL tab. If prompted to save your changes, click No.

10. In Connection Tree, right-click the package_totals view node, and then click Open IntelliShell. Studio 3T launches IntelliShell in its own tab.

11. At the IntelliShell command prompt, replace the existing code with the following find statement:

db.getCollection("package_totals").find(
  { 
    "package" : "Basic"
  }, 
  { 
    "prio_support" : "$prio_support", 
    "transactions" : "$transactions", 
    "_id" : NumberInt(0)
  }
);

Like the previous SELECT statement, the find statement returns the prio_support and transactions fields from the package_totals view, while limiting the results to those documents whose package value is Basic.

12. Press F5 to run the find statement. Studio○3T should return the same two rows as before.

13. Close the IntelliShell tab. If prompted to save your changes, click No.

14. Leave Studio 3T open for the next exercise.