'Sum of the values in nested dicts excluding some based on other key in MongoDB database
I have a MongoDB database with the following format:
{
"_id": xxx,
"timestamp": "1643649900000",
"scores":
[{
"name": "APPL",
"price": 80
},
{
"name": "GOOGL",
"price": 83,
},
{
"name": "COMPI",
"price": 76,
},
{
"name": "and more names which also can change in the following documents",
"price": 76,
}]
},
{
"_id": yyy,
"time": "1644350400000",
"scores":
[{
"name": "STCMP",
"price": 33
},
{
"name": "APPL",
"price": 95,
},
{
"name": "GOOGL",
"price": 83,
},
{
"name": "MINN",
"price": 76,
}]
},
I need to sum all prices per time but excluding (or subtract from the sum) some.
My scores list has around 200 dicts and I want to exclude around 5 of them. I only managed to do the summing part, but after two days of search still can't manage to exclude with my limited knowledge.
toBeExcluded = ["APPL", "GOOGL"]
sums.aggregate([
{
"$unwind" : "$scores"
},
{
"$group": {
"_id": "$time",
"total": {
"$sum": "$scores.price"
}
}
},
{
"$addFields":{
"timeAdj": {"$toInt": [{"$subtract":[{"$divide": ["$_id", 1000]}, 300]}]}
}
},
{
"$sort": {"timeAdj":1}
}
]))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
