Search code examples
azure-data-explorerkql

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

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.

Solution

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