CODEWITHSUNDEEP
azure-data-explorer
MaxDev
MaxDev

Reputation: 147

distinct unordered dynamic column in kusto

query:

let result = datatable(MyString:string)
[
'{"X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3"}',
'{"Y": "xyz", "X": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3"}'
];
result
| distinct *

result is

{"X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3"}
{"Y": "xyz", "X": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3"}

is there any operation in kusto to make the result be ordered by key and then get the distinct to be the result like:

{"prop1": "value1", "prop2": "value2", "prop3": "value3", "X": "xyz", "Y": "xyz", "Z": "xyz"}

Upvotes: 1

Views: 2785

Answers (1)

Slavik N
Slavik N

Reputation: 5298

You should use dynamic_to_json() to sort the keys in the JSON (see details here) - note that the key "X" comes before "prop1", because the uppercase chars comes before lowercase chars.

This is what the correct query looks like:

let result = datatable(MyString:string)
[
    '{"X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3"}',
    '{"Y": "xyz", "X": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3"}'
];
result
| extend MyString = tostring(dynamic_to_json(todynamic(MyString)))
| distinct *

Result:

MyString
{"X":"xyz","Y":"xyz","Z":"xyz","prop1":"value1","prop2":"value2","prop3":"value3"}

Note: for query efficiency, it's better to store JSONs as dynamic columns and not as string columns. Here's how:

let result = datatable(MyJSON:dynamic) [
    dynamic({'a': {'b': {'c': 0.0, 'd': 4.0}, 'e': {'f': {'g': 0.0, 'h': 1.0}}}}),
    dynamic({'a': {'e': {'f': {'g': 0.0, 'h': 1.0}}, 'b': {'c': 0.0, 'd': 4.0}}})
];
result
| extend MyJSON = dynamic_to_json(MyJSON)
| distinct *

Upvotes: 3

Related Questions