how to select json in kusto sql

Question

I want select json data in azure database using kusto sql. see it the data.

the data in entities column. it looks like json type. but with [,] i dont know why with [,].... not only {,}..

i want select like this in general sql type.. (select id, hostname, osfamily .... from SecurityAlert)

so, i tried like this...

SecurityAlert
| extend d=parse_json(Entities) 
| extend Entities=d.$id, id=d["$id"]

Answer 1

The data is in json array, so you need to "expand" the array using the mv-expand operator. Afterwards you can access it as you did using the dot "." notation.

Here is an example:

let SecurityAlerts = datatable(Entities:dynamic) [dynamic([{"$id":"4", "hostName":"a2"}, {"$id":"5", "hostName":"a3"}])];
SecurityAlerts 
| mv-expand Entities
| extend id = Entities.['$id'], hostName = Entities.hostName

Results: