Extract single value from on prem sql query result object

I usually prefer to use the Query JSON action, as it has a lot of handy filtering capabilities if you know the syntax for it. I just wish that NWC knew that if you're using a collection as a source for the Query JSON, to convert the collection into JSON format automatically (as the Get Item From Collection item apparently does...). 

 

In addition to @SimonMuntz suggestion, the following 2 actions would probably work for you:

1. Apply a regular expression to remove string formatting, hopefully converting the entire collection into a JSON format. This would be useful if the Get Item From Collection action wasn't usable (maybe because you don't know which item has the property you need?).  Expression used here is ("(?={)|(?<=})"|\(?="))

Basically, it's replace any quotes followed by a {, quotes preceded by a }, or slashes followed by a quote.

 

2. Query JSON now that it's in JSON format, just pull the intended value out of the string.

 

You could alternatively do the whole thing with a single Regular Expression to extract the single piece of information you need.  This would take the least amount of actions (1) but would be specific to what you're trying to pull, and not as reusable as the Regular Expression above that would convert it to a JSON. 

Yep. But like I mentioned, there are cases where that action isn't what you want because you don't know what item in the collection you need. If the SQL statement is only returning one result than the Get Item From Collection action would work. If there's multiple results, then doing my regular expression should get the entire collection into JSON format, not just a single item. Than the Query JSON action could be used with some expression filtering to get the necessary item out.

Not necessarily. You may know what you need, but not know what index in the collection that item is in. It all depends on what your collection is comprised of, what you need out of it, and what rules you use to filter the collection to what you need.

 

That may not be the case here, but I've hit plenty of situations where if I tried to use a For Each loop or Get Item From Collection action to try to get the targeted value, it would have been a very complicated process.  Example being a collection containing multiple layers of nested collections. If I need a specific top level collection but have to use a value found 3 nested collections down to identify that specific collection, I've yet to find a better solution than what I proposed. That being said, I would love to have a better solution.