The exciting conclusion to my attempt to bludgeon a JSON REST API response to death with SQL Server 2012 – a breakthrough in the functionality of Open Web Studio to the use of the Query action to break into JSON for row(s) of values.

The response from the Avatax API that I am trying to chew up is similar to the one below, an example of the data that I need is given on lines 331-346.  Previously, I smacked at it with SQL Server , because hey – I love SQL.

Fortunately, I’m working in DotNetNuke through the module Open Web Studio and can leverage the QUERY action. This documentation from about TEN years ago shines a tiny bit of light on the subject, but truth be told it was the example configuration that opened my eyes.

I send the request to the Avatax API via the INPUT action.  I used the authorization token generated by Postman during testing (from the username and password) and the details/lines that I’m sending were stored in [taxreq,A].  The response is being dropped into [taxresp,A], where I will pick it apart with a JSON query.

Since the “lines” of the response isn’t the natural root, I reframe the query with the tag.  For this API call I’m validating the tax percentages applied to each line – so my main interest is the “tax” element.

The JSON query returns a row for every element in the “lines” object.

Overall, this section of the OWS configuration looks like:

• Input – call to the Avatax API, returns JSON to [taxresp,A]
• **Query **– [checkTax] JSON query
  • Assignment – Action.testResponse = [tax,checkTax]   (for validating my query formatting and troubleshooting)
  • Query –  SQL query with the [tax,checkTax] and other variables

Now that last query is repeated for every line in the JSON response – which if you’re not careful is a LOT of calls from your IIS server to your SQL server.  I ended up grouping the lines by taxCode before the input to the Avatax API, then using the taxcode to update the lines in groups instead of by individual line.

Say you want more information from the JSON return that isn’t within the object that you queried with “checkTax,” like elements from the original root level.  You can query the variable [taxresp,A] again with another JSON query, it isn’t destroyed by the query.

Post-Publish Note:

Here is an OWS config that works with the above described functionality. The config can be imported to your OWS instance through the Import button on the Action tab.

https://gist.github.com/dzsquared/ab1190dd1928b794c777e5f48af2570a