Working with large or complex JSONs can be intimidating, The key is to understand the JSON structure you are working with. If you’re struggling trying to extract data from a JSON, then this post is for you!
JSON Structure
The Power Automate HTTP action is used to query REST APIs that return their response in JSON format. Below is a JSON structure that contains information about three consultants. I’ll use this JSON in all my following examples as it is typical of what might be returned by an API
{
“body”: {
“consultant”: [
{
“active”: true,
“personal_id”: 1301,
“name”: {
“first_name”: “Percy”,
“last_name”: “Dube”
},
“skill”: [
“Power Apps”,
“Power BI”
]
},
{
“active”: true,
“personal_id”: 1302,
“name”: {
“first_name”: “Chris”,
“last_name”: “Harper”
},
“skill”: [
“Power Apps”,
“Power Automate”,
“Power Pages”
]
},
{
“active”: true,
“personal_id”: 1303,
“name”: {
“first_name”: “Barry”,
“last_name”: “Blue”
},
“skill”: [
“Power Apps”,
“Power Automate”,
“Power Pages”,
“Power BI”
]
}
]
}
}
Summarising the above JSON
There is a single ‘body’ object that encapsulates the whole JSON that has a multi-tier structure
Within the ‘body’ there are three ‘consultant’ objects. The ‘first_name’ of these objects are Percy, Chris and Barry
Each ‘consultant’ object contains an array (denoted by the square brackets) that in turn contains name/value pairs called ‘active’ which is a Boolean and ‘personal_id’ which is an integer
The ‘name’ object is further split into ‘first_name’ and ‘last_name’ name/value pairs, both of which are strings
There is also a string array called ‘skill’, and the number of skills can vary for each consultant
If you have a large or complex JSON to work with, it definately helps to make use of free online tools. I Iike to use https://jsonformatter.org/ or https://stoolset.com/json-formatter. There’s alot you can do with these tools, in particular, both allow you to view the JSON in a number of ways including the expanding tree view which is a big help to understand and get to grips with the overall JSON structure
Below is the same JSON in a tree view. It’s easier to understand this way
Extracting JSON Data with a Flow
I’ll go through some examples of how to get at the data contained in this JSON, but first, let’s replicate the JSON being returned from an API
To do this, copy and paste the JSON above into the input of a Power Automate Compose action. Run the flow and copy the output
Add a Parse JSON action, simply to replicate the approach you would take if you were receiving this JSON as a response from an API. Click on the ‘Generate from sample’ button and past the output just copied from the Compose action as the sample payload
Also add a second Compose action (Compose_1) which we’ll used to execute our queries
Now, let’s get at the data!
Target the first Consultant Object in the JSON
We know this JSON returns three consultant objects, but you may want to just target the first object. This is typically the case if you know that your API call will only return one object, for example, if you are sending a HTTP GET request based on a unique identifier
To target the first object of an array, you need to specify you are targeting the first object, even if the JSON contains only the one object
You can do this by either use the identifier [0] or the ‘first’ function
Get details of the first consultant object
In the Compose_1 action, add either of the expressions below as dynamic content:
body('Parse_JSON')?['body']?['consultant'][0]first(body('Parse_JSON')?['body']?['consultant']) The expression doesn’t need to be manually typed out, and in fact Power Automate can make it tricky to do so. It’s easier to select from the dynamic content as below
Selecting ‘Body consultant’ automatically enters: body(‘Parse_JSON’)?[‘body’]?[‘consultant’], which saves time and avoids typing errors
As expected, running the flow acts as a filter and removes every object that doesn’t have the identifier [0]. It has essentially reduced the large JSON we started with to a much smaller one that only contains data on the first consultant:
To drill into the JSON further and obtain the ‘personal_id’ of the first consultant, rather than specifying the full consultant object, instead specify the ‘personal_id’ name/value pair for that object
Use either of the following expresions in the Compose_1 action dynamic content. This time, because a name/value pair is selected rather than a full object, the integer 1301 is returned
body('Parse_JSON')?['body']?['consultant'][0]?['personal_id']
first(body('Parse_JSON')?['body']?['consultant'])?['personal_id'] And similarly, to drill down even further, to get the ‘first_name’ of the first consultant, run either of the expressions below, which both return the string ‘Percy’
body('Parse_JSON')?['body']?['consultant'][0]?['name']?['first_name']first(body('Parse_JSON')?['body']?['consultant'])?['name']?['first_name']Note that for each of the queries, we are taking exactly the same approach. We are starting at the top level [‘body’] and drilling down till we reach the required data
Target objects other than the first
The first object in an array is numbered 0, the second item is number 1, the third number 2 and so on
To get the ‘last_name’ of the second consultant, use the expression below in a Compose action to return the string ‘Harper’
body('Parse_JSON')?['body']?['consultant'][1]?['name']?['last_name']Objects within embedded arrays can also be targeted. The expression below returns the third skill of the second consultant, which is the string ‘Power Pages’
body('Parse_JSON')?['body']?['consultant'][1]?['skill'][2]Count the number of returned objects
If your HTTP request to an API isn’t based on a unique identifier, it’s likely that you won’t know how many objects have been returned in the JSON. If this is the case, you can use the length function to count the number of object
The expression below, counts the number of consultant, which returns 3
length(body('Parse_JSON')?['body']?['consultant'])We can drill into the JSON further. For example, to identify the number of skills for the first consultant, use the following expression, which returns 2
length(body('Parse_JSON')?['body']?['consultant'][0]?['skill'])Counting the number of objects returned is very useful because you know exactly how may objects you are working with. If you find that only one object has been returned, you can directly target it using the technique described earlier
Working with Multiple Objects
If the JSON contains multiple objects then you can capture them into an array using the Select function
You may want to filter them further, and if so, preceed the Select action with a Filter array action
Using the Filter Array action (I’ve flipped over to the Power Automate classic view below as it’s easier to display the content of both actions), I have first filtered the JSON for consultants who have ‘Power Apps’ as one of their skills
Then a Select action returns an array of the personal_id and last_name of the filtered consultants from the preceeding Filter Array action
Running the flow returns the array below
Note that you don’t need to keep the data in the same format as the initial JSON. It’s straightforward to flatten the structure when creating the new array with the Select action, which is exactly what I have done here with personal_id and last_name
Summary
Please don’t be intimidated by a complex JSON. Take a bit of time to understand the JSON structure and remember that all the queries follow the same logical format. Start at the top tier and drill down to locate the specific object or key/value pair that you require
If you have a large or complex JSON to work with, you should definately make use of free online tools such as . https://jsonformatter.org/ or https://stoolset.com/json-formatter and use the expanding tree view which is a big help to understand the full JSON structure