Group and Total

Add a match stage for orders in 2020

First, add a $match stage that matches orders placed in 2020:

pipeline.push({
  $match: {
    orderdate: {
      $gte: new Date("2020-01-01T00:00:00Z"),
      $lt: new Date("2021-01-01T00:00:00Z"),
    },
  },
});

Add a sort stage to sort by order date

Next, add a $sort stage to set an ascending sort on the orderdate field to surface the earliest 2020 purchase for each customer in the next stage:

pipeline.push({
  $sort: {
    orderdate: 1,
  },
});

Add a group stage to group by email address

Add a $group stage to group orders by the value of the customer_id field. In this stage, add aggregation operations that create the following fields in the result documents:

• first_purchase_date: the date of the customer's first purchase

• total_value: the total value of all the customer's purchases

• total_orders: the total number of the customer's purchases

• orders: the list of all the customer's purchases, including the date and value of each purchase

pipeline.push({
  $group: {
    _id: "$customer_id",
    first_purchase_date: { $first: "$orderdate" },
    total_value: { $sum: "$value" },
    total_orders: { $sum: 1 },
    orders: { $push: 
      { 
        orderdate: "$orderdate", 
        value: "$value" 
      }
    },
  },
});

Add a sort stage to sort by first order date

Next, add another $sort stage to set an ascending sort on the first_purchase_date field:

pipeline.push({
  $sort: {
    first_purchase_date: 1,
  },
});

Add a set stage to display the email address

Add a $set stage to recreate the customer_id field from the values in the _id field that were set during the $group stage:

pipeline.push({
  $set: {
    customer_id: "$_id",
  },
});

Add an unset stage to remove unneeded fields

Finally, add an $unset stage. The $unset stage removes the _id field from the result documents:

pipeline.push({ $unset: ["_id"] });

Run the aggregation pipeline

Add the following code to the end of your application to perform the aggregation on the orders collection:

const aggregationResult = await ordersColl.aggregate(pipeline);

Finally, run the following command in your shell to start your application:

node agg_tutorial.js

Interpret results

The aggregation returns the following summary of customers' orders from 2020:

{
  first_purchase_date: 2020-01-01T08:25:37.000Z,
  total_value: 63,
  total_orders: 1,
  orders: [ { orderdate: 2020-01-01T08:25:37.000Z, value: 63 } ],
  customer_id: 'oranieri@warmmail.com'
}
{
  first_purchase_date: 2020-01-13T09:32:07.000Z,
  total_value: 436,
  total_orders: 4,
  orders: [
    { orderdate: 2020-01-13T09:32:07.000Z, value: 99 },
    { orderdate: 2020-05-30T08:35:52.000Z, value: 231 },
    { orderdate: 2020-10-03T13:49:44.000Z, value: 102 },
    { orderdate: 2020-12-26T08:55:46.000Z, value: 4 }
  ],
  customer_id: 'elise_smith@myemail.com'
}
{
  first_purchase_date: 2020-08-18T23:04:48.000Z,
  total_value: 191,
  total_orders: 2,
  orders: [
    { orderdate: 2020-08-18T23:04:48.000Z, value: 4 },
    { orderdate: 2020-11-23T22:56:53.000Z, value: 187 }
  ],
  customer_id: 'tj@wheresmyemail.com'
}

The result documents contain details from all the orders from a given customer, grouped by the customer's email address.