CODEWITHSUNDEEP
azureazure-log-analyticsazure-data-explorerkql
mhabib
mhabib

Reputation: 35

How to convert JSON to key value table in Kusto

I have a table that consists of one row and number of columns. One of the columns is named EventProperties which is a JSON of properties of this format:

{
   "Success":true,
   "Counters":{
      "Counter1":1,
      "Counter2":-1,
      "Counter3":5,
      "Counter4":4,
   }
}

I want to convert the Counters from this JSON to a two-column table of keys and values, where the first column is the name of the counter (e.g. Counter3) and the second column is the value of the counter (e.g. 5). I've tried this:

let eventPropertiesCell = materialize(MyTable
| project EventProperties
);
let countersStr = extractjson("$.Counters", tostring(toscalar(eventPropertiesCell)), typeof(string));
let countersJson = parse_json(countersStr);
let result = 
print mydynamicvalue = todynamic(countersJson) 
| mvexpand mydynamicvalue 
| evaluate bag_unpack(mydynamicvalue);
result

But I get a table with a column for each counter from the JSON, and number of rows that is equal to the number of counters, while only one random row is filled with the counter value. For example, with the JSON from the example above, I get:

enter image description here

But I want something like this:

enter image description here

Any help will be appreciated!

Upvotes: 2

Views: 4067

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could try using mv-apply as follows:

datatable(event_properties:dynamic)
[
   dynamic({
   "Success":true,
   "Counters":{
      "Counter1":1,
      "Counter2":-1,
      "Counter3":5,
      "Counter4":4
   }
}), 
   dynamic({
   "Success":false,
   "Counters":{
      "Counter1":1,
      "Counter2":2,
      "Counter3":3,
      "Counter4":4
   }
})
]
| mv-apply event_properties.Counters on (
    extend key = tostring(bag_keys(event_properties_Counters)[0])
    | project key, value = event_properties_Counters[key]
)
| project-away event_properties

Upvotes: 3

Related Questions