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.
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_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!
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:
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.
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.