Answered

Get JSON key based on value

(0) Share

Report

Posted on by 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?

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Nived_Nambiar 18,136 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

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! 🙌

Was this reply helpful? Yes No
VictorIvanidze 13,079 on at

Like (1)

Report
Copy link

Link copied!

Try this approach:

Was this reply helpful? Yes No
Verified answer

Chriddle 8,523 Super User 2026 Season 1 on at

Like (4)

Report
Copy link

Link copied!
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_', ' ' )

Was this reply helpful? Yes No
tjlyons65 14 on at

Like (0)

Report
Copy link

Link copied!

Wow, thank you all!

Was this reply helpful? Yes No
tjlyons65 14 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Chriddle 8,523 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
tjlyons65 14 on at

Like (0)

Report
Copy link

Link copied!

Thank you!

Was this reply helpful? Yes No