Video Transcript (English)

Hello! In this video, you’ll learn how to build an aggregation pipeline that uses the sort and project stages.

Let’s begin with an overview of these stages.

The sort stage sorts all input documents and passes them to the next pipeline stage in the sorted order.

To sort in ascending order, we use the value of 1. For descending order, we use the value of -1.

The project stage determines the output document shape. It allows us to specify the fields returned by the aggregation, whether by including or excluding certain fields. Documents that contain only the specified fields will be passed to the next stage of the pipeline.

The specified fields can be existing fields from the input documents or newly computed fields.

The project stage should usually be the last stage in a pipeline because it specifies the exact fields to be returned to the client.

Now let’s use the sort and project stages in an aggregation pipeline.

For this example, we’ll work with bank account data.

Let’s say that we want to: Find all checking accounts with a balance greater than $1500, sort the results from highest balance to lowest, and return only the original balance, the balance converted to Great British Pounds (GBP), and the account type.

To compute the GBP balance, we’ll assume an exchange rate of 1 British pound to 1.3 US Dollars.

Here’s a sample document from the collection that we’ll work with.

Note that the subset of fields that we want to return from our aggregation are: account_type, and balance.

Also note that there is no field that contains the balance in pounds. We’ll need to add a field with this computed value in the aggregation.

To perform the aggregation, we’ll first define our aggregation stages.

Then we’ll create a pipeline by storing these stages in a list.

Finally, we’ll pass the pipeline to the aggregate method.

Let’s implement the pipeline by using the match, sort, and project stages.

Let’s open our Python file in VS code.

Our virtual environment is already activated, and we connected to our Atlas cluster with MongoClient.

We have a reference to the 'bank' database and a reference to the accounts collection.

We also assigned the conversion rate of 1.3 to a variable.

Now we’ll start defining our aggregation stages.

First, we select the checking accounts with a balance of greater than $1500. To implement this, we use a $match stage to filter the incoming documents by these criteria. Note that we assign this stage to the `select_accounts` variable.

The $match operator allows only documents that match the specified query conditions to continue to the next stage.

The $gt operator is used in the query condition to state that we want only documents that have a balance of more than $1500.

Next, we need to sort the results from the highest balance to the lowest.

To do this, we create a $sort stage in which the balance is set to -1. This will organize the accounts in descending order.

The $sort stage is assigned to the variable `organize_by_original_balance`.

Now, we need to specify the fields we want to return by using a $project stage.

We want to return the `account_type`, and `balance` fields, so we set both of them to 1.

The _id field is always returned by default. To exclude it, we set it to 0.

Finally, we create a new field in the $project stage called gbp_balance. This field calculates the conversion from US dollars to British pounds. Here, to calculate the converted value, we divide the balance value by the conversion rate that we set earlier.

Now that all the pipeline stages are defined, we create our pipeline by storing the stages in a list.

Note that when we create aggregation pipelines, we are explicitly specifying the order of processes. This means that we need to be mindful that our stages are in an appropriate order.

For example, we placed our $match stage, called `select_accounts`, first in the pipeline. Placing $match operations early in the pipeline minimizes the amount of processing later in the pipeline.

The $project stage should usually be the last stage because it specifies the exact fields in the output. Here we placed the $project stage, which is named `return_specified_fields`, last in the pipeline.

That leaves the $sort stage, `organize_by_original_balance`, which sorts the original balance in descending order. Here we place `organize_by_original_balance` second to last in the pipeline.

To get our aggregation results, we pass the pipeline to the `aggregate` collection method.

The aggregate method returns a cursor, which we assign to the variable `results`.

Finally, we’ll iterate through the cursor and print the results of the aggregation.

Now that we’ve reviewed the code, let’s run it!

Each returned document contains the requested fields, which are account_type, balance, and the computed gbp_balance.

Well done!

In this video, you learned how to use the sort and project stages in an aggregation pipeline.