JSON unable to return specific property value

[Powerline]

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.

[MarconettiMarco]

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

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

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]

@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. 

[MarconettiMarco]

Hello @Powerline 

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

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

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

BR,

Marco

[Powerline]

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.

[Powerline · Accepted solution]

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

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.