Kusto query to project json data

Question

We have a message column value as below,

Query Value : { "prop1" : "value1", "prop2" : "value2" }

Want to display the projection of the Kusto query as below,

message	Property1	Property2
Query Value :{ "prop1" : "V1", "prop2" : "V2"}	V1	V2

Is this achievable?

Answer 1

For this case you can use below kql query:

let rithTable1 = datatable(Rithinput: dynamic)[
'Query Value : { "prop1" : "value1", "prop2" : "value2" }'
];
rithTable1
| extend  x=indexof(Rithinput, ":")
| extend rithkey1=parse_json(tostring(substring(Rithinput, x + 1)))
| mv-apply rithkey1 on (
    extend key = tostring(bag_keys(rithkey1)[0])
    | project key, value = rithkey1[key]
)
|project  Rithinput,value,key
| summarize Property1 = tostring(makelist(value)[0]), Property2 = tostring(makelist(value)[1]) by tostring(Rithinput)

Output:

Fiddle.