CODEWITHSUNDEEP
azureazure-data-explorerkql
Markus S.
Markus S.

Reputation: 2812

Azure Data Explorer transform camelCase values in dynamic column to PascalCase

We imported a lot of historic data into Azure Data Explorer. They should end up in the same table as the current live streaming data.

However the historic data contains a dynamic json column where the field names are written in camelCase. The new format expects the field names in PascalCase.

Example:

Historic Format

{
  "messageType":"xy"
}

New Format

{
  "MessageType":"xy"
}

Update:

Is there any chance to achieve this kind of transformation in KQL or do we have to unload all the data, apply the transformation and ingest the data again?

Upvotes: 1

Views: 675

Answers (1)

Avnera
Avnera

Reputation: 7618

Here is one way to do it:

datatable(d:dynamic)[dynamic({
  "messageType":"xy",
  "messageValue":5
})
]
| mv-apply d on (
    mv-expand kind=array d
    | extend k = strcat(toupper(substring(tostring(d[0]), 0,1)), substring(tostring(d[0]), 1))
    | extend prop = pack(k, d[1])
    | summarize make_bag(prop)
)

result:

enter image description here

Upvotes: 2

Related Questions