CODEWITHSUNDEEP
azure-data-explorerkql
Swasti
Swasti

Reputation: 287

How to project additional column after using parse and mv-expand in kusto

Here's a sample for string column, I would want to split this string column into multiple columns based on the key values of dictionary and if there are more keys, I want to make them into different rows.

<column2>: "for each user:[{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"},
{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"}]"```

I want to trim initial string "for each user:" and create columns for id, publisher, type, Identity.Id, Identity.sid, roles, permissions, name and since there are two dictionaries in the above sample, I want to insert the second item into a new row.

I also want <column1> in the output

Upvotes: 1

Views: 2475

Answers (2)

Slavik N
Slavik N

Reputation: 5308

Here you go:

datatable(Column2: string) [
    'for each user:[{"id":1,"Publisher":null,"type":0,"Identity":{"Id":"00000000-0000-0000-0000-000000000000","sid":"134567890"},"roles":[],"permissions":[],"name":"ABC"},{"id":1,"Publisher":null,"type":0,"Identity":{"Id":"00000000-0000-0000-0000-000000000000","sid":"134567890"},"roles":[],"permissions":[],"name":"ABC"}]"'
]
| parse Column2 with 'for each user:' Json:dynamic '"'
| project Json
| mv-expand Json
| evaluate bag_unpack(Json)

Result:

id Identity name permissions roles type
1 {
"Id": "00000000-0000-0000-0000-000000000000",
"sid": "134567890"
}		 ABC [] [] 0
1 {
"Id": "00000000-0000-0000-0000-000000000000",
"sid": "134567890"
}		 ABC [] [] 0

Step by step explanation:

  1. Use parse to extract the json part that you're interested at into a column named Json
  2. Project only the Json column (as you don't care about the original input string)
  3. Use mv-expand to split the array in the Json column into separate elements (each one will get his own record)
  4. Use evaluate bag_unpack(Json) to have a separate column for every key in the Json column

Upvotes: 2

Yoni L.
Yoni L.

Reputation: 25905

print message = ```<column1>: "val",
<column2>: "for each user:[{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"},
{"id":1,
"Publisher":null,
"type":0,
"Identity":{
"Id":"00000000-0000-0000-0000-000000000000",
"sid":"134567890"},
"roles":[],
"permissions":[],
"name":"ABC"}]"```
| parse message with * '<column1>: "' column1 '"' * '<column2>: "for each user:' dict:dynamic '"'
| mv-expand dict
| project dict.id, dict.publisher, dict.type, dict.Identity.Id, column1 // add the rest of your properties of interest
dict_id dict_publisher dict_type dict_Identity_Id column1
1 0 00000000-0000-0000-0000-000000000000 val
1 0 00000000-0000-0000-0000-000000000000 val

Upvotes: 1

Related Questions