cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nikviz
Frequent Visitor

find top three values in json array

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
nikviz_0-1654598015675.png

 

 
 
2 ACCEPTED SOLUTIONS

Accepted Solutions

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!

0 - The Flow.jpg

 

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.

1 - Select Scores.jpg
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.

2 - Do until.jpg

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:

3 - Set maxVAR.jpg

max(variables('maxesVAR'))

 

Set iterationVAR

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

4 - Set iterationVAR.jpg

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.

05 - Filter is.jpg
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:

06 - Apply to each.jpg

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:

07 - Filter remainingMaxesVAR.jpg
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!

08 - Set maxesVAR.jpg

body('Filter_remainingMaxes')

 

Filter nots branch

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

09 - Filter notsVAR.jpg
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:

10 - Set currentArrVAR.jpg

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.

View solution in original post

Paulie78
Super User
Super User

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 😘

View solution in original post

11 REPLIES 11
Expiscornovus
Super User
Super User

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
Frequent Visitor

Hello @Expiscornovus 

 

nikviz_1-1654599319410.png

 

Expiscornovus
Super User
Super User

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
Frequent Visitor

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
Super User
Super User

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.

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
Frequent Visitor

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
Frequent Visitor

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
Super User
Super User

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

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!

0 - The Flow.jpg

 

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.

1 - Select Scores.jpg
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.

2 - Do until.jpg

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:

3 - Set maxVAR.jpg

max(variables('maxesVAR'))

 

Set iterationVAR

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

4 - Set iterationVAR.jpg

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.

05 - Filter is.jpg
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:

06 - Apply to each.jpg

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:

07 - Filter remainingMaxesVAR.jpg
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!

08 - Set maxesVAR.jpg

body('Filter_remainingMaxes')

 

Filter nots branch

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

09 - Filter notsVAR.jpg
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:

10 - Set currentArrVAR.jpg

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
Super User
Super User

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 😘

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,188)