When working with raw JSON data in Eventhouse, one common challenge is comparing two records to identify what changed—especially when the structure isn’t fixed. Fields may appear, disappear, or shift in type, making traditional column-based comparisons brittle or outright impossible. This came up in a recent chat on the cat team, and we wanted to share this approach with others!
💡 The Scenario
Imagine you’re ingesting JSON like this:
JSON
{"a":1,"b":2,"c":3}And you want to compare it to a previous version:
JSON
{"a":1,"b":20,"c":3}You want to know: what changed?
🧪 The KQL Trick
Here’s a compact and elegant KQL snippet that does exactly that:
datatable(id:int, current:string, previous:string)
[
1, '{"a":1,"b":2,"c":3}', '{"a":1,"b":20,"c":3, "d":40}'
]
| extend current_json = parse_json(current), previous_json = parse_json(previous)
| extend keys = bag_keys(current_json)
| mv-expand key = keys
| extend current_val = tostring(current_json[tostring(key)]), previous_val = tostring(previous_json[tostring(key)])
| where current_val != previous_val🔍 What it does:
Parses both JSON strings into dynamic objects.
Extracts the keys from the current JSON.
Expands each key into its own row.
Compares the value of each key between the current and previous JSON.
Filters to only show keys where the value changed.
✅ Output:
Benefits of this approach:
No schema assumptions: You don’t need to know the structure ahead of time.
Scalable: Works across any number of fields.
Readable: Easy to debug and extend.
Flexible: You can plug this into a larger Eventhouse pipeline or use it for ad hoc investigations.
This approach is a great example of how KQL can be used to solve real-world data engineering problems with minimal overhead. Whether you're debugging ingestion issues or building validation logic, this pattern is a powerful addition to your KQL toolkit.
