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"}
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 |