我想按created_date计算每个月标签输出电压和电池电压的平均值，

Question

我是节点js和mongodb的新手，现在我想要计算每个月的碘元素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
          ]
        }
      }
    }
  }
])

蒙戈游乐场