How to get a specific value from a JSON in Power Automate

“Is it possible, maybe with an expression, to get only a specific value(s) from a JSON object in Power Automate, so I can work with them later in the flow?”


JSON is the basic data format used in Power Automate. Whenever there’s an output of any action, it’ll be a JSON on the background. Power Automate will then automatically parse that JSON, and provide the values among the available dynamic content. And if it doesn’t do it automatically, there’s still the ‘Parse JSON’ action to parse any JSON on demand. But there’s also a third option, accessing the value with an expression. You can navigate through a JSON and take only the value you need. Instead of using the whole JSON that’ll fit a JSON schema, you can just pick the specific property.

JSON format

Let’s take the output from ‘Get items’ as an example. If you check the whole output and copy it into a code editor (I use Visual Studio Code), you’ll see a similar structure as below.

That is the output of ‘Get items’ in JSON format. As the ‘statusCode’ and ‘headers’ is not that relevant, you can concentrate only on the ‘body’ part with the actual items data.

Note: the colours are mentioned only for better explanation, they’re added by the code editor.

The blue colour strings are properties, the content after the : are their values. Translated to the Power Automate user interface, blue are the available dynamic contents, and the value is the actual data you’ll get, e.g.

ID = 2
Title = Approval needed for ....
ApproverSingle = a lot of user information
Power Automate specific value JSON

Note: the JSON works with the column internal name. That’s why the name of the dynamic content can be different from the property name in JSON.

Navigating the JSON

If you use any of the available dynamic contents, you’ll see that the code behind corresponds to the location in that JSON. The navigation is done by the ?[‘propertyName’] string added to the source reference expression. For example, to process the output body Power Automate will take output from ‘Get items’, and access the property “body”.

outputs('Get_items')?['body']

The result will be the whole “body” value.

Power Automate specific value JSON

If you want to go deeper, to the actual items, you can add the next property to the expression – “value”.

outputs('Get_items')?['body']?['value']
Power Automate specific value JSON

Following this syntax of adding ?[‘propertyName’] to the expression you can directly access any value in the JSON. It should contain the whole path that leads to the value, up to the column internal name.

Working with arrays in JSON

But only until you reach an array. The JSON notation has 2 types of brackets. The first being curly brackets { and } that define an object. As long as your path leads only through curly brackets, you can just add one property after another.

The second bracket type are square brackets [ and ] that define an array. Following the example JSON you can recognise objects, arrays, and arrays that contain objects.

Power Automate specific value JSON

Since an array can contain multiple objects, you must select which object you want to access. Is it the first one? The last one? Or an object in between?

If you want the first object, you can use the first(…) expression, e.g. to get the first returned item:

first(outputs('Get_items')?['body']?['value'])

If you want the last one, you can use the last(…) expression, e.g. to get the last returned item:

last(outputs('Get_items')?['body']?['value'])

Or you can use an index in the array, e.g. to get the 2nd item:

outputs('Get_items')?['body']?['value'][1]

Once you deal with the array, you can continue building the path, e.g. take the ID of the first item…

first(outputs('Get_items')?['body']?['value'])?['ID']
or
outputs('Get_items')?['body']?['value'][0]?['ID']

… take the email of the first user in the ‘ApproverSingle’ column …

first(outputs('Get_items')?['body']?['value'])?['ApproverSingle'][0]?['Email']
or
outputs('Get_items')?['body']?['value'][0]?['ApproverSingle'][0]?['Email']
Power Automate specific value JSON

… or the ‘ApprovalStatus’ value.

outputs('Get_items')?['body']?['value'][0]?['ApprovalStatus']?['Value']

Summary

If you work with Power Automate, it’s very helpful to understand the underlying JSON and how to get a specific value. You don’t need the skills to create a whole JSON by yourself, but you should be able to read one. Because once you understand its structure and how to access the data, you’ll bypass many limitations from the user interface.

The example above was based on the outputs from ‘Get items’ action, but the principle is the same with every JSON. Start from the whole output and then navigate through the properties to the desired column value.

The only limitation of this approach is that it’ll always return only 1 value. If you need more values, e.g. all email addresses or all selected choices, you’ll need the xpath(…) solution instead. But that’s a limitation that you’ll encounter when using any other solution.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

One Comment

Add a Comment

Your email address will not be published. Required fields are marked *