azureanalyticsazure-application-insightsms-app-analyticsazure-data-explorer

How to project json key value pair to columns without explicitly specifying name


In customDimensions I have x number of key-value pair data (currently only two Name and Channel as an example in the screenshot below)

enter image description here

and I would like to project them to columns without explicitly specify the name of the key, so that in the future, if a new key-value pair added to the log, I don't have to go back and modify my query in order to display it as a new column.

enter image description here

Thank you!


Solution

  • The Kusto query language includes the bag_unpack() plugin: https://learn.microsoft.com/en-us/azure/kusto/query/bag-unpackplugin

    Here's an example:

    datatable(anotherColumn:int, customDimensions:dynamic)
    [
        1, dynamic({"Name":"mfdg",  "Channel":"wer"}),
        2, dynamic({"Name":"mfdg2", "Channel":"wer2"}),
        3, dynamic({"NotAName":2.22, "NotAChannel":7}),
    ]
    | evaluate bag_unpack(customDimensions)
    

    Which yields:

    | anotherColumn | Name  | Channel | NotAName | NotAChannel |
    |---------------|-------|---------|----------|-------------|
    | 1             | mfdg  | wer     |          |             |
    | 2             | mfdg2 | wer2    |          |             |
    | 3             |       |         | 2.22     | 7           |