Filter in KQL arrays

Filter in KQL arrays

Problem Description:

I have a database that one of its columns represents a JSON array. The array is an array or arrays with numbers, like [[1], [2,3], [4,5,6,7], [8]]. I want to filter rows where one of the arrays inside has at least 3 elements.

I used this KQL:

traces
| where message has "Items per slot"
| extend items = parse_json(customDimensions["ItemsPerSlot"])
| where ??? // There is a slot with at least 3 items

But how can I write this where condition?

Solution – 1

let traces = datatable(id:int, message:string, customDimensions:dynamic)
[
    1 ,"Items per slot" ,dynamic({"ItemsPerSlot": [[1], [2,3], [4,5,6,7], [8]]})
   ,2 ,"Items per slot" ,dynamic({"ItemsPerSlot": [[1], [2,3], [8]]})
];
traces
| where message has "Items per slot"
| extend items = customDimensions["ItemsPerSlot"]
| mv-apply item = items on (where array_length(item) >= 3 | take 1 | project-away item)

Fiddle