azure-eventhub - 流分析 - 处理没有数组名称的 JSON
问题描述
我想使用包含 CROSS APPLY GetArrayElements() 的流分析查询将此数组转换为行,但此函数需要数组名称。显然没有名字。有什么建议么?
要解析的 JSON 示例:
{
"Alert1": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Load Not Protected",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "2",
"Status": "Open"
},
"Alert2": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Emergency stop",
"Time": "08-28-2019 10:39:02",
"Value": "1",
"Threshold": 1,
"Severity": "2",
"Status": "Open"
},
"Alert3": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Load Protected(UPS Coupled)",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "1",
"Status": "Open"
},
"Alert4": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Battery Deep Discharge Protection",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "1",
"Status": "Open"
},
"Alert5": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Battery Present",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "1",
"Status": "Open"
},
"Alert6": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Sensor Communication Error",
"Time": "08-28-2019 10:39:02",
"Status": "Close"
}
}
我尝试了下面的代码,但遇到了错误:
错误:列名无效:'arrayvalue'。具有该名称的列不存在。
SELECT message.ArrayValue
FROM INPUT as event
CROSS APPLY GetRecordProperties(event) AS message
解决方案
你的方向是对的,但是,我认为你在GetRecordProperties
函数的使用上犯了一个小错误。
请看官方文档中的例子ArrayValue
,不是任何属性:
SELECT
recordProperty.PropertyName,
recordProperty.PropertyValue
FROM input as event
CROSS APPLY GetRecordProperties(event.recordField) AS recordProperty
对于您的情况,您可以执行以下 sql:
SELECT
recordProperty.PropertyName,
recordProperty.PropertyValue
FROM input as event
CROSS APPLY GetRecordProperties(event) AS recordProperty
输出:
正如我问你想如何处理Alert1,Alert2
属性,如果你想摆脱它们,那么使用下面的 sql:
SELECT
recordProperty.PropertyValue.Site, recordProperty.PropertyValue.Sensor....<more your columns>
FROM input as event
CROSS APPLY GetRecordProperties(event) AS recordProperty
输出:
推荐阅读
- debian - 使用 live-build 添加用户帐户
- c++ - boost hana index_if and type
- c - 如何获取陷入无限循环的 C 程序的代码覆盖率信息?
- c - C socket programming, getting IP of server on raspberrypi
- sql - 如何在分区范围内仅取最大周期各自的值
- angular - 如何延迟 Angular 子组件 API 调用?
- android - 重新创建活动后无法重用 PeerConnectionClient
- elasticsearch - 弹性搜索如何通过 doc id 查找文档内容
- java - 从 YourKit 调用 System.gc() 会导致分配的内存增加
- angular - 用玩笑测试@ngrx/Effect