On-Demand Materialized Views

Note

The following page discusses on-demand materialized views. Fordiscussion of views, see Views instead.

Starting in version 4.2, MongoDB adds the $merge stage forthe aggregation pipeline. Thisstage can merge the pipeline results to an existing collection insteadof completely replacing the collection. This functionality allows usersto create on-demand materialized views, where the content of the outputcollection can be updated each time the pipeline is run.

Example

Assume near the end of January 2019, the collection bakesalescontains the sales information by items:

  1. db.bakesales.insertMany( [
  2.    { date: new ISODate("2018-12-01"), item: "Cake - Chocolate", quantity: 2, amount: new NumberDecimal("60") },
  3.    { date: new ISODate("2018-12-02"), item: "Cake - Peanut Butter", quantity: 5, amount: new NumberDecimal("90") },
  4.    { date: new ISODate("2018-12-02"), item: "Cake - Red Velvet", quantity: 10, amount: new NumberDecimal("200") },
  5.    { date: new ISODate("2018-12-04"), item: "Cookies - Chocolate Chip", quantity: 20, amount: new NumberDecimal("80") },
  6.    { date: new ISODate("2018-12-04"), item: "Cake - Peanut Butter", quantity: 1, amount: new NumberDecimal("16") },
  7.    { date: new ISODate("2018-12-05"), item: "Pie - Key Lime", quantity: 3, amount: new NumberDecimal("60") },
  8.    { date: new ISODate("2019-01-25"), item: "Cake - Chocolate", quantity: 2, amount: new NumberDecimal("60") },
  9.    { date: new ISODate("2019-01-25"), item: "Cake - Peanut Butter", quantity: 1, amount: new NumberDecimal("16") },
  10.    { date: new ISODate("2019-01-26"), item: "Cake - Red Velvet", quantity: 5, amount: new NumberDecimal("100") },
  11.    { date: new ISODate("2019-01-26"), item: "Cookies - Chocolate Chip", quantity: 12, amount: new NumberDecimal("48") },
  12.    { date: new ISODate("2019-01-26"), item: "Cake - Carrot", quantity: 2, amount: new NumberDecimal("36") },
  13.    { date: new ISODate("2019-01-26"), item: "Cake - Red Velvet", quantity: 5, amount: new NumberDecimal("100") },
  14.    { date: new ISODate("2019-01-27"), item: "Pie - Chocolate Cream", quantity: 1, amount: new NumberDecimal("20") },
  15.    { date: new ISODate("2019-01-27"), item: "Cake - Peanut Butter", quantity: 5, amount: new NumberDecimal("80") },
  16.    { date: new ISODate("2019-01-27"), item: "Tarts - Apple", quantity: 3, amount: new NumberDecimal("12") },
  17.    { date: new ISODate("2019-01-27"), item: "Cookies - Chocolate Chip", quantity: 12, amount: new NumberDecimal("48") },
  18.    { date: new ISODate("2019-01-27"), item: "Cake - Carrot", quantity: 5, amount: new NumberDecimal("36") },
  19.    { date: new ISODate("2019-01-27"), item: "Cake - Red Velvet", quantity: 5, amount: new NumberDecimal("100") },
  20.    { date: new ISODate("2019-01-28"), item: "Cookies - Chocolate Chip", quantity: 20, amount: new NumberDecimal("80") },
  21.    { date: new ISODate("2019-01-28"), item: "Pie - Key Lime", quantity: 3, amount: new NumberDecimal("60") },
  22.    { date: new ISODate("2019-01-28"), item: "Cake - Red Velvet", quantity: 5, amount: new NumberDecimal("100") },
  23. ] );

1. Define the On-Demand Materialized View

The following updateMonthlySales function defines amonthlybakesales materialized view that contains the cumulativemonthly sales information. In the example, the function takes a dateparameter to only update monthly sales information starting from aparticular date.

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

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

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

  1. { "_id" : "<YYYY-mm>", "sales_quantity" : <num>, "sales_amount" : <NumberDecimal> }
  • The $merge stage writes the output to themonthlybakesales collection.

Based on the _id field (the default forunsharded output collections), the stage checks if the document inthe aggregation results matches anexisting document in the collection:

2. Perform Initial Run

For the intial run, you can pass in a date of newISODate("1970-01-01"):

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

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

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

3. Refresh Materialized View

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

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

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

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

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

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

Additional Information

The $merge stage:

  • Can output to a collection in the same or different database.
  • Creates a new collection if the output collection does not alreadyexist.
  • Can incorporate results (insert new documents, merge documents,replace documents, keep existing documents, fail the operation,process documents with a custom update pipeline) into an existingcollection.
  • Can output to a sharded collection. Input collection canalso be sharded.

See $merge for: