MongoDB Aggregation $out

Introduction

The $out stage in MongoDB's aggregation framework is used to write the results of an aggregation pipeline to a new collection. This stage effectively creates a new collection (or replaces an existing one) with the documents generated by the pipeline, making it useful for storing aggregated results for further analysis or reporting.

Prerequisites

• MongoDB installed and running on your machine.

• Access to mongosh and a populated collection with sample data.

Step 1: Start mongosh

1. Open Terminal/Command Prompt:

   • Launch your terminal (macOS/Linux) or command prompt (Windows).

2. Start mongosh:

   • Type the following command and press Enter:

     mongosh

Step 2: Switch to the Database

Switch to the database where your collection is located.

1. Use a Database:

   use myNewDatabase

Step 3: Sample Data

Assume you have a collection named sales with documents that look like this:

{ "_id": 1, "item": "Apple", "quantity": 5, "price": 2 }
{ "_id": 2, "item": "Banana", "quantity": 10, "price": 1 }
{ "_id": 3, "item": "Carrot", "quantity": 7, "price": 1.5 }

Step 4: Using $out

1. Basic Usage of $out

To create a new collection called totalSales that contains the total revenue for each item, you can use the following aggregation pipeline:

db.sales.aggregate([
  {
    $group: {
      _id: "$item",
      totalRevenue: { $sum: { $multiply: ["$quantity", "$price"] } } // Calculate total revenue for each item
    }
  },
  {
    $out: "totalSales" // Output the results to the totalSales collection
  }
])

Note: If the totalSales collection already exists, it will be replaced by the new data.

2. Verify the Output

To check the contents of the newly created totalSales collection, you can use the following command:

db.totalSales.find().pretty()

Output:

{ "_id": "Apple", "totalRevenue": 10 }
{ "_id": "Banana", "totalRevenue": 10 }
{ "_id": "Carrot", "totalRevenue": 10.5 }

3. Using $out After Filtering

You can also use $out after applying various stages in the aggregation pipeline. For example, if you want to filter items based on quantity before calculating the total revenue:

db.sales.aggregate([
  {
    $match: { quantity: { $gt: 5 } } // Filter for items with quantity greater than 5
  },
  {
    $group: {
      _id: "$item",
      totalRevenue: { $sum: { $multiply: ["$quantity", "$price"] } }
    }
  },
  {
    $out: "filteredSales" // Output the results to the filteredSales collection
  }
])

4. Combining $out with Other Stages

You can combine $out with various stages to create more complex aggregations. For instance, if you want to group items by category and then output the results:

Assuming you have a products collection with a category field:

products collection:

{ "_id": 101, "item": "Apple", "category": "Fruit", "price": 2 }
{ "_id": 102, "item": "Banana", "category": "Fruit", "price": 1 }
{ "_id": 103, "item": "Carrot", "category": "Vegetable", "price": 1.5 }

You can create a new collection that summarizes total revenue by category:

db.sales.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "item",
      foreignField: "item",
      as: "productDetails"
    }
  },
  { $unwind: "$productDetails" },
  {
    $group: {
      _id: "$productDetails.category",
      totalRevenue: { $sum: { $multiply: ["$quantity", "$productDetails.price"] } }
    }
  },
  {
    $out: "categorySales" // Output the results to the categorySales collection
  }
])

Conclusion

You have learned how to use the $out stage in MongoDB's aggregation framework to write the results of an aggregation pipeline to a new or existing collection. This stage is beneficial for storing processed data for future queries and analyses.

Feel free to reach out if you have any questions or need further guidance!