26 March 2013

SQL Server JSON to Table and Table to JSON

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.


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


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.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 3    Average: 4.3/5]

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Follow on

View all articles by Phil Factor