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.

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

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.

Keep up to date with Simple-Talk

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

This post has been viewed 126038 times – thanks for reading.

  • Rate
    [Total: 1    Average: 3/5]
  • Share

Phil Factor

Follow on

View all articles by Phil Factor