Hi,
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!