Sort an object array by numeric values that are stored as string using Power Automate

Recently I got a question in a previous post about the sort expression in Power Automate:

I am attempting to use this expression in a compose box to sort by a numeric ascending value, but it is sorting that value as if it were a word and not a number. How would I amend this expression to sort the array by a number value that is apparently stored as a text string?

Let’s have a look at the issue. If we have an array of objects as the below one in a compose action to be simple, with a integer value as String (number property):

[
  {
    "firstName": "Sylvester",
    "lastName": "Mavros",
    "country": "Sudan",
    "number": "1"
  },
  {
    "firstName": "Reed",
    "lastName": "Dunne",
    "country": "Cape Verde",
    "number": "10"
  },
  {
    "firstName": "Gabriel",
    "lastName": "Balenzuela",
    "country": "Hong Kong",
    "number": "4"
  },
  {
    "firstName": "Kory",
    "lastName": "Melot",
    "country": "Azerbaijan",
    "number": "30"
  },
  {
    "firstName": "Donovan",
    "lastName": "Kristovich",
    "country": "Georgia",
    "number": "100"
  }
]

When calling the sort expression using the number field as below:

sort(outputs('Compose'),'number')

The output array will be sorted with the following numeric order:

“1”,”10″,”100″,”30″,”4″

Which is not what we want in this case.

The solution

The quickest solution for this is to convert the value from string to integer in the array before sorting.
For that we can use the select action.

The select action receives an array/list of objects as input, and returns the mapped selected columns with the values from expressions you can customise per item.

For the string fields, you could manually reference them directly in the expression pane, for example:

But to be more consistent and avoid error, we can transform the item via an expression. In this case we need to add an extra integer property to each item, that then will allow us to sort the array properly.

To do this, click the option ‘Switch map to text mode’ in the select action:

This allows you to enter a free expression as text:

Then use the following expression, which adds a new numberSort property to the item, with the string holding the number value converted to integer:

addProperty(item(),'numberSort',int(item()?['number']))

Results

When sorting the outputs of the select action as below:

sort(Body('Select'),'numberSort')

The data will be listed in the right sort order, having the additional field converting the string to integer:

[
  {
    "firstName": "Sylvester",
    "lastName": "Mavros",
    "country": "Sudan",
    "number": "1",
    "numberSort": 1
  },
  {
    "firstName": "Gabriel",
    "lastName": "Balenzuela",
    "country": "Hong Kong",
    "number": "4",
    "numberSort": 4
  },
  {
    "firstName": "Reed",
    "lastName": "Dunne",
    "country": "Cape Verde",
    "number": "10",
    "numberSort": 10
  },
  {
    "firstName": "Kory",
    "lastName": "Melot",
    "country": "Azerbaijan",
    "number": "30",
    "numberSort": 30
  },
  {
    "firstName": "Donovan",
    "lastName": "Kristovich",
    "country": "Georgia",
    "number": "100",
    "numberSort": 100
  }
]

By using the ‘Select‘ action in combination with the sort function it’s possible to sort an array of objects by a numeric property that is stored as String in Power Automate.

The post Sort an object array by numeric values that are stored as string using Power Automate appeared first on michelcarlo.

Original Post https://michelcarlo.com/2023/08/07/sort-an-object-array-using-number-values-that-are-stored-as-string-in-power-automate/