This post explains how to sort an array of objects in Power Automate. This method only works for objects that contain a numeric value on which to sort.
I have done a previous post on How to Sort an Array in Power Automate using Office Scripts, which works very well, but Office Scripts are not available in all environments.
This example demonstrates how to sort an array of objects using only native Power Automate actions. The question was originally raised on the Power Automate community forums. The original poster actually wants to find only the top three values, which I will also cover.
Sorting in Power Automate
Since there is no method to sort an array, we have to build our own for this purpose, which is surprisingly difficult to do. Here is the sample JSON array which we are going to sort:
[
{
"label": "Car",
"score": 0.26
},
{
"label": "Bus",
"score": 0.26
},
{
"label": "Train",
"score": 0.34
},
{
"label": "Boat",
"score": 0.45
},
{
"label": "Truck",
"score": 0.62
},
{
"label": "Bike",
"score": 0.11
}
]
Notice that both Car, and Bus have the same score. The aim is to sort the array in either descending or ascending order to look like this:
[
{
"label": "Truck",
"score": 0.62
},
{
"label": "Boat",
"score": 0.45
},
{
"label": "Train",
"score": 0.34
},
{
"label": "Bus",
"score": 0.26
},
{
"label": "Car",
"score": 0.26
},
{
"label": "Bike",
"score": 0.11
}
]
Power Automate does have the max and min expressions which can help you find the largest and smallest entries of an array, but there is no way to determine the position of the element within the array.
The xpath expression however, can be used to find the position of an array element, so we can use that to created a sorted array.
Here is an example flow, which I will explain further below:
To implement this flow for yourself, you need to Initialize two variables. One called ArrayOfObjects which contains the array you want to sort and a second one called Sorted – which should be empty.
The flow works by first finding the position of the highest or lowest values from ArrayOfObjects and then:
- Inserting each value into the new array.
- Removing the found value from the original array.
You can copy the scope and paste the entire thing into your own flow by copying the code below:
{
"id": "4c808717-bb30-4633-94fa-2af031ee4cdc",
"brandColor": "#8C3900",
"connectionReferences": {},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
"isTrigger": false,
"operationName": "SortArray",
"operationDefinition": {
"type": "Scope",
"actions": {
"Settings": {
"type": "Compose",
"inputs": {
"SortOrder": "Dsc",
"Attribute": "score"
},
"runAfter": {},
"description": "Modify the settings below to change the sort order or the object attribute to sort on",
"metadata": {
"operationMetadataId": "5e788764-ea85-4254-b18d-6fe0e9e4561a"
}
},
"Apply_to_each": {
"type": "Foreach",
"foreach": "@range(0,length(variables('ArrayOfObjects')))",
"actions": {
"RemoveFoundElement": {
"type": "Compose",
"inputs": "@union\r\n(\r\n take(variables('ArrayOfObjects'),outputs('ElementPosition')),\r\n skip(variables('ArrayOfObjects'),add(outputs('ElementPosition'),1))\r\n)",
"runAfter": {
"Append_to_Sorted_Array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "6f13a269-e383-48bb-9899-7b3fb1243e80"
}
},
"Update_Array_Of_Objects_With_Removed_Element": {
"type": "SetVariable",
"inputs": {
"name": "ArrayOfObjects",
"value": "@outputs('RemoveFoundElement')"
},
"runAfter": {
"RemoveFoundElement": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "e50136bb-7b12-42db-acc4-70338d823317"
}
},
"ArrayOfValues": {
"type": "Select",
"inputs": {
"from": "@variables('ArrayOfObjects')",
"select": "@item()[outputs('Settings')['Attribute']]"
},
"runAfter": {},
"description": "item()[outputs('Settings')['Attribute']]",
"metadata": {
"operationMetadataId": "ad3f6b94-8dc5-489b-9210-38e3671c8255"
}
},
"Prepare": {
"type": "Compose",
"inputs": {
"Root": {
"Values": "@variables('ArrayOfObjects')"
}
},
"runAfter": {
"ArrayOfValues": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "ebf6f3bb-6669-4792-be20-de49ce49f573"
}
},
"XML": {
"type": "Compose",
"inputs": "@xml(outputs('Prepare'))",
"runAfter": {
"Prepare": [
"Succeeded"
]
},
"description": "xml(outputs('Prepare'))",
"metadata": {
"operationMetadataId": "c8af3e8c-24b8-4b8e-b76c-893389262fcc"
}
},
"Append_to_Sorted_Array": {
"type": "AppendToArrayVariable",
"inputs": {
"name": "Sorted",
"value": "@variables('ArrayOfObjects')[outputs('ElementPosition')]"
},
"runAfter": {
"ElementPosition": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "48c26b4c-094e-4928-9fde-4c6c6b700cb3"
}
},
"ElementPosition": {
"type": "Compose",
"inputs": "@if\r\n(\r\n equals(outputs('Settings')['SortOrder'], 'Asc'),\r\n int(xpath(outputs('XML'), concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', min(body('ArrayOfValues')),']/preceding-sibling::*)'))), \r\n int(xpath(outputs('XML'), concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', max(body('ArrayOfValues')),']/preceding-sibling::*)'))) \r\n)",
"runAfter": {
"xpath_expression": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "4c6aa711-ae8f-44f5-b460-1baa6921dae1"
}
},
"xpath_expression": {
"type": "Compose",
"inputs": "@if(\r\n equals(outputs('Settings')['SortOrder'], 'Asc'),\r\n concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', min(body('ArrayOfValues')),']/preceding-sibling::*)'), \r\n concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', max(body('ArrayOfValues')),']/preceding-sibling::*)') \r\n)",
"runAfter": {
"XML": [
"Succeeded"
]
}
}
},
"runAfter": {
"Settings": [
"Succeeded"
]
},
"description": "range(0,length(variables('ArrayOfObjects')))",
"metadata": {
"operationMetadataId": "f5e1ebe2-13e2-4c60-a619-7e74808a028c"
}
}
},
"runAfter": {
"Initialize_Sorted": [
"Succeeded"
]
}
}
}
Once you have copied the code above you can simply paste it into your own flow by adding a new action, going to My clipboard and then press CTRL-V and you will see the scope appear:
Detail of how the flow works
For each iteration of the loop, the variable ArrayOfObjects is converted to XML. The example data produces this XML on the first iteration of the loop:
<Root> <Values> <label>Car</label> <score>0.26</score> </Values> <Values> <label>Bus</label> <score>0.26</score> </Values> <Values> <label>Train</label> <score>0.34</score> </Values> <Values> <label>Boat</label> <score>0.45</score> </Values> <Values> <label>Truck</label> <score>0.62</score> </Values> <Values> <label>Bike</label> <score>0.11</score> </Values> </Root>
During this first run it finds the highest value in the score attribute of the array. It does this using a dynamic xpath expression. Inside the scope there is compose action called xpath expression, the value of which is never used again, but I left it in place so that you can see what the flow is doing. In the first iteration the xpath produced is:
count(//Root/Values[score= 0.62]/preceding-sibling::*)
If you copy the XML above into xpather.com and then copy the expression to the expression box you will see that it returns a value of 4. The number 4 in this case represents the count of array elements before the target value was found.
The array position is then used for two purposes:
- To reconstruct the original array so that contains all of the elements except the one which was found.
- To add the found element to a new array.
In the next iteration of the loop, the found value is no longer present in the original array. This process keeps going until there is only one element left in the array.
Finding the top three values
Although I have explained how to sort an array. The original posters question was just to find the top three values. There are two ways this flow can be modified to achieve this:
Modify the apply to each loop
The source for the apply to each loop uses the following expression:
range(0,length(variables('ArrayOfObjects')))
With our sample data, this will produce the following JSON array:
[ 0, 1, 2, 3, 4, 5 ]
To get only the top 3 from the original array, the expression for the apply to each loop could be changed to:
range(1,3)
This will produce the fastest result as the apply to each loop will run only as many times as required to produce the Top 3 entries.
Using the take expression
The other alternative is to leave the scope to sort the entire array and when it is finished use the take expression within a compose action to extract the first 3 entries:
take(variables('Sorted'), 3)
This is the best method to use if you want an entire sorted array as opposed to a Top X.
Conclusion
This is a fairly advanced flow but it is easy to implement by copying the scope code. It would nice if there was a native sort action, if you feel the same, please add your vote to the idea on the forum.
Craig says
Thank you for this excellent post! It was exactly the solution I needed for a hierarchical approval scenario.
Ben says
I believe as of this writing, there is now an expression built-in to do array sorting. If not, it’s just about to be released.
kp says
Hi!
I would like to sort an array by email addresses, what would I need to do to have this sort on email rather than the score like yours is?