On-Demand Materialized Views

Indexes

Standard views use the indexes of the underlying collection. As a result, you cannot create, drop or re-build indexes on a standard view directly, nor get a list of indexes on the view.

You can create indexes directly on on-demand materialized views because they are stored on disk.

Performance

On-demand materialized views provide better read performance than standard views because they are read from disk instead of computed as part of the query. This performance benefit increases based on the complexity of the pipeline and size of the data being aggregated.

1. Define the On-Demand Materialized View

The following updateMonthlySales function defines a monthlybakesales materialized view that contains the cumulative monthly sales information. In the example, the function takes a date parameter to only update monthly sales information starting from a particular date.

updateMonthlySales = function(startDate) {
   db.bakesales.aggregate( [
      { $match: { date: { $gte: startDate } } },
      { $group: { _id: { $dateToString: { format: "%Y-%m", date: "$date" } }, sales_quantity: { $sum: "$quantity"}, sales_amount: { $sum: "$amount" } } },
      { $merge: { into: "monthlybakesales", whenMatched: "replace" } }
   ] );
};

• The $match stage filters the data to process only those sales greater than or equal to the startDate.

• The $group stage groups the sales information by the year-month. The documents output by this stage have the form:

  { "_id" : "<YYYY-mm>", "sales_quantity" : <num>, "sales_amount" : <NumberDecimal> }

• The $merge stage writes the output to the monthlybakesales collection.

  Based on the _id field (the default for unsharded output collections), the stage checks if the document in the aggregation results matches an existing document in the collection:

  • When there is a match (i.e. a document with the same year-month already exists in the collection), the stage replaces the existing document with the document from the aggregation results.

  • When there is not a match, the stage inserts the document from the aggregation results into the collection (the default behavior when not matched).

2. Perform Initial Run

For the initial run, you can pass in a date of new ISODate("1970-01-01"):

updateMonthlySales(new ISODate("1970-01-01"));

After the initial run, the monthlybakesales contains the following documents; i.e. db.monthlybakesales.find().sort( { _id: 1 } ) returns the following:

{ "_id" : "2018-12", "sales_quantity" : 41, "sales_amount" : NumberDecimal("506") }
{ "_id" : "2019-01", "sales_quantity" : 86, "sales_amount" : NumberDecimal("896") }

3. Refresh Materialized View

Assume that by the first week in February 2019, the bakesales collection is updated with newer sales information; specifically, additional January and February sales.

db.bakesales.insertMany( [
   { date: new ISODate("2019-01-28"), item: "Cake - Chocolate", quantity: 3, amount: new NumberDecimal("90") },
   { date: new ISODate("2019-01-28"), item: "Cake - Peanut Butter", quantity: 2, amount: new NumberDecimal("32") },
   { date: new ISODate("2019-01-30"), item: "Cake - Red Velvet", quantity: 1, amount: new NumberDecimal("20") },
   { date: new ISODate("2019-01-30"), item: "Cookies - Chocolate Chip", quantity: 6, amount: new NumberDecimal("24") },
   { date: new ISODate("2019-01-31"), item: "Pie - Key Lime", quantity: 2, amount: new NumberDecimal("40") },
   { date: new ISODate("2019-01-31"), item: "Pie - Banana Cream", quantity: 2, amount: new NumberDecimal("40") },
   { date: new ISODate("2019-02-01"), item: "Cake - Red Velvet", quantity: 5, amount: new NumberDecimal("100") },
   { date: new ISODate("2019-02-01"), item: "Tarts - Apple", quantity: 2, amount: new NumberDecimal("8") },
   { date: new ISODate("2019-02-02"), item: "Cake - Chocolate", quantity: 2, amount: new NumberDecimal("60") },
   { date: new ISODate("2019-02-02"), item: "Cake - Peanut Butter", quantity: 1, amount: new NumberDecimal("16") },
   { date: new ISODate("2019-02-03"), item: "Cake - Red Velvet", quantity: 5, amount: new NumberDecimal("100") }
] )

To refresh the monthlybakesales data for January and February, run the function again to rerun the aggregation pipeline, starting with new ISODate("2019-01-01").

updateMonthlySales(new ISODate("2019-01-01"));

The content of monthlybakesales has been updated to reflect the most recent data in the bakesales collection; i.e. db.monthlybakesales.find().sort( { _id: 1 } ) returns the following:

{ "_id" : "2018-12", "sales_quantity" : 41, "sales_amount" : NumberDecimal("506") }
{ "_id" : "2019-01", "sales_quantity" : 102, "sales_amount" : NumberDecimal("1142") }
{ "_id" : "2019-02", "sales_quantity" : 15, "sales_amount" : NumberDecimal("284") }