kqlazure-data-explorerkusto-explorer

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


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?


Solution

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