jsonazure-data-explorerkqlplayfab

Can I extract multiple nested json properties in kusto (KQL)?


I have telemetry events send to playfab. In those events I want to query the content of the Payload. I'm able to do it, but nested properties are all in the same column. I can use the following to extract everything in my event BUT for the SuperProperties nested in Payload. Superproperties are all in the same column

Here is the structure of my event

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

What I've tried

If found a similar question here, but i'm not sure to fully understand the question OR the answer. Even after looking at this documentation which seems to maybe explain how to do it. The reason ? Because my knowledge in Kusto and even programming in general is basic.

So, is there someone that could try to explain if this is possible to get all the properties under Payload, even the superproperties which are nested in Payload in their specific column ? If so, how to do it ? It does not matter if I query anything else outside the Payload or not

Here are my attempts

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

These ones work, but they are queried separately, which is no ideal.

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

This one also work, it queries Payload content BUT put Superproperties in one column + query anything outside payload, which is fine with me

Thank you !


Solution

  • 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_")
    
    IdStageFrom IdStageTo SubStageId TimeToCompleteStage TimeToCompleteSubStage SuperProperties_GameSessionID SuperProperties_Index SuperProperties_PlayFabId SuperProperties_Time SuperProperties_Version
    25 27 26 19 18 20 23 21 24 22

    Fiddle