Master MongoDB Aggregation Pipeline: Essential Operators & Real-World Examples

Link to Video: https://www.youtube.com/watch?v=wnG5iLtP58s&t=1s

Video Transcript

Understanding MongoDB Aggregation Pipelines

Data in MongoDB is stored using a document-based structure. Sometimes, we need to process this data, which could involve manipulating, filtering, or grouping it based on certain conditions. Additionally, we may want to sort or filter data from arrays. Fortunately, MongoDB provides an in-house solution for these operations called aggregation pipelines.

What is an Aggregation Pipeline?

Aggregation pipelines work in stages. In each stage, MongoDB processes data and outputs it, which is then passed as input to the next stage. By using different stages, we can apply various data processing techniques to achieve our desired result. It's called a "pipeline" because the data flows from one stage to the next.

Let's explore the aggregation pipeline through some examples. We'll use MongoDB Compass, a free application that allows you to view data stored in MongoDB. For this demonstration, I've created a database called e-commerce with two collections: customers and orders.

Example: Finding All Shipped Orders

Let's say we want to find all orders with a status of "shipped." In the orders collection, there's a field called status that indicates whether an order is shipped, delivered, etc.

1. Stage 1: We use the match operator (similar to a WHERE clause in SQL) to find documents where status equals "shipped."

   • This gives us the documents with a "shipped" status.

2. Stage 2: Now, let's count the number of "shipped" orders using the count operator. The output of the previous stage is passed as input, and we get the count of "shipped" orders.

MongoDB Compass also allows us to export these aggregation pipelines into code, supporting various languages like Java.

Example: Grouping Orders by Status

Next, we want to group the orders based on their status. For this, we use the group operator:

• First, we group by status.

• Then, we use an accumulator function, like sum, to count how many orders fall under each status (e.g., shipped, delivered, pending).

• 
  

Using Accumulators and Grouping

MongoDB's group operator allows us to group by fields and use accumulator functions like sum to perform calculations during aggregation. For example, we can calculate the total number of orders in each status.

• We can also use null in the group operator to group all documents together, making it possible to calculate the total number of documents or the total sum of a particular field across all documents.

• 
  

Example: Total Sales and Average Order Value

To calculate the total sales across all orders, we can use the sum operator with the totalAmount field. If we wanted to calculate the average order value, we would replace sum with the avg operator.

Example: Counting Products Sold Using unwind

Suppose we want to count the total quantity of each product sold. The orders collection contains an array field called items, where each item has a productID. Since items is an array, we can use the unwind operator to flatten the array, creating one document per item. After unwinding, we can group by productID and use sum to calculate the total quantity sold.

Sorting and Limiting Orders

To sort orders by orderDate, we use the sort operator. For example, sorting by orderDate in descending order can be done by passing -1 as the value.

If we want to find the five most recent orders, we can combine sort with the limit operator to return only the first five results.

Example: Counting Orders by City

To count the number of orders from each city, we can group the orders by the shippingAddress.city field and use the sum operator to count the number of orders in each group.

Matching Multiple Conditions

MongoDB's match operator can handle complex queries. For example, if we want to find shipped orders from New York, we can match on both the status field and the shippingAddress.city field simultaneously.

Filtering with Regular Expressions

To match cities that start with the letter "N", we can use the regex operator inside a match stage. This allows us to find all documents where city starts with a specific pattern.

Projecting Specific Fields

To return only specific fields, we use the project operator. For example, we might only want to return orderID, customerID, and totalAmount in the results.

Grouping and Returning Order IDs

If we want to group orders by status and return the orderIDs for each status, we can use the push operator to create an array of orderIDs for each status group.

So, right now my Orders table is looking up from the Customers table. I'll be using the lookup operator to do this. The localField refers to the field name present in the Orders table. In this case, we have customerID in the Orders table, so I'll mention that here. The foreignField is the field in the Customers table where we want to match the data. The Customers table also has a field called customerID, so I'll mention that as the foreignField.

I want to name this new field in my result customerDetails. Now I get the result where each order includes customer details as an array of objects. For example, for Customer 1, it might look something like this:

{ "orderID": "123", "customerDetails": [ { "customerID": "1", "name": "John Doe", "address": "123 Main St" } ] }

Now, why did it create an array? Well, that's because of how data is stored in MongoDB. If there were multiple documents with the same customerID, MongoDB would append all the corresponding customer objects into this array. From an application point of view, we expect only one document, but because there's no restriction in place, MongoDB makes it an array.

If I want this to be an object instead of an array, I can use another stage. I’ll use the project operator to specify which fields I want, for example, orderID, status, and customerDetails. But if I don't want customerDetails as an array, I can fetch the first element of the array by using the arrayElemAt operator.

Here's how that works:

• I'll rename customerDetails to just customer.

• Using arrayElemAt, I’ll fetch the element at index 0, which is the first (and in this case, only) element.

So now, I have the customer details as a single object instead of an array. The other way to do this would be to use the first operator, which also gives me the same result. Both first and arrayElemAt can convert arrays to objects.

Complex Multi-Staged Problem Statements

Now, let's move on to more complex problem statements. Let's say I want to find the top 3 customers who have spent the most money on their orders.

1. Step 1: I need to group my orders by customerID. So, I'll use the group operator to group the orders.

2. I'll accumulate the total expenditure for each customer. I can create a custom field called expenditure and sum the totalAmount for each customer's orders.

3. 
   

Here’s how the result might look:

{ "_id": "customerID", "expenditure": 1500 }

1. Step 2: Since I need the top 3 customers, I'll sort the results by expenditure in descending order.

2. Step 3: I'll use the limit operator to restrict the output to just 3 customers.

3. 
   

So now I have my top 3 customers who spent the most money. Instead of writing multiple queries, I can export this as a MongoDB pipeline, which is essentially an array of stages like group, sort, and limit. If I’m using Node.js, I can even copy this code directly into my application.

Another example: I want to find all orders where the customer is from a specific state, and the order status is "delivered."

To solve this:

1. I’ll start with a lookup to join the Customers table with the Orders table.

2. Then, I’ll use the unwind operator to convert the customerDetails array into a single object.

3. Finally, I’ll use the match operator to filter for orders where the customer is from a specific state (let’s say CA) and the order status is "delivered".

4. 
   

So now I have all the orders where the customer is from California, and the status is delivered.

Another problem: I want to show a summary of customers who have placed more than one order. The summary should show the total amount spent and an array of their order IDs.

1. First, I’ll group by customerID again, and I’ll sum up the total amount spent by each customer.

2. I’ll also push all the order IDs into an array.

3. Next, I need to filter out customers who have placed less than 2 orders. So, I’ll use the sum operator to create a field called orderCount, and I’ll increment this field by 1 for each order.

4. Then, I’ll add a match stage to only include customers whose orderCount is greater than 1.

Now, I want to clean up my output. I don’t need to show the orderCount anymore, so I’ll use the project operator to display only the customerID, totalAmount, and orderIDs. Here's what the final output might look like:

{ "customerID": "3", "totalAmount": 2000, "orderIDs": ["order1", "order2"] }

Conclusion and Bonus

In this video, I’ve gone through different problem statements to show how MongoDB aggregation pipelines work.

To summarize, we used several operators:

• group to group documents by a certain field.

• sum to calculate totals.

• match to filter data based on conditions.

• project to modify the output and select specific fields.

• lookup to perform joins between collections.

• unwind to deconstruct arrays.

I also mentioned that MongoDB pipelines have some limitations. According to the official MongoDB documentation, each document in the result set cannot exceed 16MB. If your pipeline requires more than 100MB of memory, you can use the allowDiskUse option to allow MongoDB to use disk space instead of RAM for processing.

Lastly, we only covered a few operators here, but MongoDB provides a wide range of operators that you can explore in the official documentation.