Current data:
[
{
"_id": "6239bd7a465f8d1fbfc80f95",
"tag_id": "620e23a6450b9ea1a1e31d75",
"time_stamp": "2022-02-28T19:55:55.666000",
"is_deleted": false,
"value": 20
},
{
"_id": "623ef6e4cd717dc11aee942b",
"tag_id": "620e23a6450b9ea1a1e31d75",
"time_stamp": "2022-03-01T20:55:55.666000",
"is_deleted": false,
"value": 20
},
{
"_id": "623ef6f0cd717dc11aee94f6",
"company_id_rdb": 3,
"tag_id": "620e23a6450b9ea1a1e31d75",
"time_stamp": "2022-03-02T21:55:55.666000",
"is_deleted": false,
"value": 20
},
{
"_id": "623ef718cd717dc11aee97d9",
"company_id_rdb": 3,
"tag_id": "620e23a6450b9ea1a1e31da6",
"time_stamp": "2022-03-02T19:55:55.666000",
"is_deleted": false,
"value": 20
},
{
"_id": "623ef722cd717dc11aee9895",
"tag_id": "620e23a6450b9ea1a1e31da6",
"time_stamp": "2022-03-03T20:55:55.666000",
"is_deleted": false,
"value": 20
},
{
"_id": "623ef72ccd717dc11aee9943",,
"tag_id": "620e23a6450b9ea1a1e31da6",
"time_stamp": "2022-03-04T21:55:55.666000",
"is_deleted": false,
"value": 20
}
]
The final object should be like this:
{
"time_stamp": "2022-03-04T21:55:55.666000",
"value": 60,
"tags_count":2
},
{
"time_stamp": "2022-02-28T19:55:55.666000",
"tags_count":2,
"value": 60
},
{
"time_stamp": "2022-03-03T20:55:55.666000",
"tags_count":2,
"value": 60
},
I tried the below query but had no luck:
db.getCollection('data').aggregate([
{$match:{"tag_id":{$in:[ObjectId("620e23a6450b9ea1a1e31da6"),ObjectId("620e23a6450b9ea1a1e31d75")]}}},
{$group:{"_id":"$time_stamp","value":{$sum:"$value"},"count":{$sum:1}}}
])
I have a collection that has records in it at a difference of 10 mins each for different tags(tag_id), and a value for each tag. I want to write a query in which I pass a list of tags let's say t1 and t2 and a time range (since $ until), and the query should return the sum of the value of the tags passed at every timestamp coming in that range.
CodePudding user response:
It looks you are almost there , maybe something like this:
db.collection.aggregate([
{
$match: {
"tag_id": {
"$in": [
"620e23a6450b9ea1a1e31da6",
"620e23a6450b9ea1a1e31d75"
]
},
"time_stamp": {
"$gte": "2022-02-28T19:55:55.666000",
"$lte": "2022-03-03T20:55:55.666000"
}
}
},
{
$group: {
"_id": "$time_stamp",
"value": {
$sum: "$value"
},
"count": {
$sum: 1
}
}
}
])
Explained:
- Match by tag_id and time_stamp range
- Group by the time_stamp and sum values count