Exercise 3: Adding a computed ratio field based on the converted lookup field

In this exercise, you’ll add a stage to the aggregation pipeline after the $set stage. The new stage will insert a field into the pipeline that provides the transactions-to-population ratio for each state. You’ll also round the ratio values to seven decimal places.

To add the ratio field

1. On the Pipeline tab of the Aggregation Editor, right-click the $set pipeline stage, and then click Add New Stage After Selected Stage.
2. On the new tab, select the $set option from the Operator drop-down list. You must add a second $set stage, rather than using only one, because you need to use the output from the first $set stage to add the new field to the pipeline.
3. In the editor window, replace the placeholder text and curly braces with the following code:

{
  ratio: { $round: [ { $divide: [ "$transactions", "$population" ] }, 7 ] }
}

The $set expression creates a field named ratio and then uses the $divide operator to divide the transactions field by the population field. This will give you your transaction-to-population ratio for each state. The expression then uses the $round operator to round the output to seven decimal places.

4. In the Stage Input pane, click the Execute button. Studio 3T runs the pipeline for all stages up to but not including the $set stage.
5. In the StageOutput pane, click the Execute button. This will execute the pipeline up to and including the second $set stage. The following figure shows part of the results, as they appear in Table View.

The pipeline now includes the ratio field, a numerical field that’s rounded up to seven decimal places.

6. Click Save on the Aggregation Editor toolbar to save your changes.
7. Leave the Aggregation tab open and the existing statement in place for the next exercise. You’ll be modifying the statement by changing the sort order and limiting the number of results.