2

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!

mhabib
  • 35
  • 2
  • 6

1 Answers1

3

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
Yoni L.
  • 22,627
  • 2
  • 29
  • 48
  • Thanks, Yoni. That seems to give me the needed table format, but the problem is that I don't want to hard-code the JSON and its values in the query itself, but rather get it from the table (MyTable in my code above). I tried to modify your solution and change it to: ... datatable(event_properties:dynamic) [ dynamic( eventPropertiesCell ) ] | mv-apply event_properties.Counters ... but it says "Missing json value". – mhabib May 05 '20 at 20:14
  • 1
    sorry, i thought it was obvious I can't query your table, that's why i used a `datatable` with constant values instead. you should be able to swap that part with your actual table. – Yoni L. May 05 '20 at 20:41