“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
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.
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']
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.
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']
… 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.
Tom, you’ve done fantastic explantion of the getting value from JSON !
Great article thanks – I’ve used the index in an array to extract additional images of my product.
I’ve set this up to extract 10 images by their index number, however the issue I’ve run into is that flow crashes on extract 5, if for example there are only 4 images of the 10 exist for that particular product.
Any suggestions or alternative approaches on how to account for this would be appreciated.
Hello Shane,
if you’ve got various number of objects in the array then you can’t use indexes to access all the values. I don’t know what’s your plan with the images, but one solution would be to extract the objects in a loop – add ‘Apply to each’ to process the whole array (the expression without the [index]) and in that loop process the images (or store them in some variable similar to the last screenshot here: https://tomriha.com/get-data-from-http-response-without-parse-json-in-power-automate/).
Love This!
I am trying to parse the JSON that comes from a Survey Monkey survey response. I can get the body data, but I cant seem to be able to navigate the JSON effectively to get the data our of the ‘pages’ element, where the answers are stored, approx 3 levels down. Any ideas?
Hello Duane,
I never worked with Survey Monkey, but it shouldn’t matter how many levels deep the element is, just navigate through all the elements until you get there.
Thanks for this great article – it is exactly where i am on my project….however, i need to go one level more abstract and hoping you can help. Essentially, I need to itemize the names of all the columns of a sharepoint list. I understand how to do this in python…-> jsonObj[‘body’][‘value’][0].keys() ….Thoughts on how to do this in PowerAutomate?
Hello dt,
I don’t have any idea how to do this, I can’t even say if it’s possible or not.
Please note that there seems to be a typo.
Wrong:
first(outputs(‘Get_items’)?[‘body’]?[‘value’])?[‘ApproverSingle’][0]?[‘Email’]
Correct:
first(outputs(‘Get_items’)?[‘body’]?[‘value’])?[‘ApproverSingle’]?[‘Email’]
Hello Stephan,
there’s no typo, only confusing name of a column. 🙂 The ‘ApproverSingle’ column is actually a multiple people picker column as you can recognise by the [ ] brackets.
Thanks Tom – really great article! Works very smoothly to fetch output data.
Is there a different syntax to fetch specific Input data?
The input JSON can offer additional info that isn’t necessarily available elsewhere.
Fetching input data works as you’d expect for triggers – for example:
trigger()?[‘inputs’]?[‘parameters’]?[‘folderPath’]
– but what about getting input data for other type of actions?
Hello LCz,
I never tried this, but isn’t the trigger the only action that can have extra inputs? All the other actions in the flow should use outputs of one of the previous actions as the input.
Hey Tom,
Thanks for this, is there way for powerautomate to get the “statusCode”?
Thanks,
Ross
Hello Ross,
if you want the ‘StatusCode’ then use it instead of the ‘Body’, e.g. outputs(‘Get_items’)?[‘StatusCode’]
Hey Tom,
I am trying to grab a record with the largest value for a particular field. Do you know how I would filter the JSON to return this record?
Thanks,
Wes
Hello Wes,
the first idea would be to do it in three steps.
1. ‘Select’ only the value with the number from all the objects
2. Find the maximum from the numbers (like here: https://tomriha.com/how-to-get-the-highest-value-from-excel-rows-in-power-automate/)
3. ‘Filter array’ to search for the object that contains the highest value returned by step 2
Tom, nice job
question – how would you extract the following from the body/value
“{Identifier}”: “%2bMemos%252fTest.pdf”,
i can get to body(‘checkforfile’)?[‘value’][0]?[‘Identifier’] but nothing is retrieved
thx
Hello Stuart,
the column name must be exactly the same as in the JSON, including the curly brackets:
body('checkforfile')?['value'][0]?['{Identifier}']How can we parse json with specific fields output. For ex, only ID and Title as the output array.
Hello Vimal L,
with the ‘Select’ action: https://tomriha.com/how-to-select-specific-values-from-an-array-in-power-automate/. Extract the whole array from the JSON to use it as the input, and then select which values you want to extract from that array.
This is great!!! Now I understand how to reference objects and arrays in PA. Thank you very much dear sir! 🙂
This was so helpful! Thanks so much. Knowing the JSON structure helps me with UI limitations.
This is a great Article for those of us struggling with “low code” as Msoft advertises…. haha, its still not that easy.
Well written, but could you expand/edit upon how the numbers in closed brackets lead to pulling something from JSON script?
ie: the [0], listed in the first and/or outputs expressions isn’t clear. Do these lead to certain lines or values or properties within an array? Would we ever use a [1] or [2]?
Hello Jared,
the numbers in closed brackets are used to pick a specific item from an array, [0] is the first one, [1] is the second one, etc. This article has a few more examples: https://tomriha.com/how-to-split-file-name-for-further-processing-in-power-automate/
I have no arrays in my JSON code.. only values as denoted by { and } through my JSON code.
I don’t need to/can’t use PARSE JSON because I do not have an array (throws error).
That said, how Can I make 3 object values within curly brackets available for use later within my flow: (ie: include 3 objects from a Teams meeting created in an earlier step that are: joinUrl, conferenceId, and tollNumber)?
outputs(‘Create_a_Teams_meeting’)?[‘properyName’]?[‘joinUrl’]?[‘Value’]?[‘conferenceId’]?[‘value’]?[‘tollNumber’]?[‘value’]
I see them all in the JSON code using “compose”, but they are not available as dynamic content for individual items for use in email body/script.
Hello Jared,
each value needs its own expression to extract it. If the values come from the ‘Create a Teams meeting action it’ll look as below for the joinUrl:
outputs(‘Create_a_Teams_meeting’)?[‘body’]?[‘onlineMeeting’]?[‘joinUrl’]
I don’t see any conferenceId or tollNumber in the output so I can’t tell you how to get these, but it should look similar – the whole path in the JSON leading to the value.
Thank you Tom, this helped tremendously! What a great way to learn about JSON. It makes a lot of sense now. Thank you for putting this together for us!
Hey Tom!
This blog is so well explained. Thanks for creating this piece 🙂
I have a problem where I have to write expressions in a PA condition, that has values from a json object. But I’m not able to come up give the compare condition using the method you have specified. Is there any solution for this?
I read all your comments and still didn’t get an idea. Would really appreciate if you could help
My problem statement basically is to compare two price, say A and B, and to alert if price B is lesser or greater than the price A by 5%. My problems are to incorporate this formula and getting the dynamic values from json object
Hello Yasholekha,,100),5)
I’d build the expressions step by step as explained in this article (https://tomriha.com/how-to-combine-expressions-in-your-power-automate-flows/), you can even use a few ‘Compose’ actions for the pieces to make it easier.
1. Get the number A
2. Get the number B
3. Calculated 5% of number A – mul(div(
4. Check in condition whether A – B is more than the calculated 5%
Thank you!! This is the most comprehensive article I’ve found about traversing JSON in Power Automate. Bookmarking this page, Nice Job!
Hi Tom,
Thank you for sharing this with us, this is very helpful.
How can we filter an array using the powerx? Lets say in your example if I want to get an email by filtering on Display Name. This is what I am using. Is this correct?
equals(outputs(‘Get_items’)?[‘body’]?[‘value’]?[‘ApproverSingle’]?[‘DisplayName’], ‘Tomas Riha’)?[Email]
Thanks in advance
Hello GKV,
you’ll need two steps – firstly, the ‘Filter array’ action where you filter the items returned by ‘Get items’ using the display name. Once you have the array filtered you can access the email.
Hi Tom,
I’m trying to access ‘Id’ and ‘Name’ from the following (one of the last few lines of json):
{
“odata.metadata”: “https://m365x26117437.sharepoint.com/_api/$metadata#SP.ApiData.RoleAssignments”,
“value”: [
{
…
“RoleDefinitionBindings”: [
{
“odata.type”: “SP.RoleDefinition”,
“odata.id”: “https://m365x26117437.sharepoint.com/_api/Web/RoleDefinitions(1073741829)”,
“odata.editLink”: “Web/RoleDefinitions(1073741829)”,
“BasePermissions”: {
“High”: “2147483647”,
“Low”: “4294967295”
},
“Description”: “Has full control.”,
“Hidden”: false,
“Id”: 1073741829,
“Name”: “Full Control”,
“Order”: 1,
“RoleTypeKind”: 5
}
],
“PrincipalId”: 3
},
I’m using the following from inside a loop that goes through each value (which is top level array of what’s posted above):
items(‘Apply_to_each_value’)?[‘RoleDefinitionBindings’][1]?[‘{Id}’]
items(‘Apply_to_each_value’)?[‘RoleDefinitionBindings’][1]?[‘{Name}’]
I have tried quite a few different options and can’t seem to access that data. I can get it if I just call ‘roleDefinitionBindings’ but it brings back alllll the data that’s under it which is of course way more than what I need…
Would really appreciate your help on this one.
Hello Christian,
I shortened the comment a bit to keep only the relevant part.
If you’re using index you always start with 0 – since there’s just 1 item in the RoleDefinitionBindings it should be …?[‘RoleDefinitionBindings’][0]. Also, if you check the JSON the property names don’t contain { nor }, it’s just ?[‘RoleDefinitionBindings’][0]?[‘Id’], ?[‘RoleDefinitionBindings’][0]?[‘Name’]
Hi Tom,
I am trying to access an object with a dynamic property.
I was able to access the object with,
outputs(‘Mapping’)?[‘body/value’][0]?[‘Red’]
now i would like to use a variable
variables(‘Color’) // this returns Blue
so combining doesn’t even work…
outputs(‘Mapping’)?[‘body/value’][0]?[variables(‘Color’)]
Do you know how to use a variable instead of the static property ?
Thanks!
Hello Jason,
it’s used exactly as you described, that should work.
This is literally the best explanation I have seen. I very much value your content, blog, and emails!
Hello,
This is fantastic! I think I’ve tried using compose and initialize variable but in both cases I’m getting the same error. I want to get the name of the MS form the responses come from. I can see it in the Parse JSON output near the top before the question arrays (kinda like where you said to skip over the headers in your first example).
So I came up with this:
outputs(‘Parse_JSON_Questions’)?[‘body’]?[‘formsProRTTitle’]?[‘value’]
And I’m getting this error:
Unable to process template language expressions in action ‘Initialize_variable’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘outputs(‘Parse_JSON_Questions’)?[‘body’]?[‘formsProRTTitle’]?[‘value’]’ cannot be evaluated because property ‘value’ cannot be selected. Property selection is not supported on values of type ‘String’. Please see https://aka.ms/logicexpressions for usage details.’.
Hello Carla,
it’s telling you that ‘formsProRTTitle’ is a string field, there’s no ‘value’ to select, end the expression with ..[‘formsProRTTitle’]
thank you so much for this article.
better than the actual power automate docs.
now i can use the ID to create batches to deal with a very large file set… i think!
Appreciate you. Thank you again.
This is a great article to say the least.
However how do I extract all the instances of some tag in ‘values’, not just the first one? Practical example of such situation is output from Power Automate “Get files(properties only)” flow.