azure - 流分析 - 处理没有数组名称的 JSON

Question

带有 modbus 模块的 IoT Edge v2 将数据发送到 IoT 中心，格式为:

[
{
    "DisplayName": "Voltage",
    "HwId": "",
    "Address": "400001",
    "Value": "200",
    "SourceTimestamp": "2019-01-03 23:40:24"
},
{
    "DisplayName": "Voltage",
    "HwId": "",
    "Address": "400002",
    "Value": "24503",
    "SourceTimestamp": "2019-01-03 23:40:24"
},
...
]

我想使用包含CROSS APPLY GetArrayElements()的流分析查询将此数组转换为行，但此函数需要数组名称。显然没有名字。有什么建议吗？

https://learn.microsoft.com/en-us/stream-analytics-query/getarrayelements-azure-stream-analytics https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parsing-json

Answer 1

是的，它需要一个数组名称。 CROSS APPLY GetArrayElements() 用于嵌套数组。

示例:

[{
      "source": "xda",
      "data": 
        [{
            "masterTag": "UNIFY1",
            "speed": 180
        },
        {
            "masterTag": "UNIFY2",
            "speed": 180
        }],
      "EventEnqueuedUtcTime": "2018-07-20T19:28:18.5230000Z",
  },
  {
      "source": "xda",
      "data": [{
            "masterTag": "UNIFY3",
            "speed": 214
        },
        {
            "masterTag": "UNIFY4",
            "speed": 180
        }],
      "EventEnqueuedUtcTime": "2018-07-20T19:28:20.5550000Z",
  }
]

您可以使用下面的sql将其转换为行:

SELECT 
    jsoninput.source, 
    arrayElement.ArrayValue.masterTag
INTO 
    output
FROM jsoninput
CROSS APPLY GetArrayElements(jsoninput.data) AS arrayElement 

但是，现在您提供的输入数据是一个纯数组。如果你想将此数组转换为行，只需使用sql:

select jsoninput.* from jsoninput