Hello,
I have 2 collections, Containers and Products . In real life, products are put into containers and there is no exact limit to how many products can be put into containers, so I establish a one to many relationship by having each Product point to the Container document it belongs to:
Sample Product document:
{
_id: <mongo id>,
container: <container document id>,
<.. other product details>
}
A container needs to obtain real time information on the product it stores when queried so I perform the following aggregation query for any specific container:
[$MATCH container _id] => [$LOOKUP products that have the container id] => [$PROJECT data in a needed format]
This query is blazing fast, due to the $match stage.
However, there is a section in the app that needs to list many containers in a table and provide the calculated data as well. Because an unknown amount of containers needs to be listed, I am not able to use the $match stage:
[$LOOKUP products that have the container id] => [$PROJECT data in a needed format]
I realize this is problematic, because it is probably causing to scan all the products in their collection. Since the product amount is constantly growing in the system, this is making the query increasingly slower. Is there a way to optimize the above aggregation query? Maybe it is possible to somehow narrow down the bags (even without knowing their ids at runtime) by using a path variable in the $Lookup or something? Or should I ditch the whole aggregation idea and start storing calculations on the container itself rather than dynamically aggregating the data on them?