Explanation: I want to find the value_avg, time_avg , value_min, time_min, value_max, time_max. In time_values we have objects, time and value field, first we have to $match the time_values with data.time_start and data.time_end.
second step could be in between time range, that will $match, we will find the value_avg, time_avg , value_min, time_min, value_max, time_max . please see the expected output.
In Expected output remaining_time_values will those elements that will $match.
If you have any questions let me know, please. Thanks in advance.
{
"data": {
"time_start": "2021-09-09T09:17:01.891Z",
"time_end": "2021-09-09T09:17:11.091Z",
"value_start": 142,
"value_end": 1384
},
"time_values": [
{
"time": "2021-09-09T09:17:01.491Z",
"value": 0
},
{
"time": "2021-09-09T09:17:01.691Z",
"value": 10
},
{
"time": "2021-09-09T09:17:01.891Z",
"value": 142
},
{
"time": "2021-09-09T09:17:02.091Z",
"value": 479
},
{
"time": "2021-09-09T09:17:02.291Z",
"value": 1166
},
{
"time": "2021-09-09T09:17:02.491Z",
"value": 1430
},
{
"time": "2021-09-09T09:17:02.691Z",
"value": 1089
},
{
"time": "2021-09-09T09:17:02.891Z",
"value": 759
},
{
"time": "2021-09-09T09:17:03.091Z",
"value": 896
},
{
"time": "2021-09-09T09:17:03.291Z",
"value": 1331
},
{
"time": "2021-09-09T09:17:03.491Z",
"value": 1384
},
{
"time": "2021-09-09T09:17:11.091Z",
"value": 1384
},
{
"time": "2021-09-09T09:17:11.291Z",
"value": 0
},
{
"time": "2021-09-09T09:17:21.095Z",
"value": 0
},
{
"time": "2021-09-09T09:17:21.300Z",
"value": 0
},
{
"time": "2021-09-09T09:17:23.695Z",
"value": 0
},
{
"time": "2021-09-09T09:17:23.895Z",
"value": 270
},
{
"time": "2021-09-09T09:17:24.095Z",
"value": 492
},
{
"time": "2021-09-09T09:17:24.295Z",
"value": 603
},
{
"time": "2021-09-09T09:17:24.495Z",
"value": 769
},
{
"time": "2021-09-09T09:17:24.701Z",
"value": 851
},
{
"time": "2021-09-09T09:17:24.895Z",
"value": 938
},
{
"time": "2021-09-09T09:17:25.095Z",
"value": 1120
},
{
"time": "2021-09-09T09:17:25.299Z",
"value": 1123
},
{
"time": "2021-09-09T09:17:25.495Z",
"value": 880
},
{
"time": "2021-09-09T09:17:25.695Z",
"value": 640
},
{
"time": "2021-09-09T09:17:25.895Z",
"value": 630
},
{
"time": "2021-09-09T09:17:26.095Z",
"value": 645
},
{
"time": "2021-09-09T09:17:35.896Z",
"value": 645
},
{
"time": "2021-09-09T09:17:36.106Z",
"value": 0
},
{
"time": "2021-09-09T09:17:49.097Z",
"value": 0
},
{
"time": "2021-09-09T09:17:49.295Z",
"value": 464
},
{
"time": "2021-09-09T09:17:49.495Z",
"value": 1154
},
{
"time": "2021-09-09T09:17:49.695Z",
"value": 1548
},
{
"time": "2021-09-09T09:17:49.895Z",
"value": 1479
},
{
"time": "2021-09-09T09:17:50.095Z",
"value": 1562
},
{
"time": "2021-09-09T09:17:50.295Z",
"value": 1731
},
{
"time": "2021-09-09T09:17:50.496Z",
"value": 1897
},
{
"time": "2021-09-09T09:17:50.695Z",
"value": 1976
},
{
"time": "2021-09-09T09:17:50.895Z",
"value": 1922
},
{
"time": "2021-09-09T09:17:51.095Z",
"value": 1721
},
{
"time": "2021-09-09T09:17:51.296Z",
"value": 1336
},
{
"time": "2021-09-09T09:17:51.525Z",
"value": 951
},
{
"time": "2021-09-09T09:17:51.695Z",
"value": 772
},
{
"time": "2021-09-09T09:17:51.895Z",
"value": 1008
},
{
"time": "2021-09-09T09:17:52.095Z",
"value": 1417
},
{
"time": "2021-09-09T09:17:59.095Z",
"value": 1417
}
]
}
Expected Output Document.
{
"data": {
"time_start": "2021-09-09T09:17:01.891Z",
"time_end": "2021-09-09T09:17:11.091Z",
"value_start": 142,
"value_end": 1384,
"value_avg" :"??",
"time_min" : "??",
"value_min" : "??",
"time_max" : "??",
"value_max" : "??"
},
"remaining_time_values": [
{
"time": "2021-09-09T09:17:01.891Z",
"value": 142
},
{
"time": "2021-09-09T09:17:02.091Z",
"value": 479
},
{
"time": "2021-09-09T09:17:02.291Z",
"value": 1166
},
{
"time": "2021-09-09T09:17:02.491Z",
"value": 1430
},
{
"time": "2021-09-09T09:17:02.691Z",
"value": 1089
},
{
"time": "2021-09-09T09:17:02.891Z",
"value": 759
},
{
"time": "2021-09-09T09:17:03.091Z",
"value": 896
},
{
"time": "2021-09-09T09:17:03.291Z",
"value": 1331
},
{
"time": "2021-09-09T09:17:03.491Z",
"value": 1384
},
{
"time": "2021-09-09T09:17:11.091Z",
"value": 1384
}
]
}
CodePudding user response：
You can use an aggregation query like this:
- First
$unwindto deconstruct the array and operate for each value. - I have used
$setto parse string to Date, but if your data is yet a Date object is not necessary this step. - Then
$matchto compare between desired range (I think this is the match you want but if you want to use$gtor$ltinstead of$gteand$lteyou can change this. - And
$groupto reconstruct the array using accumulation operators like$avg,$maxand$minto get desired values.
db.collection.aggregate([
{
"$unwind": "$time_values"
},
{
"$set": {
"data.time_start": {
"$toDate": "$data.time_start"
},
"data.time_end": {
"$toDate": "$data.time_end"
},
"time_values.time": {
"$toDate": "$time_values.time"
}
}
},
{
"$match": {
"$expr": {
"$and": [
{
"$lte": [
"$data.time_start",
"$time_values.time"
]
},
{
"$gte": [
"$data.time_end",
"$time_values.time"
]
}
]
}
}
},
{
"$group": {
"_id": "$_id",
"time_start": {
"$first": "$data.time_start"
},
"time_end": {
"$first": "$data.time_end"
},
"value_start": {
"$first": "$data.value_start"
},
"value_end": {
"$first": "$data.value_end"
},
"remaining_time_values": {
"$push": "$time_values"
},
"value_avg": {
"$avg": "$time_values.value"
},
"time_min": {
"$min": "$time_values.time"
},
"value_min": {
"$min": "$time_values.value"
},
"time_max": {
"$max": "$time_values.time"
},
"value_max": {
"$max": "$time_values.value"
}
}
}
])
Example here