英文:
KQL/ADX - mv-apply on empty array removes records
问题
我想从具有JSON内容的动态字段中提取值，并在流式摄入期间基于计算找到最小值。数组也可能为空。
我尝试了以下内容：
datatable (Id:int, BatteryDetails: dynamic)[1, dynamic([]),2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])]| mv-apply f = BatteryDetails on(project LowestBatteryLevel = toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))| top 1 by LowestBatteryLevel asc)| project Id, LowestBatteryLevel
结果：
| Id | LowestBatteryLevel |
|---|---|
| 2 | 50 |
我想要获得：
| Id | LowestBatteryLevel |
|---|---|
| 1 | |
| 2 | 50 |
任何关于如何保留记录1的想法吗？已经找到了这个问题，但没有帮助。
英文:
I would like to extract values from a dynamic field with JSON content and find the smallest value based on a calculation during streaming ingestion. The array may also be empty.
I tried the following:
datatable (Id:int, BatteryDetails: dynamic)[1, dynamic([]),2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])]| mv-apply f = BatteryDetails on(project LowestBatteryLevel = toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))| top 1 by LowestBatteryLevel asc)| project Id, LowestBatteryLevel
Result:
| Id | LowestBatteryLevel |
|---|---|
| 2 | 50 |
I would like to get:
| Id | LowestBatteryLevel |
|---|---|
| 1 | |
| 2 | 50 |
Any idea how the record 1 can be retained? Already found this question but this did not help.
答案1
得分: 0
Here is the translated code portion:
datatable (Id:int, BatteryDetails: dynamic)[1, dynamic([]),2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])]| extend LowestBatteryLevel = iff(isnull(BatteryDetails) or isempty(BatteryDetails), null,mv-apply f = BatteryDetails on(project LowestBatteryLevel = toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))| top 1 by LowestBatteryLevel asc))| project Id, LowestBatteryLevel
英文:
datatable (Id:int, BatteryDetails: dynamic)[1, dynamic([]),2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])]| extend LowestBatteryLevel = iff(isnull(BatteryDetails) or isempty(BatteryDetails), null,mv-apply f = BatteryDetails on(project LowestBatteryLevel = toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))| top 1 by LowestBatteryLevel asc))| project Id, LowestBatteryLevel
答案2
得分: 0
datatable (Id:int, BatteryDetails: dynamic)[1, dynamic([]),2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])]| mv-apply f = BatteryDetails on(summarize LowestBatteryLevel = min(toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))))| project Id, LowestBatteryLevel
输出：
| Id | LowestBatteryLevel |
|---|---|
| 1 | |
| 2 | 50 |
<details><summary>英文:</summary>**Code:**
datatable (Id:int, BatteryDetails: dynamic)
[
1, dynamic([]),
2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])
]
| mv-apply f = BatteryDetails on
(
summarize LowestBatteryLevel = min(toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100)))
)
| project Id, LowestBatteryLevel
I referred the same [thread](https://stackoverflow.com/questions/74365147/how-to-return-null-results-after-mv-apply-on-json-array) that you shared and wrote the above query. The query uses the `mv-apply` operator to apply the `summarize` to each element of the `BatteryDetails` dynamic field. The `summarize` operator calculates the `LowestBatteryLevel` by dividing the `fullChargedCapacity` by the `designedCapacity`, multiplying by 100, rounding to the nearest integer, and converting to an integer. The `min` function is used to select min value in each group.**Output:**| Id | LowestBatteryLevel ||----|---------------------|| 1 | || 2 | 50 |['fiddle'](https://dataexplorer.azure.com/clusters/help/databases/Samples?query=H4sIAAAAAAAAA3WQy2rDMBBF9/6KwStNUduk0C4M3TTdBPIHwYuJNU5V9DDyOMFp++9VTJ8hGcEgruYyR9eQ5LNxDGppKhtEwxOJcBqfWci6vgIzBvK2wWJdQK65/lbUukY9aXe/2s/bW9kOzi1eKG3ZLKijxspYVvCgoTTc2234J89nHxouee4vempEKOriHfzumrrOjdDC48kXIIaJUk39WP3gPSV7YIBV3HMvX4YV79hlv7dBScxpqBSHYPLdxCGHpNqbM4AIt/Bn4hQU4SqTzhBx2o8ZtkvxlRuBpdFnAD4BHGaM/ZQBAAA=)</details># 答案3**得分**: 0I'm here to provide you with the translated content. Here's the translated code part:```markdown通过在数组为空的情况下伪造一个虚拟条目，找到了一个可能的解决方案：
datatable (Id:int, BatteryDetails: dynamic)
[
1, dynamic([]),
2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])
]
| extend BatteryDetailsTmp = iff(array_length(BatteryDetails) == 0, dynamic([{"fullChargedCapacity": 0, "designedCapacity": 1}]), BatteryDetails)
| mv-apply f = BatteryDetailsTmp on
(
project LowestBatteryLevel = toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))
| top 1 by LowestBatteryLevel asc
)
| project-away BatteryDetailsTmp
| project Id, LowestBatteryLevel
Please let me know if you need any further assistance.
英文:
Figured out a possible solution by faking a dummy entry in case the array is empty:
datatable (Id:int, BatteryDetails: dynamic)[1, dynamic([]),2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}])]| extend BatteryDetailsTmp = iff(array_length(BatteryDetails) == 0, dynamic([{"fullChargedCapacity": 0, "designedCapacity": 1}]), BatteryDetails)| mv-apply f = BatteryDetailsTmp on(project LowestBatteryLevel = toint(round(todouble(f.fullChargedCapacity) / todouble(f.designedCapacity) * 100))| top 1 by LowestBatteryLevel asc)| project-away BatteryDetailsTmp| project Id, LowestBatteryLevel
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论