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

Trouble with Parse JSON schema

Hello,

I'm having issues with a correct schema for a Parse JSON.   I'm getting the JSON from an HTTP response and that part is fine.  An example response is below.  I've used this and the actual response to "generate schema" and it doesn't seem to work correctly.  The amount of "jobcodes" will be 1-50.  I need to build an array consisting of the number for the jobcode either using the actual jobcode or the id field.  I've tried....    After the Parse JSON, I invoke an apply to each, use dynamic value "jobcodes" from Parse JSON.  Attempt to append to array variable but if I use "jobcodes" it fails because this is an object.  I can't seem to use "id" because its treating each "id" separately as @{body('Parse_JSON')?['results']?['jobcodes']?['26508071']?['id']} for example.   I wont know the number so I cant step through that way.  I feel like I'm just doing something wrong and not seeing it as I have done similar processes before in flow/automate.

 

Drilled to one line, how can I parse the below JSON and come up with 17288279,17288283 in an array with the condition that it might be two values or 50.

 

{
"results": {
"jobcodes": {
"17288279": {
"id": 17288279,
"parent_id": 0,
"assigned_to_all": false,
"billable": false,
"active": true,
"type": "regular",
"has_children": false,
"billable_rate": 0,
"short_code": "asm",
"name": "Assembly Line",
"last_modified": "2018-07-12T21:13:14+00:00",
"created": "2018-05-28T20:18:17+00:00",
"filtered_customfielditems": "",
"required_customfields": [],
"locations": [],
"connect_with_quickbooks": true
},
"17288283": {
"id": 17288283,
"parent_id": 0,
"assigned_to_all": false,
"billable": false,
"active": true,
"type": "regular",
"has_children": false,
"billable_rate": 0,
"short_code": "dev",
"name": "Development Team",
"last_modified": "2018-05-28T20:19:33+00:00",
"created": "2018-05-28T20:19:33+00:00",
"filtered_customfielditems": "",
"required_customfields": [],
"locations": [],
"connect_with_quickbooks": false
}
}
},
"more": false
}

1 ACCEPTED SOLUTION

Accepted Solutions
abm
Super User
Super User

Hi @DHepler 

 

Please follow the below steps. First step pass your json values under the Parse JSON step.

 

image.png

 

Above Compose 2 expression is as follows:

xml(body('Parse_JSON')?['results'])
 
Next compose expression is:
xpath(xml(outputs('Compose_2')),'jobcodes//id')
 
Next add the below steps:
 
image.png
 
Above Select action step pass the output from the previous Compose action step. For the Map step expression is as follows:
 
base64ToString(item()?['$content'])
 
Final compose 3 expression is:
replace(replace(string(body('Select')),'<id>',''),'</id>','')
 
My run output is as follows:
 
image.png

 

 



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

View solution in original post

3 REPLIES 3
eliotcole
Super User
Super User

There's an XML hack for this out there, from John Liu that will likely sort you out on this. I'm not saying this to answer the question, just ... if you don't get an answer for a while, you could look it up, it's really good.

 

I have used it in a few answers on this site (if you can find them, then awesome!) and personally a few times to sort data like this.

 

But you'll need to be clever, here.

abm
Super User
Super User

Hi @DHepler 

 

Please follow the below steps. First step pass your json values under the Parse JSON step.

 

image.png

 

Above Compose 2 expression is as follows:

xml(body('Parse_JSON')?['results'])
 
Next compose expression is:
xpath(xml(outputs('Compose_2')),'jobcodes//id')
 
Next add the below steps:
 
image.png
 
Above Select action step pass the output from the previous Compose action step. For the Map step expression is as follows:
 
base64ToString(item()?['$content'])
 
Final compose 3 expression is:
replace(replace(string(body('Select')),'<id>',''),'</id>','')
 
My run output is as follows:
 
image.png

 

 



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
DHepler
Frequent Visitor

@abm,

This worked brilliantly!  Thank you so much.  Seems like a lot of hoops for what seems to be a simply thing but it works and that is all that matters.  

 

Thank you so much.

 

Dave

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,412)