我可以在kusto(KQL)中提取多个嵌套的json属性吗

Question

我有遥测事件发送到playfab。在这些事件中，我想查询Payload的内容。我可以做到，但是嵌套属性都在同一列中。我可以使用以下内容来提取事件中的所有内容，但对于Payload中嵌套的SuperProperties除外。超属性都在同一列中

这是我的活动的结构

SchemaVersion": xxxx
"FullName_Namespace": xxxx,
"FullName_Name": xxxx,
"Entity_Id": xxxxx,
"Entity_Type": txxxxx,
"EntityLineage_title": xxxxxx,
"EventData": {
"Timestamp": "xxxxxxx",
"PayloadContentType": "Json",
"EntityLineage": {
"master_player_account": "xxxxxx",
"title_player_account": "xxxxxx",
"namespace": "xxxxx",
"title": "xxxxxx"
},
"SchemaVersion": "xxxxxx",
"Originator": {
"Type": "xxxxxx",
"Id": "xxxxxx"
},
"OriginInfo": {
"Timestamp": "xxxxxx"
},
"FullName": {
"Namespace": "xxxxxx",
"Name": "xxxxxx"
},
"Payload": {
"TimeToCompleteSubStage": xxxxxx,
"TimeToCompleteStage": xxxxxx,
"SuperProperties": {
"GameSessionID": "xxxxxx",
"PlayFabId": "xxxxxx",
"Version": "xxxxxx",
"Index": xxxxxx,
"Time": "xxxxxx"
},
"IdStageFrom": "xxxxxx",
"SubStageId": xxxxxx,
"IdStageTo": "xxxxxx"
},
"Id": "xxxxxx",
"Entity": {
"Type": "xxxxxx",
"Id": "xxxxxx"
}
},
"EventId": xxxxxx,
"Timestamp": xxxxxx,
"EntityLineage_title_player_account": xxxxxx,
"EntityLineage_master_player_account": xxxxxx,
"EntityLineage_namespace": xxxxxx,
"ExperimentVariants": xxxxxx

我尝试过的

如果在这里发现了类似的问题，但我不确定是否完全理解这个问题或答案。甚至在看了这些文件之后，这些文件似乎解释了如何做到这一点。原因是什么？因为我在Kusto甚至编程方面的知识都是基础知识。

那么，是否有人可以解释是否可以获得Payload下的所有属性，甚至是嵌套在Payload中特定列中的超级属性？如果是，该怎么做？如果我查询有效载荷之外的任何其他信息，或者不是，这都无关紧要

这是我的尝试

['events.all'] 
| where FullName_Name == 'FTUE_Funnel'
| project Payload = parse_json(EventData.Payload)
| evaluate bag_unpack(Payload)

['events.all'] 
| where FullName_Name == 'FTUE_Funnel'
| project Payload = parse_json(EventData.Payload.SuperProperties)
| evaluate bag_unpack(Payload)

这些是有效的，但它们是单独查询的，这并不理想。

['events.all'] 
| where FullName_Name == 'FTUE_Funnel'
| extend Payload = parse_json(EventData.Payload)
| project-away EventData
| evaluate bag_unpack(Payload,'extra_')

这个也可以，它查询有效负载内容，但将Superproperties放在一列中+查询有效负载之外的任何内容，这对我来说很好

谢谢！

Answer 1

bag_unpack((

datatable(event:dynamic)
[
dynamic
(
{
"SchemaVersion": 1,
"FullName_Namespace": 2,
"FullName_Name": 3,
"Entity_Id": 4,
"Entity_Type": 5,
"EntityLineage_title": 6,
"EventData": {
"Timestamp": 7,
"PayloadContentType": "Json",
"EntityLineage": {
"master_player_account": 8,
"title_player_account": 9,
"namespace": 10,
"title": 11
},
"SchemaVersion": 12,
"Originator": {
"Type": 13,
"Id": 14
},
"OriginInfo": {
"Timestamp": 15
},
"FullName": {
"Namespace": 16,
"Name": 17
},
"Payload": {
"TimeToCompleteSubStage": 18,
"TimeToCompleteStage": 19,
"SuperProperties": {
"GameSessionID": 20,
"PlayFabId": 21,
"Version": 22,
"Index": 23,
"Time": 24
},
"IdStageFrom": 25,
"SubStageId": 26,
"IdStageTo": 27
},
"Id": 28,
"Entity": {
"Type": 29,
"Id": 30
}
},
"EventId": 31,
"Timestamp": 32,
"EntityLineage_title_player_account": 33,
"EntityLineage_master_player_account": 34,
"EntityLineage_namespace": 35,
"ExperimentVariants": 36
}
)
]
| extend Payload = event.EventData.Payload
| project-away event
| evaluate bag_unpack(Payload)
| evaluate bag_unpack(SuperProperties, "SuperProperties_")

TimeToCompleteStage><1th>SuperProperties_Index版本><20>23><22>

IdStageFrom	IdStageTo	TimeToCompleteSubStage	SuperProperties_NameSessionID
25	27	26	19	18	21	24