I have a table called Resources, which contains a column called Properties (which is a property bag). Inside properties, there is a property called AdditionalInformation, which is an array of bags. I want to remove keys from the bags that are nested inside AdditionalInformation. Basically, I want to do something like:
for each resource in Resources:
for each info in resource.Properties.AdditionalInformation:
info.RemoveKey("KeyToRemove")
In terms of converting this into KQL, I'm not sure how to perform the double loop above. I've gotten as far as:
Resources
| extend properties = resource.Properties
| extend info = properties.AdditionalInformation
| bag_remove_keys(info, dynamic(['KeyToRemove']))
But this of course is not valid KQL. How should solve this problem?
The following could work, but it's quite inefficient to do this type of manipulation at query time, over a large number of records.
You'd be better off removing the unwanted properties in the application that generates the payload.
let Resources = datatable(i:long, Properties:dynamic)
[
1, dynamic({"hello":"world", "AdditionalInformation":[{"key_to_remove":17,"key_to_retain":13},{"key_to_remove":27,"key_to_retain":23}]}),
2, dynamic({"foo":"bar", "AdditionalInformation":[{"key_to_retain":33,"key_to_remove":37},{"key_to_retain":43,"key_to_remove":47}]}),
]
;
Resources
| mv-apply info = Properties.AdditionalInformation on (
extend info = bag_remove_keys(info, dynamic(["key_to_remove"]))
| summarize properties_temp = bag_pack("AdditionalInformation", make_list(info))
)
| extend ModifiedProperties = bag_merge(bag_remove_keys(Properties, dynamic(["AdditionalInformation"])), properties_temp)
| project-away properties_temp
i | Properties | ModifiedProperties |
---|---|---|
1 | { "hello": "world", "AdditionalInformation": [ { "key_to_remove": 17, "key_to_retain": 13 }, { "key_to_remove": 27, "key_to_retain": 23 } ] } |
{ "hello": "world", "AdditionalInformation": [ { "key_to_retain": 13 }, { "key_to_retain": 23 } ] } |
2 | { "foo": "bar", "AdditionalInformation": [ { "key_to_retain": 33, "key_to_remove": 37 }, { "key_to_retain": 43, "key_to_remove": 47 } ] } |
{ "foo": "bar", "AdditionalInformation": [ { "key_to_retain": 33 }, { "key_to_retain": 43 } ] } |