我想根据created_date计算每个月标签Ext Voltage和Battery Voltage的平均值

Question

我是节点js和mongodb的新手，现在我想计算每月ioelements Ext电压和电池电压的平均值，其中日期可以从created_at获取，我已经在下面发布了来自mongodb

{
"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"
}

Answer 1

您希望在当月使用$group(可以使用$month提取(，最后我们可以使用$avg运算符轻松计算值的平均值，如下所示：

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
]
}
}
}
}
])

Mongo游乐场