JSON Parser missing data points

[Anonymous]

Hi,

I am using a Parse JSON data operation to parse through an email body that consists of fairly simple JSON. The problem is that JSON Parser is not capturing all the data points in the email body, but leaves randomly ~1/4 of them blank. Any ideas?

As said, JSON Is simple: {"Case ID":"12","Sales Manager":"Test","Customer":"NYC","Country":"United States","Currency":"Dollars","Year":"2017","Price":"1234"}  (Shortened here to 7 data pairs, the actual file has ~30 data pairs). All json data fields are strings because of the source, i.e on purpose. I can take care of that problem when data is in database where i am planning to put it. And naturally I convert the email html to txt before parsing.

When troubleshooting this I see that output of the parser is e.g.:

Case ID : 12

Sales Manager : Test

Customer : (empty)

Country : United States

Currency : Dollars

Year : 2017

Price : (empty)

Places where the empty data appears change from time to time, but are always the same when same data is sent (i.e. if I don't change the input, they always appear in the same places).

JSON sample I use works fine in "Parse JSON" when creating the flow step, and in the next step I can connect the data points to respective db fields without any problems. 

[Anonymous · Accepted solution]

After another day of testing I noticed the problem was that JSON parser changed some of the empty spaces to \n and hence the database update missed them. I.e. "Sales Manager" became "Sales\nManager". Replacing spaces with underscores fixed the problem.

[v-xida-msft]

Hi @Anonymous,

Could you please share a screenshot of your flow's configuration?

The json data within my email Body as below:

I have made a test on my side and don't have the issue that you mentioned. My flow's configuration as below:

Witin Schema field of "Parse JSON" action, type the following formula:

{
    "type": "object",
    "properties": {
        "Case ID": {
            "type": "string"
        },
        "Sales Manager": {
            "type": "string"
        },
        "Customer": {
            "type": "string"
        },
        "Country": {
            "type": "string"
        },
        "Currency": {
            "type": "string"
        },
        "Year": {
            "type": "string"
        },
        "Price": {
            "type": "string"
        },
        "Case ID1": {
            "type": "string"
        },
        "Sales Manager1": {
            "type": "string"
        },
        "Customer1": {
            "type": "string"
        },
        "Country1": {
            "type": "string"
        },
        "Currency1": {
            "type": "string"
        },
        "Year1": {
            "type": "string"
        },
        "Price1": {
            "type": "string"
        },
        "Case ID2": {
            "type": "string"
        },
        "Sales Manager2": {
            "type": "string"
        },
        "Customer2": {
            "type": "string"
        },
        "Country2": {
            "type": "string"
        },
        "Currency2": {
            "type": "string"
        },
        "Year2": {
            "type": "string"
        },
        "Price2": {
            "type": "string"
        },
        "Case ID3": {
            "type": "string"
        },
        "Sales Manager3": {
            "type": "string"
        },
        "Customer3": {
            "type": "string"
        },
        "Country3": {
            "type": "string"
        },
        "Currency3": {
            "type": "string"
        },
        "Year3": {
            "type": "string"
        },
        "Price3": {
            "type": "string"
        },
        "Case ID4": {
            "type": "string"
        },
        "Sales Manager4": {
            "type": "string"
        },
        "Country4": {
            "type": "string"
        },
        "Currency4": {
            "type": "string"
        },
        "Year4": {
            "type": "string"
        },
        "Price4": {
            "type": "string"
        },
        "Customer4": {
            "type": "string"
        }
    }
}

The flow works successfully as below:

Please check if you have filled proper json schema witin Schema field of "Parse JSON" action. In addition, please consider take a try to clear cahe of your browser and re-create your flow to check if the issue is solved.

Also please check if you have filled proper json data within email body on your side.

Best regards,

Kris

[Anonymous]

Hi Kris,

thanks for testing this and sorry that I didn't mark it solved before. As I wrote above, the problem was that the parser (or html-to-text before that) changes some of the spaces to \n: 

Like you see above, space in Pricing manager is changed, space in "Case ID" is not. After this Sharepoint upload failed on those data fields where space was changed. Replacing a simple space with underscore fixed the problem.

Flow itself is exacty as you also made it: