cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
philcatterall
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
abm
Super User
Super User

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 blogPower Automate Video Tutorials

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,947)