Extract single value from on prem sql query result object

  • 30 September 2022
  • 7 replies

Badge +1

I am querying an on-prem SQL DB via a gateway. How do I extract a specific value from the results? For example, what actions are needed to extract  only FundAssetStrategyDescription from this object? The result should be "Risk Averse Mix (short term)"



Best answer by SimonMuntz 3 October 2022, 05:31

View original

7 replies

Userlevel 6
Badge +22



The action returns an object.
The easiest way to process an object in Nintex Automation Cloud is to use a for each loop.

This video is for the Azure SQL action but the concept of handling the returned value is exactly the same.

The second option is to use a get item from collection action to extract the results. This puts them in a string which is now in JSON format so you can use a Query JSON action to extract the bits you want.  Like I said.  Using the For Each is much simpler. 

Userlevel 4
Badge +10

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. 

Userlevel 6
Badge +22

@bsikes If you use a get item from collection action first you do not need to use a regular expression as the output is a JSON string.


Still, Using a Loop for Each action is still the easiest way to work with objects.  No need to know Regex or JSON.

Userlevel 4
Badge +10
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.
Userlevel 6
Badge +22
" 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 you don't know what item you need then it wont matter what format its in as you will still not know what you need.
Userlevel 4
Badge +10

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. 

Userlevel 4
Badge +12

I know not quite what the original poster was after but it would be great to add support for 'First Item' into the SQL on-premises object.


I've created an idea on the new Nintex ideas portal if you would like to up-vote it.

Add First Item to SQL On-premises actions and object | Nintex Ideas


(@SimonMuntz - if you could share the idea with Product Manager please?)