CODEWITHSUNDEEP
azure-data-explorerkql
Bineet Singh
Bineet Singh

Reputation: 33

How to select only json strings from a dynamic col in kusto?

The dynamic datatype column of my Table sometimes has values other than json in it, But 'evaluate bag_unpack' command fails to run due to those.

Is there a way in which I can only select the properly formatted json strings from the col and send those to 'evaluate bag_unpack' while ignoring the rest of the garbage values.

Sample data:

{"message":"msg1","code":"1"}
<html><body>Server Down</body></html>
{"message":"msg2","code":2"}
{"message":"msg3","code":"3"}

Upvotes: 2

Views: 672

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

bag_keys() is used in this case to figure out if the parsed log is a valid JSON or not

datatable(log_id:int, log:dynamic)
[
     1 ,'{"message":"msg1","code":"1"}'
    ,2 ,'<html><body>Server Down</body></html>'
    ,3 ,'{"message":"msg2","code":2"}'
    ,4 ,'{"message":"msg3","code":"3"}'
]
| extend parsed_log = todynamic(tostring(log))
| extend valid_json = iff(isnotempty(bag_keys(parsed_log)), parsed_log, dynamic(null))
| evaluate bag_unpack(valid_json)
log_id log parsed_log code message
1 {"message":"msg1","code":"1"} {"message":"msg1","code":"1"} 1 msg1
2 Server Down Server Down
3 {"message":"msg2","code":2"} {"message":"msg2","code":2"}
4 {"message":"msg3","code":"3"} {"message":"msg3","code":"3"} 3 msg3

Fiddle

Upvotes: 1

Related Questions