json - 在 azure 流分析中解析 json 内容

Question

我的 SQL 技能非常有限。请帮忙。

Azure IoT 边缘上的 Modbus 模块以这种格式（来自下载的示例数据）将 JSON 内容返回到流分析作业 -

[
  {
    "PublishTimestamp": "2021-07-28 19:28:15",
    "Content": [
      {
        "HwId": "XY-MOD2-1",
        "Data": [
          {
            "CorrelationId": "DefaultCorrelationId",
            "SourceTimestamp": "2021-07-28 19:28:15",
            "Values": [
              {
                "DisplayName": "Temperature",
                "Address": "30002",
                "Value": "210"
              },
              {
                "DisplayName": "Temperature",
                "Address": "30003",
                "Value": "538"
              }
            ]
          }
        ]
      }
    ],
    "EventProcessedUtcTime": "2021-07-28T20:26:23.9127084Z",
    "PartitionId": 0,
    "EventEnqueuedUtcTime": "2021-07-28T19:28:15.9460000Z",
    "IoTHub": {
      "MessageId": null,
      "CorrelationId": null,
      "ConnectionDeviceId": "rp4linuxedge1",
      "ConnectionDeviceGenerationId": "637630846187016425",
      "EnqueuedTime": "2021-07-28T19:28:15.9550000Z",
      "StreamId": null
    }
  },

我无法弄清楚我应该使用什么 SQL 语法来将其作为输出 -

源时间戳	地址	价值
时间1	30002	210
时间1	30003	538
时间2	30002	215
时间2	30003	540

标签： jsonazure-iot-hubazure-iot-edge

Answer 1

JSON对象中的数组可以使用这个代码块来访问——

select 
    cast(dataArr.ArrayValue.SourceTimestamp as datetime) as SourceTimestamp, 
    cast(valuesArr.ArrayValue.Address as bigint) as Address, 
    cast(valuesArr.ArrayValue.Value as float) as Value

into powerbioutput
from iotinput i
cross apply GetArrayElements(i.Content) as contentArr
cross apply GetArrayElements(contentArr.ArrayValue.Data) as dataArr
cross apply GetArrayElements(dataArr.ArrayValue.[Values]) as valuesArr