Thanks to Phil Factor, there’s a wonderful implementation that handles parsing JSON data even if you’re stuck using SQL 2008/2012/2014. dbo.parseJSON

Phil’s work with JSON has saved my tuckus a couple of times, with my affinity for consuming APIs, especially well-written ones with JSON support.

Today, I’m working with Avalara’s Tax API  and pulling tax rates and rules for a project based on the location, customer, and items being sold.  These tax rates are being displayed on a page in DotNetNuke – so I’m using OpenWebStudio to make the REST API call and output the results.  As a fail-safe for the API, the tax rates will also be written to a database table for use at a later date if the API is unavailable.

At first, I was shamefully working with their v1 API – a violation of one of my main tenants of working with APIs (not using the newest version).  Fortunately, when I tossed the response at parseJSON in SQL it was unable to parse it.

After I reviewed my options and selected the v2 API for creating new transactions , which returns an array of the lines with their applicable sales tax.  The kind developers at Avalara share a Postman collection for their APIs, which makes getting started with their services quick and I was able to switch gears from v1 to v2.

The response for a quick tax call of 4 lines: taxresponse.json

A bit of brute force work with CTEs and PIVOT yielded an unwieldy query that converted the JSON response into 4 lines with their tax rates and tax codes.

[sql]DECLARE @JSONVAL NVARCHAR(MAX) = ‘PUT THE JSON HERE AND DON”T REMOVE THE SEMI COLON AT THE END OF THE LINE’;

WITH PLINES AS (
SELECT * FROM
(
SELECT PARENT_ID, NAME, STRINGVALUE FROM dbo.PARSEJSON(@JSONVAL)

WHERE PARENT_ID IN (SELECT ARRAY.OBJECT_ID FROM dbo.PARSEJSON(@JSONVAL) LINES
LEFT JOIN dbo.PARSEJSON(@JSONVAL) ARRAY ON LINES.OBJECT_ID = ARRAY.PARENT_ID WHERE LINES.name = ‘lines’)
AND NAME IN (‘lineNumber’,’tax’,’taxCode’)
) AS JSONTBL
PIVOT
(
MAX(stringvalue)
FOR NAME IN ([lineNumber])
) AS PIVOTTEDLINES
),
PTAX AS
(
SELECT * FROM
(
SELECT PARENT_ID, NAME, STRINGVALUE FROM dbo.PARSEJSON(@JSONVAL)

WHERE PARENT_ID IN (SELECT ARRAY.OBJECT_ID FROM dbo.PARSEJSON(@JSONVAL) LINES
LEFT JOIN dbo.PARSEJSON(@JSONVAL) ARRAY ON LINES.OBJECT_ID = ARRAY.PARENT_ID WHERE LINES.name = ‘lines’)
AND NAME IN (‘lineNumber’,’tax’,’taxCode’)
) AS JSONTBL
PIVOT
(
MAX(stringvalue)
FOR NAME IN ([tax])
) AS PIVOTTEDLINES
),
PTAXCODE AS
(
SELECT * FROM
(
SELECT PARENT_ID, NAME, STRINGVALUE FROM dbo.PARSEJSON(@JSONVAL)

WHERE PARENT_ID IN (SELECT ARRAY.OBJECT_ID FROM dbo.PARSEJSON(@JSONVAL) LINES
LEFT JOIN dbo.PARSEJSON(@JSONVAL) ARRAY ON LINES.OBJECT_ID = ARRAY.PARENT_ID WHERE LINES.name = ‘lines’)
AND NAME IN (‘lineNumber’,’tax’,’taxCode’)
) AS JSONTBL
PIVOT
(
MAX(stringvalue)
FOR NAME IN ([taxCode])
) AS PIVOTTEDLINES
)
SELECT PLINES.lineNumber
, PTAX.tax
, PTAXCODE.taxCode
FROM PLINES
LEFT JOIN PTAX ON PLINES.parent_id = PTAX.parent_id
LEFT JOIN PTAXCODE ON PLINES.parent_id = PTAXCODE.parent_id;
[/sql]

It technically works, but it’s not the right solution to the problem. I either need to parse the JSON response with Javascript and work around OpenWebStudio, or work out the JSON query functionality in OpenWebStudio.

Meanwhile, grabbing the JSON and mangling querying it directly with SQL to grab a single tax rate wasn’t quite as ugly.  Same sample JSON input, consolidated SQL query.

[sql]

SELECT max(ALL_LINES.STRINGVALUE) AS TAXAMT
FROM (SELECT * FROM dbo.parseJSON(‘PUT JSON HERE’)) AS ALL_LINES
LEFT JOIN (SELECT * FROM dbo.parseJSON(‘JSON ALSO GOES HERE’)) AS LINE_ITEMS ON LINE_ITEMS.NAME = ‘taxCode’ AND LINE_ITEMS.PARENT_ID = ALL_LINES.PARENT_ID
WHERE ALL_LINES.NAME = ‘tax’ AND LINE_ITEMS.NAME = ‘taxCode’

[/sql]