already I'm facing a problem which is the inability to loop an array of objects using Kusto Query Language. The Table (Events) is under this form.

I want to loop into each object of the column "Entities" then I'm going to save the Names of these entities within a new column which will be under this form.

Already I have worked around this problem by looping manually into each object, this is the code I managed to develop.

Events 
| project Event, 
 Entities1=tostring(parse_json(Entities)[0].Name) 
,Entities2=tostring(parse_json(Entities)[1].Name) 
,Entities3=tostring(parse_json(Entities)[2].Name) 
,Entities4=tostring(parse_json(Entities)[3].Name), 
| project AllEntities = coalesce(Entities1,Entities2,Entities3,Entities4) 
| summarize count() by AllEntities

I'm asking if anyone can offer me a solution or a function to make my loop automatically.

CodePudding user response：

KQL is a declarative language, similarly to SQL, and declarative languages do not use control flow commands (e.g., if, goto or loop), but provide special syntax / operators / functions that deal with complex types.

KQL provides the following operators:

• mv-expand
• mv-apply

datatable(Event:string, Entities:string)
[ 
    'Ev1' ,'[{"$id":"1","Name":"Ilyes Tab","UPNSuffix":"gmail.com","Type":"account"}, {"$id":"2","Name":"John Smith","UPNSuffix":"gmail.com","Type":"account"}]'
   ,'Ev2' ,'[{"$id":"3","Name":"william Red","UPNSuffix":"gmail.com","Type":"account"}, {"$id":"5","Name":"Steve Smith","UPNSuffix":"gmail.com","Type":"account"}, {"$id":"8","Name":"Reshald M","UPNSuffix":"gmail.com","Type":"account"}]'
   ,'Ev3' ,'[{"$id":"4","Name":"Fred Stalone","UPNSuffix":"gmail.com","Type":"account"}]'
]    
| mv-expand parse_json(Entities)
| project AllEntities = tostring(Entities.Name)

Fiddle