从复杂的MQTT消息获取传感器值 - 嵌套对象查询

发布于 2025-01-24 12:26:27 字数 1161 浏览 3 评论 0原文

我需要获取温度和湿度值，并将它们存储在AWS端式表作为值中，以便我可以在Grafana中使用它们。

如何以可用的格式而不是字符串数组来检索这些数字值？我尝试仅取得没有成功的温度检索：

SELECT (SELECT temperature FROM data.value.device_list) as temp FROM   MyAWSTopic

接收到的MQTT有效载荷如下：

{
  "time": 1651066698,
  "mac": "30:ae:7b:e2:23:72",
  "from": "BLE",
  "to": "GATEWAY",
  "data": {
    "attribute": "mod.device_list",
    "mac": "20:ae:7c:e2:28:72",
    "value": {
      "device_list": [
        {
          "modelstr": "MDSensor",
          "sensortype": "Temperature_Humdity",
          "connectable": 1,
          "ble_addr": "E5:02:C5:79:F2:C4",
          "temperature": "26.392",
          "scan_rssi": -35,
          "addr_type": 1,
          "scan_time": 1651064693,
          "dev_name": "DBeacon",
          "data": "02010608FF5904555B454360573756E426561636F6E",
          "humidity": "35.119",
          "connect": 0
        }
      ]
    }
  },
  "deviceCode": "aws_device_code",
  "type": "reportAttribute"
  }

这是我得到的结果：[{{“温度”：“ 26.073”}，{}，{}，{}，{}，{}]

生成的表

原文

I need to get the temperature and humidity values and store them into a AWS timestream table as values, so that i can use them in Grafana.

How could i retrieve those numbers-values in a usable format instead of string array?
I tried to retrieve only temperature with no success:

SELECT (SELECT temperature FROM data.value.device_list) as temp FROM   MyAWSTopic

The received MQTT payload is the following:

{
  "time": 1651066698,
  "mac": "30:ae:7b:e2:23:72",
  "from": "BLE",
  "to": "GATEWAY",
  "data": {
    "attribute": "mod.device_list",
    "mac": "20:ae:7c:e2:28:72",
    "value": {
      "device_list": [
        {
          "modelstr": "MDSensor",
          "sensortype": "Temperature_Humdity",
          "connectable": 1,
          "ble_addr": "E5:02:C5:79:F2:C4",
          "temperature": "26.392",
          "scan_rssi": -35,
          "addr_type": 1,
          "scan_time": 1651064693,
          "dev_name": "DBeacon",
          "data": "02010608FF5904555B454360573756E426561636F6E",
          "humidity": "35.119",
          "connect": 0
        }
      ]
    }
  },
  "deviceCode": "aws_device_code",
  "type": "reportAttribute"
  }

And this is the result i get : [{"temperature":"26.073"},{},{},{}]

Generated table

分享到QQ

分享到微博