KQL/ADX - mv-apply on empty array removes records

Question

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.

Answer 1

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