Action "Parse JSON" Failed

[Anonymous]

Hello,

I am continuously receiving errors for my parsing JSON flow. I will include an example of how the JSON is below. I cannot seem to get past the "Parse JSON" element of the flow.

One error read:

"The 'from' property value in the 'query' action inputs is of type 'String'. The value must be an array."

Another read:

"ValidationFailed. The schema validation failed."

{
  "Form": {
    "Number": "000-000",
    "ExpirationDate": "4/30/2022"
  },
  "Product": "Aluminum",
  "HTSUSCode": "660",
  "PreviouslyGrantedER": null,
  "MetalClass": "Sheet",
  "RequestingOrg": {
    "OrgLegalName": "Example Company",
    "StreetAddress": "Example Street",
    "City": "Hadley",
    "State": "Massachusetts",
    "ZipCode": "01075",
    "HeadquartersCountry": "United States",
    "POCName": "ExampleName",
    "PhoneNumber": "400-600-0000\t\t",
    "EmailAddress": "example@example.com\t",
    "WebsiteAddress": null
  },
  "RequestingImporter": {
    "OrgLegalName": "Example Inc.",
    "StreetAddress": "Example St 2 3\t",
    "City": "South Hadley",
    "State": "Massachusetts",
    "ZipCode": "01075",
    "HeadquartersCountry": "United States",
    "POCName": "ExampleNamee",
    "PhoneNumber": "401-600-0000",
    "EmailAddress": "example2@example.com\t",
    "WebsiteAddress": null
  },
  "RequestingParent": {
    "OrgLegalName": null,
    "StreetAddress": null,
    "City": null,
    "State": null,
    "ZipCode": null,
    "HeadquartersCountry": null,
    "WebsiteAddress": null
  },
  "RequestingAuthRep": {
    "POCName": null,
    "POCOrganization": null,
    "CountryLocation": null,
    "PhoneNumber": null,
    "EmailAddress": null,
    "WebsiteAddress": null,
    "OtherInformation": null
  },
}

Schema:

{
  "type": "object",
  "properties": {
    "Form": {
      "type": "object",
      "properties": {
        "Number": {
          "type": "string"
        },
        "ExpirationDate": {
          "type": "string"
        }
      }
    },
    "Product": {
      "type": "string"
    },
    "HTSUSCode": {
      "type": "string"
    },
    "PreviouslyGrantedER": {},
    "MetalClass": {
      "type": "string"
    },
    "RequestingOrg": {
      "type": "object",
      "properties": {
        "OrgLegalName": {
          "type": "string"
        },
        "StreetAddress": {
          "type": "string"
        },
        "City": {
          "type": "string"
        },
    }
  }
}

Error Output:

[
  {
    "message": "Invalid type. Expected String but got Null.",
    "lineNumber": 0,
    "linePosition": 0,
    "path": "ProductClassification[29].Key",
    "schemaId": "#/properties/ProductClassification/items/properties/Key",
    "errorType": "type",
    "childErrors": []
  },
]

[ManishJain]

Hi @Anonymous ,

It simply is telling you to handle null values in all the fields. for eg. ProductClassification array has Key and value both are required fields. All you need to do add null like this :

"Key": {
"type": ["string","null"]
}

https://www.sharepointsiren.com/2018/10/flow-parse-json-null-error-fix/

Thanks

[Anonymous]

Hi @ManishJain , thanks for the suggestion. I would like to automate this so if I have to change something in the JSON itself then that would not work. Do you happen to be good with expressions? I would like to parse the JSON into SharePoint list.

[ManishJain]

Hi @Anonymous ,

What I have suggested should go in the schema part and if any of the parameters need to empty or null then this is the way to handle nulls in Parse JSON action.

Thanks

[Anonymous]

Thanks again for your suggestion @ManishJain . The problem with this is that I would have to manually edit the schema each time (at least that is what I am getting). This JSON is pulled from a SharePoint list. Each list item has JSON like this and it could be in the thousands. I am trying to find the easiest way to automatically parse this data into SharePoint without lifting a finger. Hope this makes sense.

Also, I am a visual person so if I am not understanding what you are saying, please provide a visual 😅

[ManishJain · Accepted solution]

Hi @Anonymous ,

you need to find out which fields can possibly be null , if you are not sure I would suggest for all string fields / columns add type : ["null","string"]

Thanks

[Anonymous]

I finally understood what you meant. Thanks for posting! @ManishJain 

[Anonymous]

Thanks for the tip,  it helped shape up the JSON schema.

[nathanbetters]

The problem with this approach (I have the same issue), is that the field cant be used later on in the workflow then if we do this. So when the json has actual data it cant be used.