cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Powerline
Regular Visitor

JSON unable to return specific property value

Hi,
I am looking at how I could use Power Virtual Agents / Power Automate instead of the bot SDK, seeing what the limitations are. I have run into a problem working with JSON, trying to navigate to a property to get a value.


Dummy JSON:

{
    "locationz": { 
        "woods":"Branston",
        "field":"Rick",
        "house":"burger"
    },
    "check":"yes"
}

 

I've checked the above and can retrieve Branston outside of Power Automate, so that's valid.

 

The JSON file is in OneDrive and using 'Get File Content' to access the JSON.

In Power Automate I have tried two things with the same results:

  • Using Compose: body('Get_file_content')
  • Or Compose: outputs('Get_file_content')?['body']

Either of the above returns the JSON above, as I would expect, so the "query" and file retrieval are both working, as are the outputs.

 

It goes wrong when I try to retrieve just the 'locationz' property - using the following, both of which should work, according to the docs:

  • Compose: outputs('Compose')?['locationz']
  • Or Compose: outputs('Get_file_content')?['body']?['locationz']

As soon as I try and go to the next level in JSON the result is coming back empty.

 

I have tried:

  • Different variations of ?['body']?['locationz'] in both of the scenarios above, adding and removing elements
  • For the JSON file itself, I have tried turning 'Infer Content Type' on and off

Any ideas? I have searched and found a bunch of different docs and posts on how to get JSON - tomriha's Let's Power Automate was very helpful. I want to just navigate to the property value I want rather than having add steps to parse JSON - I have not looked into that as I don't want to add more complexity than I would using the sdk. Hoping for someone to tell me I'm doing something stupid.

1 ACCEPTED SOLUTION

Accepted Solutions

Building on what Marco shared led me to create this which has solved it, having looked at a few posts elsewhere:

powerautomate.png

 

Get File Content is pointed at a JSON file in OneDrive. Then the flow:

 

For the action, fixing: 

json(base64ToString(body('Get_File_content')['$content']))

 

And for digging: 

outputs('fixing')?['locationz']?['woods']

The solution goes to the property for woods (which is hardcoded here), next step will be mapping this to a user value input from the Power Virtual Agent.

The reason I am using this method instead of Parse JSON is it allows anyone to update the underlying JSON and dynamically call that from a bot without having to alter the Flow, Parse JSON action; for example if someone added "locationz": { "road":"value"... 

Parse JSON looks like a good option where the schema is fixed.

View solution in original post

5 REPLIES 5
MarconettiMarco
Super User
Super User

Hello @Powerline ,

have you tried to use the "Parse JSON" action? you can put the Content based on your Trigger or previous Action, then, by clicking on the "Generate from sample", add your JSON payload

MarconettiMarco_0-1657633770890.png

 

I've tested your body, and I'm able to get all the properties:

MarconettiMarco_1-1657633901696.png

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

 

 

Powerline
Regular Visitor

@MarconettiMarco thank you for that. I had seen this approach / option - as mentioned I want to get the option of navigating to the value as it's simpler so haven't explored this approach. Direct navigation is also more flexible, if it works. 

Hello @Powerline 

I've tried to use the following expression and it works:

outputs('Compose')?['locationz']

 

MarconettiMarco_0-1657635007888.png

 

MarconettiMarco_1-1657635064216.png

 

 

Are you sure that the Action name you want to grab is named "Compose"?

 

BR,

Marco

 

 

That's not worked for me but after your post @MarconettiMarco I added the Parse JSON action and getting an error now which has highlighted the problem, the way Power Automate is treating the file: 

"$content-type""application/octet-stream"
Really appreciate the input, given me something solid to chase down.

Building on what Marco shared led me to create this which has solved it, having looked at a few posts elsewhere:

powerautomate.png

 

Get File Content is pointed at a JSON file in OneDrive. Then the flow:

 

For the action, fixing: 

json(base64ToString(body('Get_File_content')['$content']))

 

And for digging: 

outputs('fixing')?['locationz']?['woods']

The solution goes to the property for woods (which is hardcoded here), next step will be mapping this to a user value input from the Power Virtual Agent.

The reason I am using this method instead of Parse JSON is it allows anyone to update the underlying JSON and dynamically call that from a bot without having to alter the Flow, Parse JSON action; for example if someone added "locationz": { "road":"value"... 

Parse JSON looks like a good option where the schema is fixed.

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

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

Users online (2,109)