Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

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

Posted on September 26, 2021September 26, 2021 by Tom

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

31 thoughts on “How to get a specific value from a JSON in Power Automate”

  1. Maciej says:
    October 5, 2021 at 5:40 pm

    Tom, you’ve done fantastic explantion of the getting value from JSON !

    Reply
  2. Shane says:
    November 9, 2021 at 7:00 pm

    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.

    Reply
    1. Tom says:
      November 11, 2021 at 3:41 pm

      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/).

      Reply
  3. Duane Lines says:
    November 14, 2021 at 3:11 am

    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?

    Reply
    1. Tom says:
      November 17, 2021 at 9:05 pm

      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.

      Reply
  4. dt says:
    February 10, 2022 at 4:59 pm

    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?

    Reply
    1. Tom says:
      February 13, 2022 at 6:33 pm

      Hello dt,
      I don’t have any idea how to do this, I can’t even say if it’s possible or not.

      Reply
  5. Stephan says:
    April 13, 2022 at 3:45 pm

    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’]

    Reply
    1. Tom says:
      April 20, 2022 at 5:46 pm

      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.

      Reply
  6. LCz says:
    May 4, 2022 at 3:53 pm

    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?

    Reply
    1. Tom says:
      May 11, 2022 at 6:02 pm

      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.

      Reply
  7. Ross says:
    May 4, 2022 at 7:21 pm

    Hey Tom,
    Thanks for this, is there way for powerautomate to get the “statusCode”?

    Thanks,

    Ross

    Reply
    1. Tom says:
      May 11, 2022 at 6:04 pm

      Hello Ross,
      if you want the ‘StatusCode’ then use it instead of the ‘Body’, e.g. outputs(‘Get_items’)?[‘StatusCode’]

      Reply
  8. Wes says:
    May 6, 2022 at 4:59 pm

    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

    Reply
    1. Tom says:
      May 15, 2022 at 5:23 pm

      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

      Reply
  9. stuart says:
    May 19, 2022 at 11:32 am

    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

    Reply
    1. Tom says:
      May 25, 2022 at 4:15 pm

      Hello Stuart,
      the column name must be exactly the same as in the JSON, including the curly brackets:
      body('checkforfile')?['value'][0]?['{Identifier}']

      Reply
  10. Vimal L says:
    June 19, 2022 at 8:08 am

    How can we parse json with specific fields output. For ex, only ID and Title as the output array.

    Reply
    1. Tom says:
      June 22, 2022 at 9:22 pm

      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.

      Reply
  11. Pingback: How to Understand Code in Power Automate » The Analytics Corner
  12. Ciprian says:
    July 15, 2022 at 10:39 am

    This is great!!! Now I understand how to reference objects and arrays in PA. Thank you very much dear sir! 🙂

    Reply
  13. Andrew M says:
    July 26, 2022 at 11:18 pm

    This was so helpful! Thanks so much. Knowing the JSON structure helps me with UI limitations.

    Reply
  14. Jared Waterman says:
    October 21, 2022 at 5:09 pm

    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]?

    Reply
    1. Tom says:
      October 31, 2022 at 6:33 pm

      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/

      Reply
  15. jared says:
    October 22, 2022 at 12:00 am

    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.

    Reply
    1. Tom says:
      October 31, 2022 at 6:40 pm

      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.

      Reply
  16. Joan Connelly says:
    January 29, 2023 at 8:31 pm

    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!

    Reply
  17. Yasholekha R says:
    February 1, 2023 at 2:52 pm

    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

    Reply
    1. Yasholekha R says:
      February 1, 2023 at 2:55 pm

      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

      Reply
      1. Tom says:
        February 9, 2023 at 9:52 pm

        Hello Yasholekha,
        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(,100),5)
        4. Check in condition whether A – B is more than the calculated 5%

        Reply
  18. MJD says:
    February 7, 2023 at 4:51 pm

    Thank you!! This is the most comprehensive article I’ve found about traversing JSON in Power Automate. Bookmarking this page, Nice Job!

    Reply

Leave a Reply Cancel reply

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

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

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.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes