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:

| 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]]})
| 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)
1Items per slot{"ItemsPerSlot":[[1],[2,3],[4,5,6,7],[8]]}[[1],[2,3],[4,5,6,7],[8]]


Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.