I'm new in node js and mongodb and now i want to calculate ioelements Ext voltage and battery voltage average value per month where the date can be taken from created_at, i've posted the sample data coming from mongodb below
{
"recieved_at": "2022-01-05T03:28:07.816Z",
"data": {
"timestamp": "2022-01-05T03:28:05.000Z",
"priority": 0,
"gps": {
"longitude": 8.7472616,
"latitude": 50.0881766,
"altitude": 171,
"angle": 179,
"satellites": 5,
"speed": 6
},
"event_id": 0,
"properties_count": 6,
"ioElements": [{
"id": 239,
"value": 0,
"label": "Ignition",
"valueHuman": "No"
},
{
"id": 240,
"value": 0,
"label": "Movement",
"valueHuman": "No"
},
{
"id": 66,
"value": 12064,
"label": "Ext Voltage",
"dimension": "mV",
"valueHuman": ""
},
{
"id": 24,
"value": 6,
"label": "Speed",
"dimension": "km/h",
"valueHuman": ""
},
{
"id": 67,
"value": 4081,
"label": "Battery Voltage",
"dimension": "mV",
"valueHuman": ""
},
{
"id": 199,
"value": 6,
"label": "Trip Odometer",
"valueHuman": ""
}
]
},
"imei": "357073294061474",
"deviceTcpConnection": {},
"deviceUdpConnection": {
"address": "185.116.158.53",
"port": 10190
},
"deviceDetail": true,
"protocol": "UDP",
"created_at" => "2022-01-13T08:55:47.984Z"
}
You want to be using $group on the month ( that you can extract using $month), lastly we can use the $avg operator to calculate the average of values with ease, like so:
db.collection.aggregate([
{
$addFields: {
extVoltage: {
"$arrayElemAt": [
{
$filter: {
input: "$data.ioElements",
as: "elem",
cond: {
$eq: [
"$$elem.label",
"Ext Voltage"
]
}
}
},
0
]
},
batteryVoltage: {
"$arrayElemAt": [
{
$filter: {
input: "$data.ioElements",
as: "elem",
cond: {
$eq: [
"$$elem.label",
"Battery Voltage"
]
}
}
},
0
]
}
}
},
{
$group: {
_id: {
year: {
"$year": "$created_at"
},
month: {
"$month": "$created_at"
}
},
ExtVoltage: {
$avg: {
$ifNull: [
"$extVoltage.value",
0
]
}
},
BatteryVoltage: {
$avg: {
$ifNull: [
"$batteryVoltage.value",
0
]
}
}
}
}
])