Power Platform Community Forum Thread Details

How to parse a badly formed JSON API response

Hi, I'm using an API which unfortunately is not well structured. I can't change that but wondered if anyone has hit similar issues.

Here is an example of the (poorly formed) JSON returned.

{
 "Message": "Ok. found",
 "Data": [
 {
 "Company must update its website" : "By 20 February, Company must complete updates"
 },
 {
 "Company must provide a detailed statement" : "By 9 March, must provide a detailed statement"
 },
 {
 "Company must inform its customers" : "By on 9 March 2020, Company must provide evidence"
 }
 ]
}

It should, of course look something like this (Note this is my mock-up/representation).

{
 "Message": "Ok. found",
 "Data": [
 {
 "Heading" : "Company must update its website", 
 "Text" : "By 20 February, Company must complete updates"
 },
 {
 "Heading" : "Company must provide a detailed statement", 
 "Text" : "By 9 March, must provide a detailed statement"
 },
 {
 "Heading" : "Company must inform its customers",
 "Text" : "By on 9 March 2020, Company must provide evidence"
 }
 ]
}

Note that what I'm getting is an array of objects, but it is impossible to parse it because the property name is actually a value (a "heading" in this case).

I have sort of worked around it by assigning the Data Array to a string, stripping out the JSON delimiters ( namely '[' ']' '{' )and then creating an Array by using the Split function using the remaining '}'. But its clunky and not at all elegant.
Plus I don't get proper objects, which I will need for a slightly more complex transformation I need to do.
This workaround leaves me with the two properties (Heading and Text) concatenated and only separated by the JSON ':' delimiter.
e.g. a string like this

"Company must inform its customers : By on 9 March 2020, Company must provide evidence"

This is sort of OK for this example, but won't work for some other poorly formed API responses, so ideally what I want to is to convert this into a proper array of JSON objects, structured as in my mock-up above.
Maybe someone has encountered this challenge before or has some genius ideas.

Thanks all - Phil

Hi @philcatterall

You could iterate the Data array and get each item (eg: "Company must update its website" : "By 20 February, Company must complete updates") then use a split expression delimiter as ":" colon to get both values. Next append this in a string. Please see below.