cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

 

1 REPLY 1
Super User III
Super User III

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.

 

image.png

 

Next we need to construct the data array using apply to each loop 

 

image.png

 

Above I used the expression as follows:

 

split(string(outputs('Compose_2')), ':')

 

Under Compose 4

 

"Heading" : "@{outputs('Compose_3')[0]}", "Text" : "@{outputs('Compose_3')[1]}"

 

Finally under the array variable Data you should have an array similar to the mock you mentioned earlier.

 

Note: I haven't added "Message": "Ok. found" string to the array. 

 

Thanks

 

 

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (20,352)