Get JSON key based on value

[tjlyons65]

Hello there!

We have an inspection form that populates an excel spreadsheet (can't use MS Forms because the functions we need don't exist in it).  Answers are either "Pass" or "Fail".  I can get the latest entry using last():

last(body('List_rows_present_in_a_table')?['value'])

 Which returns the entire row as an object in JSON:

The problem is, I need to extract only the  columns  that equal "Fail".  I've looked everywhere, and found lots of ways to pull the value based on the key, but not the other way around.  Do I have to use "Apply to Each", and if so, what would that look like?

[Nived_Nambiar]

Hi @tjlyons65 

There is no function which can help to get key value corresponding to value 

So as a workaround try this approach

Get the last row based on above flow (same as your expression)

last(outputs('List_rows_present_in_a_table')?['body/value'])

Now use filter array to filter the key value pairs whose value is Fail as shown below

Expression - 

split(replace(replace(string(outputs('Compose')),'{',''),'}',''),',')

Now once done with filtering, to extract the key values only - try this approach

Expression - 

replace(replace(split(item(),':')?[0],'"',''),'"','')

Now see the output

I have used the following excel table

Now see how select action result looks like

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[VictorIvanidze]

Try this approach:

[Chriddle · Accepted solution]

Another approach:

Compose

{
"Air filter": "Pass",
"Oil filters": "Pass",
"Body Damage": "Fail"
}

Select

From

xpath(
	xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
	concat('//Item/*[text()="Fail"]')
)

Map

replace(
	xpath(item(), 'name(/*)'),
	'_x0020_',
	' '
)

[tjlyons65]

Wow, thank you all!

[tjlyons65]

I don't understand it very well, but it works!  Thank you!

[Chriddle]

In the Select's From, the object is conveted to XML and the xpath function returns an array of xml nodes that have the txt node "Fail" (//Item/*[text()="Fail"]).

In the Map, the name of these items is determined (name(/*)) and '_x0020_' is replaced with a space.

This is necessary because spaces are not allowed in XML node names and therefore encoded.

If you encouter other special characters in your property names, check this post.

[tjlyons65]

Thank you!