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

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (86,353)