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

Parse JSON output into a usable string

Hi,

 

I am connecting to Dynamics 365 and returning a list of records. For each record I am then cycling through and using the API to return a JSON object like this:

 

{

  "@odata.context": "https://wtin.crm4.dynamics.com/api/data/v8.2/$metadata#sectors(new_sector1)",

  "value": [

    {

      "@odata.etag": "W/\"1067116\"",

      "sector": "Apparel > Trousers",

      "sectorid": "df07b87c-4695-e611-80e8-1458d05b422c"

    },

    {

      "@odata.etag": "W/\"1067122\"",

      "sector": "Apparel > Nightwear/Sleepwear",

      "sesector": "eb07b87c-4695-e611-80e8-1458d05b422c"

    }

  ]

}

 

What I want to do is build a string that uses just the "sector" keys separated with a character, say a bar '|' character from the JSON, so in the above case I would like to be able to return:

 

Apparel > Trousers | Apparel > Nightwear

 

I will then use the string in a further action.

 

The JSON is returned within a 'for each' loop. I have tried all sorts of join and compose data operation combinations, but can't figure it out.

 

Anyone got any pointers?

 

Many thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Parse JSON output into a usable string

Hi @SteveA,

 

Please take a try with the code below under compose Action (Should be added in the Apply to each ) and see if it could return the actual string you wanted:

 

"@concat(first(item()?['Value'])?['sector'],'|', last(item()?['Value'])?['sector'])"

This assume you only have two elements in the Value array.

Besides, would you please share more details for the entity you used here?

If you are using the the List recrods action, then I may take some tests from my side.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Re: Parse JSON output into a usable string

Hi @SteveA,

 

Please take a try with the code below under compose Action (Should be added in the Apply to each ) and see if it could return the actual string you wanted:

 

"@concat(first(item()?['Value'])?['sector'],'|', last(item()?['Value'])?['sector'])"

This assume you only have two elements in the Value array.

Besides, would you please share more details for the entity you used here?

If you are using the the List recrods action, then I may take some tests from my side.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

SteveA
Frequent Visitor

Re: Parse JSON output into a usable string

Michael, thanks!

 

This works well, although I had to make a slighy amendment and place a parse JSON action prior to the compose and use this:

 

@concat(first(body('parsesectors')?['Value'])?['sector'],'|', last(body('parsesectors')?['Value'])?['sector'])

 

It works perfect, but, unfortunately I could have more than two items in the array. This is a great help though as I'm a step closer now.

 

What I really need to be able to do it exactly this, but up to a notional limit of say 5 or 6 values, which would be the maximum.

 

Cheers!

SteveA
Frequent Visitor

Re: Parse JSON output into a usable string

This is not elegant at all!! But it does work. This builds a list separated with ' | ' characters for the first 5 sectors in the JSON.

 

@concat(first(body('getsectors')?['Value'])?['sector'],' | ', first(skip(body('getsectors')?['Value'],1))?['sector'],' | ', first(skip(body('getsectors')?['Value'],2))?['sector'],' | ', first(skip(body('getsectors')?['Value'],3))?['sector'],' | ', first(skip(body('getsectors')?['Value'],4))?['sector'],' | ', first(skip(body('getsectors')?['Value'],5))?['sector'] )

 

All I need to do now is work out how to surpress the ' | ' charcaters when the relevant sector is null.

 

Thanks!

Anonymous
Not applicable

Re: Parse JSON output into a usable string

Hi @SteveA,

 

I would like to achieve similar results as you described. But I'm absolut newbie in this theme. Could you provide some more info or screenshots of the flow about how you get this work.

 

Kind regards,

Mikhail

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (7,351)