Hi I am new to mongodb and trying do some prototyping to see if it is best fit. In short I am trying to create an inventory tracking database. Which tracks the movement In and Out of various products by quantities. In table format it would look like this:
| product_id | product_name | quantity | Notes |
|---|---|---|---|
| 1 | sugar | 100 | |
| 1 | sugar | -100 | |
| 2 | flour | 50 | Jun stock |
| 2 | flour | -50 | Sold to John |
| 3 | water | 100 | Arrived in June |
| 4 | water | 100 | Arrive in July |
So in short when product:
- ADDED to the inventory it is given a POSITIVE quantity.
- REMOVED from inventory is given a NEGATIVE quantity.
MY QUESTION
What is the best way to run a query to work out the stock on site? Meaning summing the product_ids and only displaying matching results where the sum of the quantity is greater than 0?
I've come across group$ can you feed these results into another query eg find to get the matching records?
Also by best way I mean considering there a many records what would scale for a larger record set say 100K-200K of records?
The expected results would be:
| product_id | product_name | quantity | Notes |
|---|---|---|---|
| 3 | water | 100 | Arrived in June |
| 4 | water | 100 | Arrive in July |
ie flour and sugar sum to 0 so they are not displayed. But water there is still a sum quantity above 0 onsite so they will be displayed.
I've setup a playgroud of the data set: https://mongoplayground.net/p/zzPX8Da09SB
Also data object below. Thanks you very much for any tips or best approach.
[
{
"product_id": 1,
"product_name": "Sugar",
"quantity": 100
},
{
"product_id": 1,
"product_name": "Sugar",
"quantity": -100
},
{
"product_id": 3,
"product_name": "Flour",
"quantity": 50
},
{
"product_id": 3,
"product_name": "Flour",
"quantity": -50
},
{
"product_id": 4,
"product_name": "Water",
"quantity": 100
},
{
"product_id": 5,
"product_name": "Water",
"quantity": 100
}
]