Exercise 4: Changing the sort order in the aggregation pipeline

In this exercise, you’ll update the $sort stage, which is the fifth and last stage in the pipeline. You’ll base the sort order on the state field, rather than the total field, and specify that the documents be sorted in ascending order.

To change the sort order in the aggregation pipeline

1. Go to the 5: $sort tab in the Aggregation Editor.
2. Replace the existing expression with the following code:

{ "state": 1 }

The new code specifies that the documents should be ordered based on the state field. The value 1 indicates that they should be sorted in ascending order.

3. In the Stage Input pane, click the Execute button. Studio 3T runs the pipeline up to but not including the current stage. This is the data that is used as input for the $sort stage.
4. In the Stage Output pane, click the Execute button. This will execute the pipeline up to and including the fifth stage. The following figure shows part of the results, with the documents sorted by the state field.

5. Go to the Query Code tab. Verify that the $sort operator and its expression have been updated. The aggregate statement should now look like the following code:

db.getCollection("customers").aggregate(
  [
    { 
      "$match" : { 
        "dob" : { 
          "$lt" : ISODate("1970-01-01T00:00:00.000+0000")
        }
      }
    }, 
    { 
      "$group" : { 
        "_id" : "$address.state", 
        "total" : { 
          "$sum" : "$transactions"
        }
      }
    }, 
    {
     "$project" : { 
        "_id" : 0.0, 
        "state" : "$_id", 
        "total" : 1.0
      }
    }, 
    { 
      "$replaceRoot" : { 
        "newRoot" : { 
          "state" : "$state", 
          "total" : "$total"
        }
      }
    }, 
    { 
      "$sort" : { 
        "state" : 1.0
      }
    }
  ], 
  { 
    "allowDiskUse" : true, 
    "collation" : { 
      "locale" : "en_US"
    }
  }
);

6. Go to the Pipeline tab. Verify that the pipeline includes all five stages and that they’ve been added and updated as expected.
7. In the Pipeline output pane, click the Refresh button to update the results. Studio 3T runs the statement and returns the results, as shown in the following figure.

The results should include the state and total fields, with the data sorted in ascending order based on the state field. As you saw earlier, the results also include the _id field when viewing them in Table View, but if you switch to JSON View, you’ll see only the state and total fields.

8. Click Save on the Aggregation Editor toolbar to save your changes. You will use the state_transaction.js file for the next section in this course.
9. Close the Aggregation tab and then close Studio 3T.