如何在kusto中使用parse和mv展开后投影附加列

这里有一个字符串列的示例，我想根据dictionary的键值将这个字符串列拆分为多个列，如果有更多的键，我想将它们划分为不同的行。

<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

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_type//tr>

dict_id	dict_publisher	dict_Identity_id	column1
1		0	00000000-0000-0000-000000000000	val
1		0	00000000-0000-0000-000000000000

开始：

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)

结果：

名称类型>

id	身份	权限	角色
1	｛ "Id"："00000000-0000-0000-0000-000000000"， "sid"："134567890"> ｝
1	{ "Id"："00000000-0000-0000-000000000000"， &"sid"："134567890"> }	ABC

相关内容

最新更新

热门标签：