How to write KQL to convert CSV of key=value Pairs to Dictionary?

user_5768635 0
Viewed 74

Say I had a Kusto table with a column that stores string-typed values which looked like this:

secure=false,ipVersion=6,http=HTTP/1.1,streaming=true,traceEnabled=true,debugEnabled=false

How could I write a KQL expression which converts this to a dynamic type value such as:

{'secure':'false','ipVersion':'6','http':'HTTP/1.1','streaming':'true','traceEnabled':'true','debugEnabled':'false'}

I have tried variations of split​ and mv-apply​ and bag_pack()​ but I can never get it to fully work so I am looking for some expert advice here.

kql azure-data-explorer
2 Answers

If the payload has a stable & prefedined schema, you could use parse and pack():

print input = 'secure=false,ipVersion=6,http=HTTP/1.1,streaming=true,traceEnabled=true,debugEnabled=false'
| parse input with 'secure='secure',ipVersion='ipversion',http='http',streaming='streaming',traceEnabled='traceEnabled',debugEnabled='debugEnabled
| project output = pack('secure', secure, 'ipVersion', ipversion, 'http', http, 'streaming', streaming, 'traceEnabled', traceEnabled, 'debugEnabled', debugEnabled)
output
{
"secure": "false",
"ipVersion": "6",
"http": "HTTP/1.1",
"streaming": "true",
"traceEnabled": "true",
"debugEnabled": "false"
}

Alternatively, and less-efficiently, you could use extract_all(), mv-apply and and make_bag():

print input = 'secure=false,ipVersion=6,http=HTTP/1.1,streaming=true,traceEnabled=true,debugEnabled=false'
| mv-apply pair = extract_all(@'(\w+)=([^,]+)', input) on (
    summarize output = make_bag(pack(tostring(pair[0]), pair[1]))
)
| project output
output
{
"secure": "false",
"ipVersion": "6",
"http": "HTTP/1.1",
"streaming": "true",
"traceEnabled": "true",
"debugEnabled": "false"
}
user_78618070

Here is an additional way based on replace_regex()

We're replacing each key=value (and , or text end) with "key":"value" (and , or nothing).
The result, concatenated with { and }, could be then converted to dynamic.

print input = "secure=false,ipVersion=6,http=HTTP/1.1,streaming=true,traceEnabled=true,debugEnabled=false"
| project output = todynamic(strcat("{", replace_regex(input, "(.*?)=(.*?)(,|$)", @'"\1":"\2"\3'), "}"))

"output": {
    "secure": "false",
    "ipVersion": "6",
    "http": "HTTP/1.1",
    "streaming": "true",
    "traceEnabled": "true",
    "debugEnabled": "false"
}

Fiddle

user_63364790
Related