Mongoose Aggregate to group by brand_id and orders_sum > 1000

Question

From the Sales collection, I am trying to get all the documents grouped by brand_id and each brands total order value to be greater than 1000$.

So if there are A, B, C, D brands. Then it should documents from the Sales collection for each brand, where the brand's total sales is above 1000$.

My code for fetching the documents grouped by brand_id is:

const data = await Sales.aggregate([
    {
      $group: {
        _id: '$brand_id',
        records: {
            $push: "$$ROOT"
        }
      }
    }
]);

But am confused on how to add the condition of fetching the documents only if each brands sales total is above 1000$.

I tried checking the documentation of Mongoose, but I couldn't get an idea of how to add the condition on the aggregate()

Answer 1

Approach 1

1. $group - Group by brand_id and perform sum for your order/price field (total_sales).

2. $match - Filter the document with total_sales greater than 1000.

3. $unwind - Descontruct the records array to multiple documents.

4. $replaceWith - Replace input document with the original sales document.

const data = await Sales.aggregate([
  {
    $group: {
      _id: "$brand_id",
      total_sales: {
        $sum: "$price"
      },
      records: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $match: {
      total_sales: {
        $gt: 1000
      }
    }
  },
  {
    $unwind: "$records"
  },
  {
    $replaceWith: "$records"
  }
])

Demo Approach 1 @ Mongo Playground

---

Approach 2

1. $setWindowFields - Alternative way that allows to perform sum for your order/price by brand_id (total_sales_by_brand) in each document.

2. $match - Filter the document with total_sales_by_brand greater than 1000.

3. $unset (Optional) - Remove the total_sales_by_brand field.

const data = await Sales.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$brand_id",
      sortBy: {},
      output: {
        total_sales_by_brand: {
          $sum: "$price"
        }
      }
    }
  },
  {
    $match: {
      total_sales_by_brand: {
        $gt: 1000
      }
    }
  },
  {
    $unset: "total_sales_by_brand"
  }
])

Demo Approach 2 @ Mongo Playground