Consuming hierarchical JSON documents in SQL Server using OpenJSON

Over the years, Phil was struck by the problems of reading and writing JSON documents with SQL Server. Now that SQL Server 2016 onwards has good JSON support, he thought that the articles would be forgotten. Not so, they continue to be popular, so he felt obliged to write about how you can use SQL Server's JSON support to speed the process up.

Articles by Phil Factor about JSON and SQL Server:

  1. Consuming JSON Strings in SQL Server (Nov 2012)
  2. SQL Server JSON to Table and Table to JSON (March 2013)
  3. Producing JSON Documents From SQL Server Queries via TSQL (May 2014)
  4. Consuming hierarchical JSON documents in SQL Server using OpenJSON (Sept 2017)

JSON isn’t the easiest of document formats for transferring tabular data, but it is popular and you are likely to need to use it. This used to be a big problem in SQL Server because there wasn’t a native method for either creating or consuming JSON documents until SQL Server 2016. I wrote a succession of articles, Consuming JSON Strings in SQL Server, Producing JSON Documents From SQL Server Queries via TSQL and SQL Server JSON to Table and Table to JSON that illustrated ways of doing it, slow and quirky though they were. They used a simple adjacency list table to store the denormalised hierarchical information so it could be shredded into a relational format. This table stores sufficient information that you could, if you really wanted, create an XML file from it that loses nothing from the translation.

I wrote these articles before SQL Server adopted JSON in SQL Server 2016. Then, it was difficult. Actually it is still hardly plain sailing, but there are some excellent articles on the Microsoft site to explain it all.

I was asked the other day how to use OpenJSON to parse JSON into a hierarchy table like the one I used. The most pressing thing to do was to make a sensible substitute for the rather obtuse ParseJSON() function that used OpenJSON() instead.

This is the build script for the recursive TVF that I use nowadays in getting the hierarchy table. This is a direct replacement for the old ParseJSON function that you’d only need on an old version of SQL Server. It looks scarily long, mainly because I’ve included the dbo.Hierarchy table type that is a dependency

To get OpenJSON to work, you will need to be at the right compatibility level. This code will, if you change the ‘MyDatabase’ to the name of your database, and have the right permissions, set the correct compatibility level.

Firstly, we will need to define a user-defined table type that can be used as an input variable for functions.

Now we can go ahead and create the actual function. This is a recursive multi-line table-valued function. Note that there is only one parameter you need, which is the string containing the JSON. The other three parameters are only used when the function is being called recursively. You need to just use the DEFAULT keyword for these other parameters.

And we can now test it out

This will produce this result

The surprising fact from putting it in a test harness with some more weighty JSON is that it is slightly slower (10%) than the old function that I published in the dark days of SQL Server 2008. This is because recursion of a scalar function is allowed, but it is slow. I went on to test out a similar recursive function that did the conversion back to JSON from a hierarchy table, and that was seven times slower.  Although recursive scalar functions and CTEs are easy to write, they aren’t good for performance.

We can test out our new function by converting back into XML

Now we can check that everything is there from the contents of the @xml variable.

To capture the entire hierarchy, we had to call OpenJSON recursively. Usually, OpenJSON is easier to use. If the JSON document represents a simple table, we can be distinctly relaxed.  The OpenJSON routine is excellent for the chore of turning JSON-based tables into results. There was a time that this was much more awkward (see this article)

Which gives …

SQL Server’s JSON support is good and solid. It makes life easier for conversion but it is not as slick as SQL Server’s XML support. It is certainly a lot quicker and more effective than was possible before SQL Server 2016.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue