Exercise 1: Creating a view based on an aggregation query

In this exercise, you’ll add an aggregate statement to the Aggregation Editor, run the statement, and create a view based on that statement. You’ll then open the view in Studio 3T. The idea here is that third-party applications with the necessary permissions can then access the view at any time by using simple find operations.

To create the view

1. Launch Studio 3T and connect to MongoDB Atlas.
2. In the Connection Tree, expand the sales database node and, if necessary, expand the Collections node. 
3. Right-click the customers_txn collection node, and then click Open Aggregation Editor. Studio 3T adds the Aggregation tab to the main window. The tab displays the Aggregation Editor, with the editor’s Pipeline tab active. At this point, the aggregation pipeline is empty.
4. Copy the following aggregate statement to your clipboard.

db.customers_txn.aggregate( [
  { 
    "$replaceRoot": { "newRoot": { 
      "state": "$address.state", 
      "payments": "$payments.value" } }
  }, 
  { 
    "$unwind" : { "path": "$payments" }
  }, 
  { 
    "$group": { 
      "_id": "$state", 
      "total": { "$sum": { "$toInt": "$payments" } } }
  }, 
  { 
    "$sort": { "total": -1.0 }
  } 
] );

5. In the Aggregation Editor, click the Paste button on the far right end of the toolbar. This adds the aggregate statement and automatically defines the following four stages:

• The first stage uses the $replaceRoot operator to replace each document in the pipeline with the embedded document specified in the expression. The state field in the new documents will be based on the original $address.state field, and the new payments field will be based on the original $payments.value field. The new payments field is created as an array because the original field is part of an array.
• The second stage uses the $unwind operator to deconstruct the values in the payments array and output a document for each array element.
• The third stage uses the $group operator to group the data by the state field and find the total amount of payments for each state.
• The fourth stage uses the $sort operator to order the results by the total field, in descending order.

6. On the toolbar, click the Run button. The query results are displayed in the lower pane, as shown in the following figure.

The results show the total amount of sales for each state, with the data sorted in descending order, based on the total values.

7. Right-click anywhere in the Pipeline flow window or Pipeline output window, and then click Create view from this Aggregate Query. (You might need to scroll down to the bottom of the context menu.)
8. In the Create View dialog box, type state_sales in the Enter view name text box and click OK. Studio 3T creates the view and displays the View Creation message box, which indicates that the view has been successfully created.
9. Click OK to close the message box.
10. In the Connection Tree, expand the sales database node, if necessary, and expand the Views node. The state_sales view should now be listed under the Views node, as shown in the following figure.

11. Double-click the state_sales view node. Studio 3T open the view in its own tab in the main window.

You can query the view just like a collection, either working directly within Studio 3T or accessing it through third-party applications. In this way, you can use the view to generate reports, embed data in a dashboard, or in other ways present the aggregated information to your users.

12. Close the state_sales view tab and the Aggregation Editor but leave Studio 3T open for the next exercise.