如何在Kusto(KQL)中提取Payload下的嵌套JSON属性？

问题说明 #

发送至PlayFab的遥测事件中，Payload包含嵌套的SuperProperties属性，目前查询时只能将SuperProperties作为单列展示，需实现将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"
}

已尝试的KQL语句 #

1. 分开查询Payload和SuperProperties #

['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)

此方法可分别展开两类属性，但无法在同一结果集中展示所有字段。

2. 展开Payload但SuperProperties仍为单列 #

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

此方法仅展开Payload的一级属性，嵌套的SuperProperties仍作为单个动态列存在。

解决方案KQL语句 #

['events.all'] 
| where FullName_Name == 'FTUE_Funnel'
// 将Payload解析为动态对象
| extend Payload = parse_json(EventData.Payload)
// 提取嵌套的SuperProperties为单独动态对象
| extend SuperProps = Payload.SuperProperties
// 移除原Payload中的SuperProperties字段，避免重复
| extend Payload = bag_remove_keys(Payload, dynamic(['SuperProperties']))
// 展开Payload的一级属性到单独列
| evaluate bag_unpack(Payload)
// 展开SuperProperties的属性到单独列，添加前缀区分
| evaluate bag_unpack(SuperProps, 'Super_')

步骤说明 #

1. 筛选目标事件：指定查询的事件表和目标事件名称。
2. 解析Payload：将EventData中存储的JSON格式Payload转为Kusto动态对象。
3. 拆分嵌套属性：把Payload中的SuperProperties单独提取为一个独立的动态对象。
4. 清理原Payload：移除原Payload中的SuperProperties字段，防止后续展开时出现字段冲突。
5. 展开一级属性：使用bag_unpack将Payload的所有一级属性展开为单独列。
6. 展开嵌套属性：再次使用bag_unpack将SuperProperties的属性展开为单独列，并通过前缀Super_区分，避免与Payload的一级属性重名。

内容的提问来源于stack exchange，提问作者Steven