azureazure-application-insights

Kusto query to project json data


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?


Solution

  • 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:

    enter image description here

    Fiddle.