CODEWITHSUNDEEP
azure-data-explorerkql
Anders Fredborg
Anders Fredborg

Reputation: 99

KQL - Convert Dynamic Array of Key Value to Key Value dictionary

I have a cell of a table-column that is a dynamic. This was ingested from .Net as a Dictionary, but in Kusto it looks like an array of objects, that has a property key and value:

[
    {"key":"ProjectId","value":"1234"},
    {"key":"ProjectName","value":"Albatros"},
    {"key":"User","value":"Bond"}
]

I want to convert the contents of the cell in my Kusto query to the following dynamic:

{
  "ProjectId": "1234",
  "ProjectName": "Albatros",
  "User": "Bond"
}

I cant figure out how to write the expression, that converts it form the array into the new dynamic format.

Can anyone point me in the right direction?

Upvotes: 0

Views: 5671

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you can use a combination of mv-apply and make_bag():

print d = dynamic([
  {"key": "value"},
  {"ProjectId": "1234"},
  {"ProjectName": "Albatros"},
  {"User": "Bond"}
])
| mv-apply d on (
    summarize result = make_bag(d)
)
result
{
"key": "value",
"ProjectId": "1234",
"ProjectName": "Albatros",
"User": "Bond"
}

UPDATE based on your change to the original question:

print d = dynamic([
    {"key":"ProjectId","value":"1234"},
    {"key":"ProjectName","value":"Albatros"},
    {"key":"User","value":"Bond"}
])
| mv-apply d on (
    summarize result = make_bag(pack(tostring(d.key), d.value))
)
result
{
"ProjectId": "1234",
"ProjectName": "Albatros",
"User": "Bond"
}

Upvotes: 1

Related Questions