问题

我想从具有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

结果：

我想要获得：

任何关于如何保留记录1的想法吗？已经找到了这个问题，但没有帮助。

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

答案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

输出：

<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
**得分**: 0

I'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.

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