Table of Contents
There are some scenarios where JSON results returned from a service will have different column names or attributes. For example this JSON sample has 6 columns – Amount, Description, Product Code, Quantity and Unit Price:
[
{
"Amount":{
"type":"currency",
"content":"$65.00"
},
"Description":{
"type":"string",
"valueString":"SiteReadyB",
"content":"SiteReadyBC"
},
"ProductCode":{
"type":"string",
"valueString":"SiteReadyBC",
"content":"SiteReadyBC"
},
"Quantity":{
"type":"number",
"valueNumber":1,
"content":"1"
},
"UnitPrice":{
"type":"currency",
"valueCurrency":{
"currencySymbol":"$",
"amount":65,
"currencyCode":"CAD"
}
}
}
]
But sometimes, more columns – for which we don’t know the name of – could present in the results or not all columns will be available. This blog will show the steps required in Power Automate to extract all the column names present in a JSON result.
Step 1. Create an Array variable
Step 2. Use xpath and xml functions
Our JSON is for now being stored in an action called “Select” in Power Automate Platform
Add a compose action, and use the following formula:
@{xpath(xml(json(concat('{ "root": { "columns": ', first(body('Select')), ' } }'))), '/root/columns/*')
What this formula is doing is getting the first line of the JSON results and concatenating the attributes “root” and “columns” to it. Behind the scenes it should look something like this:
{ "root": { "columns": ', {your-json-record}, ' }
Then, it converts this into XML and finally it extracts the root/columns attributes from it.
Step 3. Obtain column names
Add an “Apply to each” action and map the input to the results of the compose action. Inside the loop, add an “append to array variable” action.
Inside the “append” action, add the following code:
replace(first(split(string(items('For_each_column_in_details')),'>')),'<','')
What this will do is extract the column name from the XML format:
We’re obtaining the text before the first “>” character and then just removing the first “<”.
Step 4. Print the results
Add a Compose action and assign to it the value of the array that contains the column names.
After the “For each column in details” loop finished the results should look like this:
Conclusion
Power Automate has amazing functions that can be used to work many formats of JSON results, combining Arrays, Loops and Compose actions along with XML functions is the perfect mix to extract dynamic column names. Contact us if you have any questions or if you’d like some help on Power Automate or PowerFx!
