On-Demand Materialized Views

On this page

Create a Materialized View in the MongoDB Atlas UI

Example
Additional Information

MongoDB 5.0 is end of life as of October 2024. This version of the documentation is no longer supported. To upgrade your 5.0 deployment, see the MongoDB 6.0 upgrade procedures.

Note

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

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

Create a Materialized View in the MongoDB Atlas UI

The example in this section uses the sample training dataset. To learn how to load the sample dataset into your MongoDB Atlas deployment, see Load Sample Data.

To create a materialized view in the MongoDB Atlas UI, follow these steps:

In the MongoDB Atlas UI, go to the Clusters page for your project.

If it's not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
If it's not already displayed, click Clusters in the sidebar.
The Clusters page displays.

Navigate to the collection

For the cluster that contains the sample data, click Browse Collections.
In the left navigation pane, select the sample_training database.
Select the grades collection.

Click the Aggregation tab

Click Add Stage

Select an aggregation stage from the Select drop-down menu

The aggregation stage transforms the data that you want to save as a view. To learn more about available aggregation stages, see Aggregation Pipeline Quick Reference.

For this example, add a new field with the $set stage:

Select $set from the Select drop-down menu.
Add the following syntax to the aggregation pipeline editor to create an average score across all score values in the scores array within the grades collection:
```
{
  averageScore: { $avg: "$scores.score" }
}
```
MongoDB Atlas adds the averageScore value to each document.

Click Add Stage

Add the `$out` stage

Select the $out stage from the Select drop-down menu.
Add the following syntax to the aggregation pipeline to write the results of the pipeline to the myView collection in the sample_training database:
```
'myView'
```
Click Save Documents.

The $out stage writes the results of the aggregation pipeline to the specified collection, which creates the view. To learn more, see $out.

Refresh the list of collections to see the myView collection.

To learn how to query the myView collection in the MongoDB Atlas UI, see View, Filter, and Sort Documents in the MongoDB Atlas documentation.

Example

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

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

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") }

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 already exist.
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 existing collection.
Can output to a sharded collection. Input collection can also be sharded.

See $merge for:

More information on $merge and available options
Example: On-Demand Materialized View: Initial Creation
Example: On-Demand Materialized View: Update/Replace Data
Example: Only Insert New Data

Back

Views

Join Collections

Note

Create a Materialized View in the MongoDB Atlas UI

In the MongoDB Atlas UI, go to the .css-h15tq0{font-style:normal;font-weight:700;}Clusters page for your project.

Navigate to the collection

Click the Aggregation tab

Click Add Stage

Select an aggregation stage from the Select drop-down menu

Click Add Stage

Add the $out stage

Example

1. Define the On-Demand Materialized View

2. Perform Initial Run

3. Refresh Materialized View

Additional Information

In the MongoDB Atlas UI, go to the Clusters page for your project.

Add the `$out` stage