Slowly Changing Dimensions渐变维度

Example示例

Consider a collection called prices that stores the prices of a set of items. You need to keep track of the changes of the price of an item over time in order to be able to process returns of an item, as the money refunded must match the price of the item at the time of purchase. Each document in the collection has an item and price field:

db.prices.insertMany( [
   { 'item': 'shorts', 'price': 10 },
   { 'item': 't-shirt', 'price': 2 },
   { 'item': 'pants', 'price': 5 },
] )

Suppose the price of pants changes from 5 to 7. To track this price change, assume the default values for the necessary data fields for SCD Type 2. The default value for validFrom is 01.01.1900, validTo is 01.01.9999, and isValid is true. To change the price field in the object with 'item': 'pants', insert a new document to represent the current state of the pants, and update the previously valid document to no longer be valid:

let now = new Date();

db.prices.updateOne(
   {
      'item': 'pants',
      "$or": [
         { "isValid": false },
         { "isValid": null }
      ]
   },
   { "$set":
      {
         "validFrom": new Date("1900-01-01"),
         "validTo": now,
         "isValid": false
      }
   }
);

db.prices.insertOne(
   {
      'item': 'pants',
      'price': 7,
      "validFrom": now,
      "validTo": new Date("9999-01-01"),
      "isValid": true
   }
);

To avoid breaking the chain of validity, ensure that both of the above database operation occur at the same timestamp. Depending on the requirements of the application, you can wrap the two above commands into a transaction to ensure MongoDB always applies both changes together. For more information, see Transactions.

The following operation demonstrates how to query the latest price of the document containing the pants item:

db.prices.find( { 'item': 'pants', 'isValid': true } );

To query for the price of the document containing the pants item at a specific point in time, use the following operation:

let time = new Date("2022-11-16T13:00:00");
db.prices.find( {
   'item': 'pants',
   'validFrom': { '$lte': time },
   'validTo': { '$gt': time }
} );

Tracking Changes in Few Fields

If you only need to track changes over time to few fields in a document, you can use SCD type 3 by embedding the history of a field as an array in the first document.

For example, the following aggregation pipeline updates the price in the document representing pants to 7 and stores the previous value of the price with a timestamp of when the previous price became invalid in an array called priceHistory:

db.prices.aggregate( [
   { $match: { 'item': 'pants' } },
   { $addFields:
      { price: 7, priceHistory:
         { $concatArrays:
            [
               { $ifNull: [ '$priceHistory', [] ] },
               [ { price: "$price", time: now } ]
            ]
         }
      }
   },
   { $merge:
      {
         into: "prices",
         on: "_id",
         whenMatched: "merge",
         whenNotMatched: "fail"
      }
   }
] )

This solution can become slow or inefficient if your array size gets too large. To avoid large arrays, you can use the outlier or the bucket patterns to design your schema.