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
v-micsh-msft
Community Support
Community Support

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
v-micsh-msft
Community Support
Community Support

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

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

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

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (2,523)