如何在AWS IoT核心中使用sql语句解码JSON列表？

Question

在这里显示了我试图在AWS IoT核心规则中解码的有效负载，以将其推送到AWS DynamoDB。

{
    "adr": true,
        "applicationID": "1",
        "applicationName": "IOTAPPS",
        "data": "AANl3AAAQT8AZA==",
        "devEUI": "sadfgxvgsfxgs245",
        "deviceName": "TRON002",
        "fCnt": 0,
        "fPort": 2,
        "object": {
          " A1": "NO",
          "A10": "YES",
          "A11": "YES",
          "A12": "YES",
          "EGHmpID": 222684,
          "SelFFMac": 100,
          "TOTAL_DATA": "100000100111111"
        },
        "rxInfo": [
          {
            "gatewayID": "FDFDFEF5584EDEef",
            "location": {
              "altitude": 0,
              "latitude": 12.66885,
              "longitude": 77.81253
            },
            "name": "IoTGateway",
            "fddi": -94,
            "time": "2019-11-18T09:02:08.895349Z"
          }
        ],
        "txInfo": {
          "dr": 5,
          "frequency": 898678700000
        }
    }

我们试图使用下面的sql语句从'rxInfo‘列表中获取'time’。

SELECT deviceName,(SELECT VALUE time FROM rxInfo) AS time FROM "my/topic" 

我们没有得到预期的答案

Answer 1

试试这个，它是在AWS IoT上测试的。

SELECT (SELECT time fROM rxInfo) AS time FROM "abc/cmd"

要生成此输出，请执行以下操作：

{
  "time": [
    {
      "time": "2019-11-18T09:02:08.895349Z"
    }
  ]
}

干杯,

随机存取存储器