SQL Server JSON to Table and Table to JSON

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)

One of the surprises that I got from writing for Simple-Talk was the popularity of my article Consuming JSON Strings in SQL Server.  I hadn’t really expected it to be so appreciated; in fact I was nervous about posting it at all. It came from a real requirement I had at the time, but I got interested in it in order to show how one could analyse hierarchical data documents iteratively in TSQL. Also, an anonymous troll on StackOverflow had told me it was impossible.

There were a few questions that I’d left unanswered. The first was how to read a JSON string as a table, and the other was how to produce a JSON document from a table. I ran out of space in the article and thought it was really the sort of thing that readers of the article would want to experiment with.

In this blog, I’ll take the whole process around the circle.  You’ll notice that much of this is a bit cumbersome, but I only use short JSON Strings and haven’t noticed a performance problem in doing this.

We start with a table.

StartTable.jpg

And by a quick use of …

We can convert it to a PowerShell array that we can use as if it were a table

… and this we can use to get the JSON version of the SQL Table, and from there to JSON…

We could, of course, have got the table as a Datatable by ADO.NET and converted that to JSON

Which produces the same JSON String. This is just a simple example:  This will not cope with a Varbinary  binary, image, timestamp or rowversion field.  You would need to convert it to a Base64 string  via [System.Convert]::ToBase64String($Reader.GetValue($i)).

To produce this directly from the table takes more effort. First you would need to install the library from my JSON article. You can convert the table to a hierarchy table.

from that point, it is easy to create the JSON string

And it won’t surprise you that the string is more or less identical to the one we got in PowerShell.

Of course, reading a JSON string as a table is rather easier.

And we can just then use it in any SQL Expression like this

EndTable.jpg

You’ll notice that I’ve given you a ‘parent_ID’ to give you the intrinsic order of the rows, since these things can be significant in a JSON document.

Of course, you can do some dynamic SQL to deal with any JSON String, but I don’t like to do this since there is no guarantee that a JSON string represents table data. Also, when you are getting json strings from an application repeatedly, they tend to  carry the same metadata.