How to loop an array of objects using Kusto Query Language

Question

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.

Event	Entities
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"}]

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.

AllEntities
Ilyes Tab
John Smith
william Red
Steve Smith
Reshald M
Fred Stalone

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.

Per SO guidelines - please remove the images and provide the data in textual format (datatable / CSV / markdown table) — David דודו Markovitz
– David דודו Markovitz, Commented Nov 5, 2022 at 13:06
The code makes no sense, especially the use of coalesce. What is you end game here? — David דודו Markovitz
– David דודו Markovitz, Commented Nov 5, 2022 at 13:39
Hi david, thank you for your response. you will find here my table. storage19985645.blob.core.windows.net/data2/Classeur.csv — ilyés Tabessi
– ilyés Tabessi, Commented Nov 5, 2022 at 16:38
Also, are you sure that Entities is of string type, and not dynamic? — David דודו Markovitz
– David דודו Markovitz, Commented Nov 5, 2022 at 16:53

David דודו Markovitz · Accepted Answer · 2022-11-05 17:05:48Z

0

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:

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)

AllEntities
Ilyes Tab
John Smith
william Red
Steve Smith
Reshald M
Fred Stalone

Fiddle

edited Nov 5, 2022 at 17:05

answered Nov 5, 2022 at 16:57

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ilyés Tabessi Over a year ago

Thank you sir, I am most satisfied with your response which totally covered my needs.

David דודו Markovitz Over a year ago

Great to hear. Please close the loop by accepting the answer.

ilyés Tabessi Over a year ago

It's done David

Collectives™ on Stack Overflow

How to loop an array of objects using Kusto Query Language

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related