loading...

MongoDB Aggregation pipeline stages to the rescue!

tushark1 profile image Tushar Khubani ・3 min read

Imagine a situation where you have a schema with a one to many relationship

//customer schema
const customerSchema = new mongoose.Schema(
  {
    name: {
      type: String
    },
    email: {
      type: String
    }
  }
);
//consumable's schema
const consumableSchema = new mongoose.Schema(
  {
    name: {
      type: String
    },
    email: {
      type: String
    }
  }
);
//payments schema
const consumableSchema = new mongoose.Schema(
  {
    amount: {
      type: Number,
      required:true
    },
    paid:{
        type:Number,
        required:true
    }
    customer: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "Customer",
      required: true
    },
    consumable : {
       type: mongoose.Schema.Types.ObjectId,
      ref: "Consumable",
      required: true
    }
  }
);

So now if you're supposed to get the sum of the paid amount for a customer, mongoose aggregation pipelines are a great and efficient way to do so.
The way you get the sum is as follows

  • Match the customer id
  • group by id (if id not required just use null)
  • get sum of paid/amount
//query to get the above result
const {customerID} = args;
const result = await Payment.aggregate([
    {
      $match: { customer: customerID }
    },
    {
      $group: {
        _id: null,
        paidTotal: { $sum: "$paid" },
        amountTotal: { $sum: "$amount" }
      }
    }
  ]);
  console.log(result)
  // logs [{ _id: null, amount: 800, paid: 600 }] which is an array of objects.

Now we come to consumables, consider consumables as food items [burger,pizza,fries...etc].
Your customer requires a statistical view of the consumables being sold/consumed daily/monthly/quarterly/yearly.
In that scenario the query would look something like this

const { from, to } = args;

  const result = await Payment.aggregate([
    {
      $match: { createdAt: { $gte: from, $lt: to } }
    },
    {
      $lookup: {
        from: "consumables",
        localField: "consumable",
        foreignField: "_id",
        as: "consumable"
      }
    },
    {
      $unwind: "$consumable"
    },
    {
      $replaceRoot: { newRoot: "$consumable" }
    },
    {
      $group: {
        _id: "$_id",
        name: { $first: "$name" },
        count: { $sum: 1 }
      }
    },
    { $sort: { name: 1 } } // can use count for sorting as well
  ]);
  console.log(result);
  //logs 
  [
      { _id: 5ca5c352ccf2241169f9c8ab, name: 'Burger', count: 30 },
      { _id: 5ca5c367ccf2241169f9c8ad, name: 'Fries', count: 24 },
      { _id: 5ca5c361ccf2241169f9c8ac, name: 'Pizza', count: 15 }
  ]

Here we use $lookup(aggregation) to get the relationship from consumables to payment (one to many).

  • Here "from" indicates the schema that we are referring to, in this case we are referring "Consumable schema from Payment schema".
  • The local field refers to the name of field which we are using to relate to the Consumable schema, which is consumable field in Payments schema
  • The foreign field refers to the field in the schema which we are trying to relate to, which is Consumable Schema, and the field is _id
  • and as can be understood as a constant which stores the result from the relationship query.
    • The result from the lookup aggregation in this case is an array, we deconstruct the array using $unwind(aggregation).
    • We then change the root to $consumable, using the $replaceRoot(aggregation), although this isn't required, but makes the query look prettier. This replaces the root of the query and moves $consumable to the topmost level and replaces all fields. In case you don't want to use $replaceRoot you can always use it like this name: "$$ROOT.consumable.name", check how this compares to the code name: {$first:"$name"}, looks neat.

To conclude, you could always use mongoose aggregation pipeline stages for generating statistics, or any similar situations. An example would be generating statistics for user signups based on time/day/date/month/quarter/year.

Hope you had patience to read all of this :)

Posted on May 9 '19 by:

tushark1 profile

Tushar Khubani

@tushark1

A passionate web developer, keen about learning always.

Discussion

markdown guide