Nested JSON
This is how to extract data into an array from a JSON that contains nested components. This is typically something you may need to do when formatting the response received from an API call.
JSON is a data interchange format that can represent a wide variety of structured data, including objects, arrays, and various data types such as strings, numbers, booleans, and null.
The API call would typically be made using the HTTP action and the JSON output parsed with the Parse JSON action
The output from the Parse action is also is a JSON, but in a simpler format that is easier to work with. Even after parsing, the JSON could retain nested structures that still need to be navigated to extract the required data.
This is the case for the file in my example shown below. As you can see, after parsing, the API call has returned a nested structure containing 3 assets.
{
"body": {
"asset": [
{
"type_fields": {
"product": "Surface Pro",
"vendor": "Microsoft",
},
"asset_id": 13000264224,
"asset_tag": "6975",
"sources": [
"User"
],
"location_id": null,
"department_id": "HR",
"user_id": 501342,
},
"type_fields": {
"product": "34 Inch Monitor",
"vendor": "Dell",
},
"asset_id": 13000284299,
"asset_tag": "6976",
"sources": [
"User"
],
"location_id": null,
"department_id": "HR",
"user_id": 501342,
},
"type_fields": {
"product": "Bluetooth Headset",
"vendor": "Sony",
},
"asset_id": 13000324544,
"asset_tag": "6977",
"sources": [
"User"
],
"location_id": null,
"department_id": "HR",
"user_id": 501342,
},
]
}
}Create a Flat Array
My objective is to create an array from this JSON, so I can display the Product, Vendor, Asset Tag and User ID key/value pairs for each asset in a HTML table.
It’s tempting to think you need to use an ‘Apply to each’ action to scroll through each asset. Indeed, it can be done this way but there is a better and quicker method.
I recommend analysing the JSON structure and then directly typing the required expression into the Select action using the expression tab in the dynamic content dialogue box. This is my preferred approach because with a complex JSON, it is often not possible to select directly from within the dynamic content tab.
In my example, for the From value in the Select action, type in the expression below, as we are wanting to capture information from the asset array in the JSON.
body('Parse_JSON')?[‘body’]?['asset'] In the Map section, type the following values to reflect the format of the JSON, noting that 3 of the 4 are further nested within the asset array:
Product item()?['type_fields']?['product']
Vendor item()?['type_fields']?['vendor']
Asset Tag item()?['asset_tag']
User item()?['user']?['user_id']
On running the flow, the returned array is shown below
[
{
"Product": "Surface Pro”,
"Vendor": "Microsoft",
"Asset Tag": "6975",
"User_ID": 501342,
},
{
"Product": "34 Inch Monitor”,
"Vendor": "Dell",
"Asset Tag": "6976",
"User_ID": 501342,
},
{
"Product": "Bluetooth Headset”,
"Vendor": "Sony",
"Asset Tag": "6977",
"User_ID": 501342,
}
]
Now we have a flat array, it is straightforward for this to be formatted into a HTML table using the ‘Create HTML table’ action.
To get the format of the table looking really good, I recommend reading Matthew Devaney’s blog post Foolproof Power Automate HTML Table Styling.
Working with JSON files can be frustrating and it is worthwhile spending time to get your head around how to access nested data. For further info, please see my recent blog post Navigating a JSON Structure