Is it possible to add a computed field to an existing collection such that as values change for a record the field is updated?

Working with Data
#1

Hi there -
I can’t seem to find the simple answer to this anywhere. Here’s an example of what Id like to achieve,
Given the following document:

{
          "orderId": "12345678900000",
          "percentage": 0.20,
          "subtotalAmount": 8000,
          "serviceFeeAmount": 1600
        }

I would like the serviceFeeAmount to calculate it’s value
{ $toInt: { $multiply [ '$subtotalAmount', '$percentage'] } }
based on the inserted subtotalAmount & percentage values.

Thanks so much!

#2

Hi @falisse_frazier :wave: - Welcome to the community.

Sounds like you want this value calculated upon insert but please correct me if i’m wrong. If so, is there a reason you are not calculating this beforehand and just inserting the calculated value then? i.e., You have percentage and subtotalAmount values to be inserted, what is the reason for not calculating serviceFeeAmount and inserting all 3 of these fields in 1 go?

However, you’ve also provided the sample document - I would like to clarify if this means that it is already inserted into your database (or at least the percentage and subtotalAmount) and you want to calculate serviceFeeAmount based off this already inserted document? i.e., you just want to update the document (that is already inserted) to have the serviceFeeAmount equal to the multiplication result of subTotalAmount and percentage.

Look forward to hearing from you.

Regards,
Jason

1 Like
#3

Hi there @Jason_Tran
The former is correct and Yes, for security purposes this calculation needs to happen inline. The code above is just a simple, generic example of what I’m trying to achieve. I am looking for an equivalent to Postgresql’s generated columns:

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically).

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Are generated columns possible in MongoDB? Or would I have to create a view?
Thanks so much!

#4

Thanks for clarifying Falisse. You could try creating a view but off the top of my head, if you’re using Atlas you can also consider testing Database Triggers to see if it suits your use case and requirements.

I created a very simple trigger function to run upon insert for demonstration purposes:

exports = function(changeEvent) {
    const fullDocument = changeEvent.fullDocument;
    const collection = context.services.get("Cluster0").db("scores").collection("collection");
    const update = [{
    "$set": {
      "serviceFeeAmount": { 
        "$multiply" : ["$subtotalAmount", "$percentage"] 
        }
      }
    }];
    collection.updateOne({'orderId':fullDocument.orderId}, update);
};

Using mongosh for the inserts (inserted documents don’t have a "serviceFeeAmount" field specified):

DB> db.collection.find({})
/// No documents as of yet
DB> db.collection.insertOne({ "orderId": "12345678900000", "percentage": 0.20, "subtotalAmount": 8000 })
{
  acknowledged: true,
  insertedId: ObjectId("642cec310e4251108de45c1b")
}
DB> db.collection.insertOne({ "orderId": "12345678000001", "percentage": 0.50, "subtotalAmount": 10000})
{
  acknowledged: true,
  insertedId: ObjectId("642cec4b0e4251108de45c1c")
}
/// inserted 2 documents above WITHOUT specifying a "serviceFeeAmount"
DB> db.collection.find({})
[
  {
    _id: ObjectId("642cec310e4251108de45c1b"),
    orderId: '12345678900000',
    percentage: 0.2,
    subtotalAmount: 8000,
    serviceFeeAmount: 1600 /// <--- Now has serviceFeeAmount field
  },
  {
    _id: ObjectId("642cec4b0e4251108de45c1c"),
    orderId: '12345678000001',
    percentage: 0.5,
    subtotalAmount: 10000,
    serviceFeeAmount: 5000 /// <--- Now has serviceFeeAmount field
  }
]

I’ve only done brief testing for several minutes on this so you may want to go over the following documentation if you believe it might suit your use case:

Regards,
Jason

1 Like
#5

I want to add to Jason_Tran’s answer.

Why store a value that is a direct computation of other fields? You may always produce this value using a projection or $set/$addFields. Storing it increases the storage size.

One reason to store such a computed field would be to be able to query it efficiently using an index.

Jason, I am not too familiar with Atlas triggers so I am wondering if doing a $set on a trigger would result in the document being written twice. Once for the initial insert and a second time for the update. With change stream, that would be the case but I am not too sure with triggers.

1 Like
#6

Good point Steve. In terms of the number of writes, I believe triggers work a similar manner to what you’ve described. I.e., A write for the insert and another for the update.

1 Like
#7

Thanks for the clarification.

Personally, then if and only if it is required to store the computed field, I would avoid the double write and compute the field in my data insert API, with the caveat that any insert done without the API would not have the field. Perhaps with schema validation could prohibit that. But my first choice would be to not store the computed field and use $project to do it.

1 Like