Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server


JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server

Author
Message
bret.lowery
bret.lowery
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 190
Comments posted to this topic are about the item JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server
Phil Factor
Phil Factor
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1437 Visits: 2966
This is a fascinating bit of code that attempts an approach that would provide very useful facilities for any database that was integrating with a JSON-savvy application. I love the idea of doing JSON bulk load. JSON serialiser/deserialisers are written to perform a lot of different tasks, and there is room for several in the Database developers armoury.
I did find existing examples of JSON parsers and loaders on the Web, including one in native T-SQL that is one of the more, er, complex and lengthy T-SQL procedures I've seen

I suspect that this is a description is either mine from Simple-Talk, or Ric Vander Ark's from SSC. In both cases, the amount of source code to do the work is probably less than is in yours if you include what's in JSON.NET! (OK, each one performs very different functions). John Galt has done an interesting and very compact CLR that requires no external JSON library.


Best wishes,

Phil Factor
Simple Talk
SQL-DBA
SQL-DBA
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 462
Great stuff. JSON is well on its way to replacing XML for one simple reason - you can access the data from anywhere. This is not the case with XML web services for example. That nice asmx service that works fine on your local machine will not work for people who want to consume the data from another domain. This is what the same-origin policy ([url=http://en.wikipedia.org/wiki/Same_origin_policy][/url] ) is all about. Yes, it works in compiled languages like C# but a pure Javascript/jQuery solution will not work, unless you use JSON.
Sander A.
Sander A.
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 457
Great article.

We're starting to use JSON more and more so I can image I want to use code like this in the near future.
BlaineM
BlaineM
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
Thank you for the article. I've been a long time proponent of JSON. I've implemented something similiar in the CLR and I have a couple suggestions for handling datasets over 100 rows to get the performance up, size down, memory utilization down and retain complex data types:
1) Put a Data map at the start of your structure that stores the names of the columns size and type. Cast and validate on the client side if necessary.
2) Remove all the Key names from every entry and utilize the map and utilize standard nested arrays

Thank you,
Blaine
ryan-611467
ryan-611467
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
Thanks so much for the article. We are constantly looking for ways to handle json data especially as some of the xml api are getting depreicated. Thanks!
Christopher Klein
Christopher Klein
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 226
Great article, made for a very informative read. Many thanks!



GoofyGuy
GoofyGuy
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 971
I'm inclined not to use this approach, for these reasons:

1. As the author indicated, using the CLR within SQL Server opens up a number of potential performance and security problems. The author's code is quite admirable, but, I feel, ought to be executed outside the SQL Server environment.

2. DBAs generally are not facile at writing .Net code. Such would typically be a task for .Net developers, not DBAs. The author mentioned reviewing a T-SQL-based approach to JSON serialisation/deserialisation, but rejected it because of its length/complexity. Still, I wonder if revisiting a T-SQL solution might be worthwhile, in part for the reason of making it more accessible to DBAs?

3. There are many shops which use SQL Server as their standard RDBMS, but do not use .Net for application development. Also, the future of .Net is uncertain, with rumours abounding Microsoft is considering its replacement by HTML5/JavaScript. Again, why not reconsider a T-SQL-based solution for JSON serialisation/deserialisation, as SQL itself is in no danger of being replaced by another language/framework, and is more universally understood by DBAs and developers than is .Net?
John Hanrahan
John Hanrahan
Right there with Babe
Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)

Group: General Forum Members
Points: 759 Visits: 1462
Where did you hear that rumor? And by .Net do you mean ASP.Net or ... just Silverlight or ....?
GoofyGuy
GoofyGuy
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 971
John, the rumours involve both .Net and Silverlight. Another announcement - supposedly clarifying what was said at the D9 conference - is due from Microsoft in September, I believe.

Here are a couple of relevant links. As you might expect, the .Net/Silverlight developer communities are not happy.

https://forums.silverlight.net/forums/p/230502/562077.aspx

http://techbrij.com/674/windows-8-dotnet-html5-silverlight
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search