KUSTO dynamically coalesce columns/values

Question

recently exposed to Kusto and looking to simplify results into a clean, flat table.

Brief background:

I'm parsing json that contains many different fields, but there is one specific piece of information I'm particularly interested in - Let's call the field MyField.

In the parsed json, MyField can actually show as MyField, My_Field, myfield, 'field'. In the future, it could probably be called _my_field_ or something else similar.

I need a way coalesce the values dynamically without having to explicitly define each new naming convention. is this doable?

what I've done:

let Query01 = 
Stuff
| where timestamp >= ago(90d) 
| project Stuff
| evaluate bag_unpack(Stuff)
;
Query01
| project-away Stuff
| ????

snapshot

Yoni L. Yoni L. · Accepted Answer · 2020-06-08T18:01:49

a. in terms of efficiency, that's quite an unfortunate situation you're in. it would really help if you could standardize the property names in your JSON payload, and not have to cover all potential past/future options

b. give the current situation, you could try something like the following example (though please don't consider it efficient)

datatable(d:dynamic)
[
    dynamic({"my_value":1, "your_value":2}),
    dynamic({"his_value":3, "MyValue":1}),
    dynamic({"theirValue":5, "_myvalue_":1}),
]
| mv-apply d on (
    extend key = tostring(bag_keys(d)[0])
    | where key matches regex '(?i).*my.*value.*'
    | project p = pack("MyValue", d[key])
    | summarize d = make_bag(p)
)
| summarize sum(tolong(d.MyValue))

(this returns 3, which is the sum of 1, 1 , 1)

KUSTO dynamically coalesce columns/values

1 Answers