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, and wrote several articles on ways of overcoming these problems. 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)
  5. Importing JSON data from Web Services and Applications into SQL Server(October 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.

The OpenJSON() function is, at its simplest, a handy device for  representing  small lists or EAV table sources as strings. For example you can pass in a simple list (roman numerals in this case) …

… and get this.

You can pass in an EAV list, with both keys and values…

… which produces … 

But if you don’t like this format you can have a  traditional table source.

This gives the result..

As you can imagine, OpenJSON’s use with lists is useful where you have to deal with a variable number of parameters. In this example, we pass a list of object IDs to an inline Table-valued function  to get back  a table with the object’s full ‘dotted’ name.

This is just scratching the surface, of course. In this article OpenJSON is destined for greater and more complicated usage to deal with the cases where the JSON is hierarchical.

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 by using OpenJSON() instead.

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. First we will try 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.

You can avoid recursion entirely with SQL, and almost always this is an excellent idea. Here is an iterative version of the task.  It looks less elegant than the recursive version but runs a five times the speed.  a seventy row JSON document is translated into a hierarchy table in 25 ms on my very slow test server!

…and a quick test run confirms that this version is much faster, and a great improvement on the old parser from the dark days before OpenJSON

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.