KQL - Remove keys from bag nested inside array nested inside bag

Question

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?

Answer 1

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