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

JSON to Excel with unkown object name

Hi,

 

I need help with how to achieve this.  I have a JSON object with several nested objects, where I need the values within the nested properties.  The problem is that if I use a parse JSON in Flow, the property is set with a name that changes:

 

{

  "results": {

   "requests": {

     "1234": {

        "id": 1234,

        "status": "active"

        "created": "2020-08-05T18:26:18+00:00"

        },

      "5678": {

          "id": 5678

          "status": "inactive"

          "created": "2020-06-02T12:35:29+00:00"

         }

     }

 

I don't seem to be able to pull the correct "id", "status", etc as an example because the parse JSON offers only: 1234.id or 5678.id so when the JSON has a different value for instance 9012 (as an example) it would not find a property because the parse JSON is looking for only 1234.id or 5678.id. 

 

I realize that I need a variable to hold the parent property / name, but I can't reference that in flow (at least I don't know how to) unless I put the entire thing in an array, but then I only get the top value since the entire JSON is in one array.

 

Any help someone could offer, would be appreciated.

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @DJC3 ,

 

You could create the flow as below:

1.png

last(split(outputs('Compose'),'.'))

 

Best Regards,

Alice

 

Community Support Team _ Alice Zhang
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

6 REPLIES 6
abm
Super User III
Super User III

Hi @DJC3 

 

Unfortunately this is not possible. The main challenge is "1234" or "5678" is dynamic. This need to be static to recognise the rest of the properties.

 

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

Hi @DJC3

 

You could take a try to extract the objects in Microsoft flow.

If you need to extract the string "id" from the string "1234.id" or "5678.id", please let m know and I would provide the solutions.

 

Best Regards,

Alice

 

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

Hi Alice,

 

That sounds like a promising idea.  Can you tell me how that is done?

 

Thanks!

Hi @DJC3 ,

 

You could create the flow as below:

1.png

last(split(outputs('Compose'),'.'))

 

Best Regards,

Alice

 

Community Support Team _ Alice Zhang
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

abm
Super User III
Super User III

Hi @DJC3 

 

I am glad that you found a solution for this. May I ask you that if the id value is changed then how you going to extract the id mappings? For me when I parse this in json it is referencing the id number internally

 

body('Parse_JSON')?['results']?['requests']?['5678']
 
Could you please explain how the solution is extracting different dynamic id's?
 
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
DJC3
Frequent Visitor

Hi @abm ,

 

I actually couldn't use the Parse_JSON at all.  I had to select the value from a string.  

 

I knew that I needed the value between ['requests'] and ['id'] so I had to convert to a string using

 

Compose:

xpath(xml(outputs('Compose')), 'string(/*/*/id)')

 
I got this essentially between Alice's response and checking some other posts similar to my request (Thanks to a post from John).  If I convert the JSON to XML and use xpath to pull the "ID", then I don't need to know what the middle part of the JSON path is since it is pulled into the string from the xpath and saved in the Compose.
 
I did this for each value that I needed and it worked like a charm.  I essentially get all of the values I need:  
 
xpath values flow.jpg In my case, I needed 8 values in total, but they came from 3 different nodes within the JSON, so I had 1 "Compose" action for each node, and then I created a compose action for each value (I just renamed the action so that I could easily find the outputs).
 
I am sure there is a different way of doing it but this seems to work and gives me what I need.

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

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 on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (27,725)