Query Values From JSON Outputs In PowerAutomate

Vinay Ayinapurapu
Jan 10, 2022

45.5k
0
4
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

QueryJsonDemo_20220109214724.zip

Introduction

In this article, we will go through couple of ways to get the required values from the JSON output values that we got from Power Automate. All the outputs that we get from the connectors like 'Get items from SharePoint', 'Get file properties', 'send HTTP action to SharePoint' etc, are in JSON format.

Using 'select' (?) operator

You can query the output values from the JSON outputs. This method is very useful if you want to get 1 or 2 values from the JSON outputs. To demonstrate the usage, I am using ‘send HTTP request action’ and then query the values.

Below are the steps to generate a simple flow. You can always refer to the attached template to download and configure.

Step 1

Go to the flow URL https://flow.microsoft.com and then start instant cloud flow.

Step 2

Initialize the string variable with a value. Here I have hard-coded the URL for simplicity. here I have given the relative URL of the document. Below is the value I have given. Make sure to change the value according to your configuration.

/teams/InspectionData/Shared%20Documents/MerlinHubDoc5.pdf

Step 3

Send the HTTP request to SharePoint. Below is the screen capture. Here I am using GetFileByServerRelativeUrl method.

Below is the screen capture of flow that has only 2 actions and 1 trigger.

Step 3

Test the flow and observe the output. On successful configuration, you will observe the below output.

Step 4

Now query the values. Most of the times the values that we need to query are under the body. Let’s use compose action and query the document GUID. If you observe the JSON you can see the GUID is under body -> GUID

To get the value we will use compose here and configure the action

Here you will use the ‘fx’ expressions and then select the ‘body’ from the dynamic value and use the ‘?’ [select operator] to query the value. Since the GUID is under the body -> GUID the expression will look like below.

body('Send_an_HTTP_request_to_SharePoint')?['d']?['GUID']

Step 5

Similarly, if we want to get etag value from JSON, you can use the compose action and get the body value and then query the respective branches.

The expression would result like below

body('Send_an_HTTP_request_to_SharePoint')?['d']?['__metadata']?['etag']

The etag is under body à dà __metadataàetag

Using ‘parse JSON’ action

Instead of select operator, if you want to get multiple values from JSON like more than 3 or 4 then we can achieve it using ‘Parse JSON’ which is a standard connector.

Step 1

Configure parse JSON accordingly.

The value of content will be the ‘body’ value from ‘Send an HTTP request to SharePoint.

Step 2

Now configure the schema correctly. In most cases this value is configured incorrectly which would result in null values. Click on ‘Generate from sample’ and then you will be getting the below pop-up.

The JSON payload should be in the following format. The JSON file format should always have open and closed parenthesis / square brackets.

{
    JSON body
}

To get the sample payload, you need to at least run the flow once with your parse JSON and capture the outputs from the ‘Send an HTTP request to SharePoint’ and save it in notepad or VS code. I would recommend VS code, as it is little easier to understand. Below is the output from the VS code.

Query values from JSON outputs in PowerAutomate

Since we are only focusing on the body, below is the screenshot of the body value and its parameters.

Query values from JSON outputs in PowerAutomate

In the sample payload you will paste the payload value starting from open parenthesis after the ‘body’ till the end of the closed parenthesis of body. Below is the screen capture for reference.

Query values from JSON outputs in PowerAutomate

Step 3

Once this is given you should observe that ‘Parse JSON’ has schema generated automatically.

Step 4

Now you can use ‘Compose’ action again, to check the values that are getting generated from ‘Parse JSON Action’.

Step 5

I have used ‘SharePoint create item’ based out of parse JSON value from above action. Below is the screen capture of the configuration. Here I have chosen etag.