SQL Clone
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 Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5571 Visits: 3035
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 Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 464
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.
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 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
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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
Say Hey Kid
Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)

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



GoofyGuy
GoofyGuy
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1347 Visits: 1468
Where did you hear that rumor? And by .Net do you mean ASP.Net or ... just Silverlight or ....?
GoofyGuy
GoofyGuy
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 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