KQL/ADX：对空数组使用mv-apply导致记录丢失的解决方法

在流式摄入场景中，需从存储JSON内容的dynamic类型字段提取值，计算后找出最小值。该字段对应的数组可能为空。

尝试的查询语句 #

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

当前查询结果 #

期望结果 #

解决方案 #

原查询中mv-apply默认是内关联行为，当数组为空时会直接过滤掉对应行。可以通过以下两种方式保留空数组的记录：

方式一：使用mv-apply的default参数 #

在mv-apply语句末尾添加default=()，指定数组为空时返回默认空行，从而保留Id=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
    ) default=()
| project Id, LowestBatteryLevel

方式二：使用outer apply结合summarize计算最小值 #

通过outer apply展开数组，再用summarize min()计算最小值，空数组时min()会返回null，自然保留对应行：

datatable (Id:int, BatteryDetails: dynamic)
[
    1, dynamic([]),
    2, dynamic([{"fullChargedCapacity": 5, "designedCapacity": 10}]) 
]
| outer apply (
    BatteryDetails
    | extend Level = toint(round(todouble(fullChargedCapacity)/todouble(designedCapacity)*100))
    | summarize LowestBatteryLevel = min(Level)
)
| project Id, LowestBatteryLevel

内容的提问来源于stack exchange，提问作者Dietmar