Exercise 4: Running an aggregate statement against the target collection

In this exercise, you’ll use IntelliShell to run a simple aggregate statement against the customers_merged collection that you created in the previous exercise.

To run the aggregate statement

1. In the Connection Tree, expand the sales database node and Collections node, if necessary.
2. Right-click the customers_merged collection node, and then click Open IntelliShell. Studio 3T adds the IntelliShell tab to the main window and defines a basic find statement on the customers_merged collection object. 
3. Replace the find statement with the following aggregate statement

db.getCollection("customers_merged").aggregate(
  [
    { "$match": { 
      "$or": [
        { "state": "Colorado" }, 
        { "state": "New Mexico" } ] }
    }, 
    { "$group": { 
      "_id": { "state": "$state", "city": "$city" }, 
      "totals": { "$sum" : "$transactions" } }
    }, 
    { "$sort": { "_id": 1 } }
  ]
);

The aggregate statement includes three stages:

• The $match stage returns documents whose state value is either Colorado or New Mexico.
• The $group stage groups the documents first by state and then by city and provides the total number of transactions for each group.
• The $sort stage sorts the documents by their _id value, which means that they’ll be sorted first by state and then by city.

4. On the IntelliShell toolbar, click the Run button (the green arrow with the screen tip that reads Run entire script). Studio 3T runs the aggregate statement and displays the results in the tab’s lower panel, as shown in the following figure. The results are displayed in Table View. Use the right-click menu on the table and select Show All Embedded Fields to see the fields within _id. This is the table view with all embedded fields displayed

5. Close the IntelliShell tab. If prompted to save changes, click No, Discard.
6. Close Studio 3T.