- pre-computed aggregation pipeline
- results are stored on and read from disk
Actually, it simply is a normal collection which is the result of a $merge or $out stage. There is no such thing as a special data structure called Materialized View like MySQL
// create mock data
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'),
},
])
// defined the custom function
updateMonthlySales = function (startDate) {
db.bakesales.aggregate([
{
$match: {
date: {
$gte: startDate,
},
},
},
{
$group: {
// using _id as group condition
_id: {
// extract the time in the ObjectId format
// returns custom format
$dateToString: {
date: '$date',
format: '%Y-%m',
},
},
sales_quantity: {
$sum: '$quantity',
},
sales_amount: {
$sum: '$amount',
},
},
},
{
// writes the output to the 'monthlybacksales' materialized views
$merge: {
into: 'monthlybakesales', // Collection's name
whenMatched: 'replace', // 'fail', 'insert'
on: "_id" // can be omitted since $merge choose _id by default
// $merge checks if the document inside prev pipeline results match an existing document inside collection
// If there is a match (e.g. '2021-01'), the collection documents are overrided with new results.
// If there isn't a match, the stage insert the document from the results into the collection. (if not exists, create the collection first)
},
},
])
}
// initialize first
updateMonthlySales(new ISODate('1970-01-01'))
// checking
db.monthlybakesales.find().sort({ _id: 1 })
// new mock data update
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'),
},
])
// update materialized view
updateMonthlySales(new ISODate('2019-01-01'))
// checking
db.monthlybakesales.find().sort({ _id: 1 })db.salaries.insertMany([
{ '_id': 1, employee: 'Ant', dept: 'A', salary: 100000, fiscal_year: 2017 },
{ '_id': 2, employee: 'Bee', dept: 'A', salary: 120000, fiscal_year: 2017 },
{ '_id': 3, employee: 'Cat', dept: 'Z', salary: 115000, fiscal_year: 2017 },
{ '_id': 4, employee: 'Ant', dept: 'A', salary: 115000, fiscal_year: 2018 },
{ '_id': 5, employee: 'Bee', dept: 'Z', salary: 145000, fiscal_year: 2018 },
{ '_id': 6, employee: 'Cat', dept: 'Z', salary: 135000, fiscal_year: 2018 },
{
'_id': 7,
employee: 'Gecko',
dept: 'A',
salary: 100000,
fiscal_year: 2018,
},
{ '_id': 8, employee: 'Ant', dept: 'A', salary: 125000, fiscal_year: 2019 },
{ '_id': 9, employee: 'Bee', dept: 'Z', salary: 160000, fiscal_year: 2019 },
{ '_id': 10, employee: 'Cat', dept: 'Z', salary: 150000, fiscal_year: 2019 },
])
db.salaries.aggregate([
{
$group: {
_id: {
// multiple group key
fiscal_year: '$fiscal_year',
dept: '$dept',
},
avgSalary: {
$avg: '$salary',
},
},
},
{
$merge: {
// test.budgets is the materialized views
into: {
db: 'test', // could be other than currently use database
coll: 'budgets', // Collection's name
},
on: '_id', // use _id field
whenMatched: 'replace',
whenNotMatched: 'insert', // can be omitted
},
},
])
db.budgets.createIndex({ "_id.fiscal_year": 1 }, { unique: true })
db.budgets.find({ "_id.fiscal_year": 2019 }).explain('executionStats') // IDHACK: _id index