find top three values in json array

[nikviz]

Hello,

I am trying to build a interactive chatbot using PVA, where user interaction is fed to AI models and the model response is sent back. I get the model response as below:

sample json:

[

  {

    "label": "Car",

    "score": 0.23

  },

  {

    "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

  }

]

I need to get the top three label based on the score.. for this example: Truck, Boat, Train should be returned, that needs to be fed back to PVA.
This is the flow, I've made, and stuck on how to get the top three. Any inputs or links would help. Thanks

[Expiscornovus]

Hi @nikviz,

Which endpoint are you using in your HTTP action? Maybe it would be possible to add $sort query parameter in the specific HTTP request if that endpoint supports it. 

Can you share a screenshot of your HTTP action config?

[nikviz]

Hello @Expiscornovus 

[Expiscornovus]

Hi @nikviz,

Thanks for sharing that screenshot. I have had a quick look in the Inference API documentation. I did not find any $sort or $top parameters so far.

I assume you are using one of the Natural Language Processing tasks listed in the documentation below (Named Entity Recognition (NER) task by any chance?)

https://huggingface.co/docs/api-inference/detailed_parameters

Let me see if I can share a workaround to sort on score descending and get top 3 afterwards.

[nikviz]

Hello @Expiscornovus 

Thanks a lot for looking into this. I am using a custom Natural Language model to classify certain Categories. so from the link https://huggingface.co/docs/api-inference/detailed_parameters#text-classification-task this seems to be the closest.

Is it possible to get the top score and the corresponding label from that json format. I can use this as a temporary solution. Like the value returned from the JSON should be "Truck" as it has the highest score.

I tried this tutorial (https://www.youtube.com/watch?v=weNI78Z1FBo) to get the highest "score", but not sure how the get the associated label. 

Thanks a lot for your help
 

[Expiscornovus]

Hi @nikviz,

You can probably use an Office Script for this. @Paulie78 has written a nice blog about this approach:

https://www.tachytelic.net/2021/03/power-automate-sort-array/

Another approach could be to turn your response into xml and use xpath. But that does require some xpath knowledge and can be a bit more complicated.

[Paulie78]

I have figured out a way of doing this (with xpath) natively in Power Automate without using an Office Script. It's quite an interesting problem. So I am going to write it up into a blog post next week and will post the link here.

[nikviz]

Thank you so much @Expiscornovus and @Paulie78 
Will surely try to implement the xpath method once the blog is out as I do not have access to use Office Script.

As a temporary workaround to fetch the corresponding label associated with highest "score"  @abm helped me out.

[nikviz]

I forgot to update this.. as a temporary workaround
I managed to solve it by:

1) Finding the maximum value from array1 -> lets call it max1

2) Filter out max1 from array and call it array2

3) Find the maximum value from array2 -> call it max2

4) Filter out max2 from array2 and call it array3

5) Find the maximum value from array3 -> call it max3

max1, max2 and max3 are the top three values

[Paulie78]

I will try to post my solution tonight, It's pretty neat actually.

[eliotcole · Accepted solution]

The one thing that I can't see mentioned, here, @nikviz, is whether or not there is a catering for the fact that there's the remotest possibility that two scores may be the same, and those two scores may be in that top three ... potentially making it a top 4/5/6/etc. 😉

Obviously I've not just come to troll, though.

I'm sure that @Paulie78's xpath() awesomeness will easily overshadow what I've done here, and will take far fewer steps.

Either way, this will provide the top three values, even if those may be shared, and then returns a new array of just the top items.

Here's the sample data that I used, I simply made two items (boat+train) have the same, second highest, value:

Spoiler

[
	{
		"label": "Car",
		"score": 0.23
	},
	{
		"label": "Bus",
		"score": 0.26
	},
	{
		"label": "Train",
		"score": 0.45
	},
	{
		"label": "Boat",
		"score": 0.45
	},
	{
		"label": "Truck",
		"score": 0.62
	},
	{
		"label": "Bike",
		"score": 0.11
	}
]

Doing The 'Long' Way ... Without xPath()

OK, the only real complicated bit in this is that I decided to give you a 'position' to indicate which value is in which position. This is totally optional, and I'll ensure that you know what to do to carry on without that.

Here's the flow!

I'll now go through each action, where you see a code box that will be an expression unless otherwise noted.

Select scores

OK, this is the only thing where the image is a bit off, because I left an old expression in the note there, ignore that.

From currentArrVAR Map item()?['score']

Initialising Variables

This should be simple, each of these is either empty or easily understood.

Name	Type	Value	Description
topArrVAR	Array		This will hold the top valued items
maxesVAR	Array	Select scores	This makes it easier to know purely an array of the values
iterationVAR	Integer	0	This contains the current loop number the Do until is on
maxVAR	Float		This contains the current maximum value in the maxesVAR

Do until

Here's the sauce, this will run until the iterationVAR is equal to 2, which means that it will have run 3 times as the first iteration is 0.

Hopefully it's relatively obvious that you simply choose the Dynamic value of iterationVar in the left side, select the condition 'is equal to', then put a number 2 in the right side of this.

Set maxVAR

This is the first real key part, this looks at the current values in the maxesVAR array, and selects the highest one to set as its value:

max(variables('maxesVAR'))

Set iterationVAR

This replaces whatever number is already in iterationVAR with the current iteration of the Do until loop.

iterationIndexes('Do_until')

Next you need to filter a few things, so these will have their conditions represented here in their advanced mode, but you can probably see how to set them.

Filter is branch

The Filter is action filters the currentArrVAR variable by the maxVAR value, returning all items where item()?['score'] matches that value.

From currentArrVAR Condition @equals(item()?['score'], variables('maxVAR'))

Now that you have the list of items which match the highest value you can add those to the topArrVAR.

The Body in that Apply to each here what happens when you select the Filter is Dynamic value:

addProperty( items('Apply_to_each'), 'position', add(variables('iterationVAR'), 1) )

The addProperty() function here is the optional part that I mentioned earlier. If you prefer to just have the original fields, simply use:

items('Apply_to_each')

Filter remainingMaxes branch

This filters the maxesVAR array to return every item() which is NOT the maximum value:

From maxesVAR Condition @not(equals(item(), variables('maxVAR')))

Now that you have filtered the maxesVar to everything EXCEPT the maximum value (maxVAR) you can set the maxesVAR to a new set of values which don't contain it ... this allows the next loop to pick the next highest value!

body('Filter_remainingMaxes')

Filter nots branch

This filters the currentArrVAR down to every item()?['score'] that DOES NOT match the maximum number (maxVAR).

From currentArrVAR Condition @not(equals(item()?['score'], variables('maxVAR')))

Now that you have an array which does not contain the current top values, you can Set currentArrVAR to contain that array, instead of its old set of data, meaning that the previous top value is gone:

body('Filter_nots')

That's it ... now your topArrVAR will hold the top three valued items, even if that is more than 3!

HURRAH!!

Any Potential Issues?

Well, the only one I can think of is potential 'concurrency' issues inside the 'Do until', but I don't think it will be an issue.

But if you wanted to be super sure, then you would run the 'Filter nots' branch after the other two, which should be fine next to each other.

[Paulie78 · Accepted solution]

This is my take on it:

https://www.tachytelic.net/2022/07/power-automate-sort-json-array/

It was a fun problem to solve, thank you for posting it 😀

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘