Parsing json of multiple formats

[V55]

Hello,

As per recent update from Azure monitor, from nov 1,2018 onwards, the log data will be pushed into blob in json lines format.

Ref - https://docs.microsoft.com/en-us/azure/monitoring-and-diagnostics/monitor-diagnostic-logs-append-blo...

We use powerBI to load and analyse those json data from azure blob.

Till now, we have been using json.Document method to parse the current json format.

But now, the json.Document method doesn't seem to be parsing the json line format. It throws 'We found extra characters at the end of JSON input'.

So, I happened to find from other posts that to solve this, I had to parse it as a text file and then convert it to json.

-------------------------------------------------------------------------------------------------------------

let
    FxFixJson = (jsonfilepathname as text) =>
let
    Source = AzureStorage.Blobs("https://MYBLOBSTOREACCT.blob.core.windows.net/"),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Content", "Name"}, {"Data.Content", "Data.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = jsonfilepathname)),
    #"Data Content" = #"Filtered Rows"{0}[Data.Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Data Content",null,null,1252)}),
    #"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"deviceId", "messageId"}, {"deviceId", "messageId"})
in
    #"Expanded Column1"
in
    FxFixJson

-------------------------------------------------------------------------------------------------------------

My concern is that my current parsing logic should support both current json format and upcoming json lines format.
I dont see anything to distinguish between both the formats (for eg, the extension is same for both the formats).

I am wondering if this has to be a trial and error method.

Like Parse with json.Document and if it fails, then parse it as text and convert to json and it might impact performance.

Is there a cleaner way of parsing of both the json formats? I dont see json lines format is natively supported by powerBI yet.

[v-yuezhe-msft]

@V55，

Is there any possibility that you download the JSON file to local drive , then use JSON connector in Power BI Desktop to connect to it?

Also you can share the JSON file here, I will test it in my Desktop.

Regards,
Lydia

[V55]

Hi @v-yuezhe-msft,

Thanks for the response.

I have actually downloaded the json to local drive and tried with json connector. It did not work, giving same error - "we found extra characters at the end of json input".

Just to give context, our customers are directly fetching the data from azure blobs. We use blob connector to fetch json data and parse. Now, after recent azure monitor change, it looks like we need to have two kind of parsing to support current json format and jsonline format (to support backward compatibility).

Current json format:

{
    "records": [
        {
            "time": "2016-01-05T01:32:01.2691226Z",
            "resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT",
            "operationName": "VaultGet",
            "operationVersion": "2015-06-01",
            "category": "AuditEvent",
            "resultType": "Success",
            "resultSignature": "OK",
            "resultDescription": "",
            "durationMs": "78",
            "callerIpAddress": "104.40.82.76",
            "correlationId": "",
            "identity": {
                "claim": {
                    "http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX",
                    "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com",
                    "appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX"
                }
            },
            "properties": {
                "clientInfo": "azure-resource-manager/2.0",
                "requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01",
                "id": "https://contosokeyvault.vault.azure.net/",
                "httpStatusCode": 200
            }
        },
        {
            "time": "2016-01-05T01:33:56.5264523Z",
            "resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT",
            "operationName": "VaultGet",
            "operationVersion": "2015-06-01",
            "category": "AuditEvent",
            "resultType": "Success",
            "resultSignature": "OK",
            "resultDescription": "",
            "durationMs": "83",
            "callerIpAddress": "104.40.82.76",
            "correlationId": "",
            "identity": {
                "claim": {
                    "http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX",
                    "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com",
                    "appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX"
                }
            },
            "properties": {
                "clientInfo": "azure-resource-manager/2.0",
                "requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01",
                "id": "https://contosokeyvault.vault.azure.net/",
                "httpStatusCode": 200
            }
        }
    ]
}

Json line format:

{"time": "2016-01-05T01:32:01.2691226Z","resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT","operationName": "VaultGet","operationVersion": "2015-06-01","category": "AuditEvent","resultType": "Success","resultSignature": "OK","resultDescription": "","durationMs": "78","callerIpAddress": "104.40.82.76","correlationId": "","identity": {"claim": {"http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com","appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX"}},"properties": {"clientInfo": "azure-resource-manager/2.0","requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01","id": "https://contosokeyvault.vault.azure.net/","httpStatusCode": 200}}
{"time": "2016-01-05T01:33:56.5264523Z","resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT","operationName": "VaultGet","operationVersion": "2015-06-01","category": "AuditEvent","resultType": "Success","resultSignature": "OK","resultDescription": "","durationMs": "83","callerIpAddress": "104.40.82.76","correlationId": "","identity": {"claim": {"http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com","appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX"}},"properties": {"clientInfo": "azure-resource-manager/2.0","requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01","id": "https://contosokeyvault.vault.azure.net/","httpStatusCode": 200}}

[V55]

I have made changes as per below logic.

Parse data with json parser

-> If success, then expand json data and get the records.

-> if fails, then parse using custom function to convert to json -> then expand data and get the records

Let me know if there is any issue with the below code.

//This query fetches data from the storage account blobs and converts it into expected data format

let    
    ParseJsonLine = (#"input content" as binary) => let        
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"input content",null,null,1252)}),
    #"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document)    
in
    #"Parsed JSON",


    //StorageAccount is a global user given parameter which specifies the storage account where the data is present
    Source = AzureStorage.Blobs(#"Storage Account"),
    
    //ContainerName is a local parameter which is fixed for every user
    Container = Source{[Name=Parameters[ContainerName]]}[Data],       

    //Content column contains the JSON we are interested in
    RemoveOtherColumns = Table.SelectColumns(Container,{"Content"}),
    
    //Expand each JSON content value with encoding as 1252
    ReadAllJsonBlobs = Table.AddColumn(RemoveOtherColumns , "JSONContent", each Json.Document([Content],1252)),

    // current Json format will parse - rows without error will return that json format
    CurrentJsonFormatTable = Table.RemoveRowsWithErrors(ReadAllJsonBlobs, {"JSONContent"}),
     //Expand JSON record to a list for each blob
    ExpandJSON = Table.ExpandRecordColumn(CurrentJsonFormatTable, "JSONContent", {"records"}, {"records"}),
    //Expand each list of records to individual records 
    ExpandRecords = Table.ExpandListColumn(ExpandJSON , "records"),
    CurrentJsonFormatRecords = Table.RemoveColumns(ExpandRecords,{"Content"}),

    // json line format will fail with json parser - rows without error will return that json format
    JsonLineFormatTable = Table.SelectRowsWithErrors(ReadAllJsonBlobs, {"JSONContent"}),     
    ReadAllJsonBlobs1 = Table.AddColumn(JsonLineFormatTable, "JSONContent1", each ParseJsonLine([Content])),
    RemoveOtherColumns1 = Table.RemoveColumns(ReadAllJsonBlobs1,{"Content", "JSONContent"}),
    JsonLineFormatRecords = Table.ExpandTableColumn(RemoveOtherColumns1, "JSONContent1", {"Column1"}, {"records"}),   

    // Combining both current format and jsonline format records
    CombinedTable = Table.Combine({CurrentJsonFormatRecords, JsonLineFormatRecords}),
    //Expand each record to Shoebox data format
    ExpandProperties= Table.ExpandRecordColumn(CombinedTable, "records", {"properties", "time", "resourceId", "operationName", "category", "resultType"}, {"properties", "AsOnDateTime", "resourceId", "operationName", "category", "resultType"})
in
    ExpandProperties