Updating Collection Documents based on another Collection


I need to update one collections documents based on another collections. Say we have two collections products and sellerCreatedProducts.

The products will look something like this:

{"_id":1,“itemName”:“Apple Watch Series 3”, “brand”: ”Apple”, “numberForSale”: ??, “averagePrice”: ??, “highestPrice”: ??, “lowestPrice”: ?? }
{"_id":2,“itemName”:“Fitbit Charge 5”, “brand”: ”Fitbit”, “numberForSale”: ??, “averagePrice”: ??, “highestPrice”: ??, “lowestPrice”: ?? }
{"_id":3,“itemName”:“Garmin 55”, “brand”: ”Garmin”, “numberForSale”: ??, “averagePrice”: ??, “highestPrice”: ??, “lowestPrice”: ?? }

The sellerCreatedProducts will look something like this:

{"_id":1,“productID”:“1”, “price”: 300, “condition”: “New”, “description”: “blah”}
{"_id":2,“productID”:“1”, “price”: 295, “condition”: “New”, “description”: “blah”}
{"_id":3,“productID”:“1”, “price”: 330, “condition”: “New”, “description”: “blah”}
{"_id":4,“productID”:“2”, “price”: 600, “condition”: “New”, “description”: “blah”}

In this example “sellers” will be able to create their own listing which will go into the sellerCreatedProducts collection, and these listings will reference the products collection. There will be many products and there will be many sellerCreatedListings per product.

The question is for each product we want to be able to update the numberForSale, averagePrice, highestPrice, lowestPrice fields. What is the best way to go about doing this? I know that using an aggregation pipeline we can easily get this data but is there a better solution? Perhaps updating each products numberForSale, averagePrice, highestPrice, lowestPrice fields every time a new sellerCreatedListing document is created or maybe something else like changing the structure of the collections?

Aggregation seems like the simplest solution to me but what if we start getting a very large sellerCreatedListing collection and are making a lot of aggregation reads on this collection. The best solution in my mind would be to have these values computed so we could just read the product once and have all of this computed information already there.

Any direction would be greatly appreciated!

Hi @Paul_Junkin ,

Yes it’s sounds like you should consider creating a materialized view or using the $merge aggregation to recompute the needed delta evry time an item is added or periodically for the latest updated data.

Read more here :

Obviously since $merge is an aggregation stage you have a lot of freedom to do any kind of transformation and filtering you want but you will need to find a unique identifier to use for the merge condition so consider what fields do you model to do that…

Let me know if thats not clear