How to parse a json file in a blob within a logic app

Question

How to parse a json file in a blob within a logic app

Nithin Vanam 286

Hello,

I am trying to create a logic app that:

Triggers on creation of a file within a ADL container ( using Azure Event Grid Trigger)
Get the file name through the blobUrl property
Read the contents of the json file using "Get blob content using path" action
Parse the json content which is a set of objects (the file is created using the ADF copy activity) to perform subsequent updates in CDS.

But when i try to parse the json conent, i get an error : BadRequest. The property 'content' must be of type JSON in the 'ParseJson' action inputs, but was of type 'application/octet-stream'.

Here is my logic app:

The json file in the ADL container has data in the below format.

And for the parse json action i used only one line item of the json file as using more than one line of data was resulting in an error to generate the below schema:

{  
    "properties": {  
        "BALANCE_AMT": {  
            "type": "integer"  
        },  
        "BALANCE_DATE": {  
            "type": "string"  
        },  
        "BOOKING_NO": {  
            "type": "integer"  
        },  
        "COMPONENT_NO": {  
            "type": "integer"  
        },  
        "COMPONENT_STATUS": {  
            "type": "string"  
        },  
        "CREATED_BY": {  
            "type": "string"  
        },  
        "CREATED_DATE": {  
            "type": "string"  
        },  
        "DISCOUNT_AMT": {  
            "type": "integer"  
        },  
        "EX_GRATIA_AMT": {  
            "type": "integer"  
        },  
        "PAYMENT_AMT": {  
            "type": "number"  
        },  
        "SEASON": {  
            "type": "integer"  
        },  
        "TERMINATION_CODE": {  
            "type": "string"  
        },  
        "TOTAL_COMM_AMT": {  
            "type": "integer"  
        },  
        "TOTAL_GROSS_AMT": {  
            "type": "number"  
        },  
        "TOTAL_NET_AMT": {  
            "type": "number"  
        },  
        "UNIQUE_KEY": {  
            "type": "string"  
        },  
        "VERSION_NO": {  
            "type": "integer"  
        }  
    },  
    "type": "object"  
}

Could someone please guide me as to how can i get past this issue? From the error it looks like the output of Get Blob contents action is in a different format to what Parse Json is expecting. Please suggest how can i change the format of the output to be compatable with the Parse JSON action? And how do i generate the schema for a json file that is a set of objects?. Thanks in advance.

Accepted answer

0 additional answers

Your answer

Answer 1

@Nithin Vanam As per the screenshot the file that is created has the below content which is incorrect json therefore the error is expected. If you see the below file then it has a single element of json per line and while parsing the single line text content should work as expected. But if you want to parse the full content of the file then you need to convert it to array and then parse.

{"SEASON":2020, "BOOKING_NO":100024,"COMPONENT_NO":500034, "VERSION_NO":9,"TOTAL_GROSS_AMT":7163.5, "TOTAL_COMM_AMT":0.0,"TOTAL_NET_AMT":7163.5,"PAYMENT_AMT":5.5, "BALANCE_AMT":7158.0,"BALANCE_DATE":"2020-05-31T00:00:00","EX GRATIA AMT":0.0, "CREATED BY":"1leung", "CREATED DATE":"2020-04-20115:31:43", "DISCOUNT AMT":0.0,"COMPONENT STATUS":"I","TERMINATION_CODE":"13","UNIQUE_KEY": "5000342020"}  
{"SEASON":2020, "BOOKING_NO":100024,"COMPONENT_NO":500034, "VERSION_NO":9,"TOTAL_GROSS_AMT":7163.5, "TOTAL_COMM_AMT":0.0,"TOTAL_NET_AMT":7163.5,"PAYMENT_AMT":5.5, "BALANCE_AMT":7158.0,"BALANCE_DATE":"2020-05-31T00:00:00","EX GRATIA AMT":0.0, "CREATED BY":"1leung", "CREATED DATE":"2020-04-20115:31:43", "DISCOUNT AMT":0.0,"COMPONENT STATUS":"I","TERMINATION_CODE":"13","UNIQUE_KEY": "5000342020"}  
{"SEASON":2020, "BOOKING_NO":100024,"COMPONENT_NO":500034, "VERSION_NO":9,"TOTAL_GROSS_AMT":7163.5, "TOTAL_COMM_AMT":0.0,"TOTAL_NET_AMT":7163.5,"PAYMENT_AMT":5.5, "BALANCE_AMT":7158.0,"BALANCE_DATE":"2020-05-31T00:00:00","EX GRATIA AMT":0.0, "CREATED BY":"1leung", "CREATED DATE":"2020-04-20115:31:43", "DISCOUNT AMT":0.0,"COMPONENT STATUS":"I","TERMINATION_CODE":"13","UNIQUE_KEY": "5000342020"}

As I can see the one json element is in a single line of your text file so the best would be to convert your text using replace \n with the , and adding the array braces [(yourcontent)].

So in my workflow, I am initializing the variable and replacing the content, and creating the array of JSON elements so it has the right content for Parse Json action.

Code View for initializing variable input

   "Initialize_variable": {  
                    "inputs": {  
                        "variables": [  
                            {  
                                "name": "input",  
                                "type": "string",  
                                "value": "[@{replace(body('Get_blob_content_using_path_(V2)'),'\n',',')}]"  
                            }  
                        ]  
                    }

Note: When you specify \n and save the workflow then in code view it will be \n as it is expected behaviour as it treats as \n i.e. text rather than new line but for newline you need to remove extra \ from code view.

In the schema, I have used the below formatted text that is the right JSON to generate the schema.

[{"SEASON":2020, "BOOKING_NO":100024,"COMPONENT_NO":500034, "VERSION_NO":9,"TOTAL_GROSS_AMT":7163.5, "TOTAL_COMM_AMT":0.0,"TOTAL_NET_AMT":7163.5,"PAYMENT_AMT":5.5, "BALANCE_AMT":7158.0,"BALANCE_DATE":"2020-05-31T00:00:00","EX GRATIA AMT":0.0, "CREATED BY":"1leung", "CREATED DATE":"2020-04-20115:31:43", "DISCOUNT AMT":0.0,"COMPONENT STATUS":"I","TERMINATION_CODE":"13","UNIQUE_KEY": "5000342020"},  
{"SEASON":2020, "BOOKING_NO":100024,"COMPONENT_NO":500034, "VERSION_NO":9,"TOTAL_GROSS_AMT":7163.5, "TOTAL_COMM_AMT":0.0,"TOTAL_NET_AMT":7163.5,"PAYMENT_AMT":5.5, "BALANCE_AMT":7158.0,"BALANCE_DATE":"2020-05-31T00:00:00","EX GRATIA AMT":0.0, "CREATED BY":"1leung", "CREATED DATE":"2020-04-20115:31:43", "DISCOUNT AMT":0.0,"COMPONENT STATUS":"I","TERMINATION_CODE":"13","UNIQUE_KEY": "5000342020"},  
{"SEASON":2020, "BOOKING_NO":100024,"COMPONENT_NO":500034, "VERSION_NO":9,"TOTAL_GROSS_AMT":7163.5, "TOTAL_COMM_AMT":0.0,"TOTAL_NET_AMT":7163.5,"PAYMENT_AMT":5.5, "BALANCE_AMT":7158.0,"BALANCE_DATE":"2020-05-31T00:00:00","EX GRATIA AMT":0.0, "CREATED BY":"1leung", "CREATED DATE":"2020-04-20115:31:43", "DISCOUNT AMT":0.0,"COMPONENT STATUS":"I","TERMINATION_CODE":"13","UNIQUE_KEY": "5000342020"}]

There can be other multiple ways of making your content to the right JSON format like looping through your content or splitting to array etc.

Feel free to get back to me if you need any assistance.

Nithin Kothakota Vanam 1 Reputation point

2021-11-29T06:44:23.373+00:00

Hi @MayankBargali-MSFT ,

Thank you for your help. I understood that the data in my json file is not of the right format and that i need to create it as an array of json records to begin with.

I used a test file with the right format but still had an issue with the output of "Get Blob Content From Path" action which was returning data in "octet-stream". So i had to use the base64ToString function to convert this data and then using the parse function did the trick.

Here is my update flow:

The expression used in Compose 5 is:

replace(base64ToString(body('Get_Blob_Content')?['$content']),'/n',',')

Could you suggest if there is anyway to convert the file content from a list of json items to and array using any of the functions? If so could you please share a quick sample
MayankBargali-MSFT 70,936 Reputation points Moderator

2021-11-30T08:59:59.83+00:00

@Nithin Vanam If there are JSON items and you want these items as an array then you need to define the array variable and loop it in through the JSON item and add it to your array variable.

Share via

How to parse a json file in a blob within a logic app

0 additional answers

Your answer