Azure Data Explorer transform camelCase values in dynamic column to PascalCase

Question

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:

I need to construct a dynamic column and update the property names within this dynamic column, not only rename the column itself in the output.

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?

The transformation you are looking for is to turn the first character to capital, correct? — Avnera
Exactly. But for properties within a dynamic column, not for column names. — Markus Strobl

Avnera Avnera · Accepted Answer · 2021-06-17T13:30:46

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:

Azure Data Explorer transform camelCase values in dynamic column to PascalCase

1 Answers