azure - 在 Azure 中使用来自 IOT 中心的数据进行查询时出现问题

Question

尝试从我的物联网设备输入中获取查询中的值。

但我无法获得正确的语法。

如果我在查询中写入以下内容，以从 V 中获取值

SELECT vqts.V
FROM device

我明白了

V
null
null

如果我在查询中写入以下内容

SELECT gatewaydata
FROM device

我明白了

gatewaydata

[{"tag_id":"ra-ftld://driver-ftld/RNA://$Global/EDGE::[CLXEMULATE]N01","model_id":"HELLO.Machine.Mixer","vqts":[{"v":20,"q":192,"t":"2020-11-13T11:25:31.2600000Z"},{"v":60,"q":192,"t":"2020-11-13T11:25:32.7660000Z"}],"mimeType":"x-ra/cip/dint"}]

[{"tag_id":"ra-ftld://driver-ftld/RNA://$Global/EDGE::[CLXEMULATE]N01","model_id":"HELLO.Machine.Mixer","vqts":[{"v":200,"q":192,"t":"2020-11-13T10:59:08.9640000Z"},{"v":400,"q":192,"t":"2020-11-13T10:59:11.9710000Z"}],"mimeType":"x-ra/cip/dint"}]

非常欢迎任何建议

下面是 Raw 中的输入 JSON 预览

[
  {
    "gatewayData": [
      {
        "tag_id": "ra-ftld://driver-ftld/RNA://$Global/EDGE::[CLXEMULATE]N01",
        "model_id": "HELLO.Machine.Mixer",
        "vqts": [
          {
            "v": 20,
            "q": 192,
            "t": "2020-11-13T11:25:31.2600000Z"
          },
          {
            "v": 60,
            "q": 192,
            "t": "2020-11-13T11:25:32.7660000Z"
          }
        ],
        "mimeType": "x-ra/cip/dint"
      }
    ],
    "EventProcessedUtcTime": "2020-11-13T11:50:54.0473698Z",
    "PartitionId": 0,
    "EventEnqueuedUtcTime": "2020-11-13T11:25:35.4010000Z",
    "IoTHub": {
      "MessageId": "cgp-iot-0.6824490650630262",
      "CorrelationId": null,
      "ConnectionDeviceId": "RAEdge",
      "ConnectionDeviceGenerationId": "637408485822041330",
      "EnqueuedTime": "2020-11-13T11:25:35.0000000",
      "StreamId": null
    }
  },
  {
    "gatewayData": [
      {
        "tag_id": "ra-ftld://driver-ftld/RNA://$Global/EDGE::[CLXEMULATE]N01",
        "model_id": "HELLO.Machine.Mixer",
        "vqts": [
          {
            "v": 200,
            "q": 192,
            "t": "2020-11-13T10:59:08.9640000Z"
          },
          {
            "v": 400,
            "q": 192,
            "t": "2020-11-13T10:59:11.9710000Z"
          }
        ],
        "mimeType": "x-ra/cip/dint"
      }
    ],
    "EventProcessedUtcTime": "2020-11-13T11:50:54.0473698Z",
    "PartitionId": 0,
    "EventEnqueuedUtcTime": "2020-11-13T10:59:15.7660000Z",
    "IoTHub": {
      "MessageId": "cgp-iot-0.06258020538786835",
      "CorrelationId": null,
      "ConnectionDeviceId": "RAEdge",
      "ConnectionDeviceGenerationId": "637408485822041330",
      "EnqueuedTime": "2020-11-13T10:59:15.0000000",
      "StreamId": null
    }
  }
]

谢谢kgalic，最后一个解决方案让我大开眼界。再次非常感谢。 :-)

Answer 1

感谢kgalic

您的建议给了我以下结果。

[{"v":300,"q":192,"t":"2020-11-16T08:37:05.1960000Z"}][{"v":600,"q":192,"t":"2020-11-16T08:37:03.1900000Z"}]

但是如果我想得到以下内容，并且没有“Quates”怎么办？就像一个表格，其中 ConnectionDeviceId,tag_id,model_id,v,q,t 作为列

ConnectionDeviceId tag_id model_id v q tRAEdge ftld://driver-ftld/RNA://$Global/EDGE::[CLXEMULATE]N01 HELLO.Machine.Mixer 60 192 2020-11-13T11:25:32.7660000Z