We were working on a project with a 300 documents with currentValue
field in a main collection, in order to track the history of each document of first collection. we created another collection named history
with approximately 6.5 millions of documents.
For each input of system we have to add around 30 history item and update currentValue
field of main collection, so, We tried computational field design pattern for currentValue
, which lead us to have writeConfilict
in concurrent situations (at concurrency of around 1000 requests).
Then we tried to compute currentValue
field with sum
(amount
field) and groupBy
(mainId
field) on history collection which takes too long (> 3s).
Main collection docs:
{
"_id" : ObjectId(...),
"stock" : [
{
"currentAmount" : -313430.0,
"lastPrice" : -10.0,
"storage" : ObjectId("..."),
"alarmCapacity" : 12
},
{
"currentAmount" : 30,
"lastPrice" : 0,
"storage" : ObjectId("..."),
"alarmCapacity" : 12
},
.
.
.
],
"name" : "name",
}
History collection docs:
{
"_id" : ObjectId("..."),
"mainId" : ObjectId("..."),
"amount" : 5,
}
If you have any other idea to handle this situation(application or db level), I would be thankful.
UPDATE 1
The update query if I use computed pattern would be:
mainCollection.findOneAndUpdate(
{
$and: [
{ _id: id },
{ "stock.storage": fromId },
{ "stock.deletedAt": null }
],
},
{
$inc: {
"stock.$.currentAmount": -1 * amount,
}
},
{
session
}
)
And Aggregation pipeline if I want to calculate currentAmount
everytime:
mainCollection.aggregate([
{
$match: {
branch: new ObjectId("...")
}
},
{
$group: {
_id: "$ingredient",
currentAmount: {
$sum: "$amount"
}
}
}])