Home Dashboard Directory Help

Add native support for JSON to SQL Server, a la XML (as in, FOR JSON or FROM OPENJSON) by bret_m_lowery



Sign in
to vote
Type: Suggestion
ID: 673824
Opened: 6/3/2011 8:52:15 AM
Access Restriction: Public


While JSON import and export is possible in SQL Server using horribly complex T-SQL code or CLR integration using the Javascript JSON methods, such methods are system-resource intensive. It would be nice if MS could integrate JSON into SQL Server the same ways they do XML: say, a FOR JSON clause, an OPENJSON statement, etc.
Sign in to post a comment.
Posted by Koen Verbeeck on 6/26/2014 at 3:49 AM
What is this? The year 2002? Please add JSON support.

(yes there are workarounds, but there's nothing wrong with a feature that can make our lives easier)
Posted by iaef on 6/13/2014 at 10:36 AM
Michael from Microsoft said it was encouraging to have 6 votes in favor... now we have more tan 700. How much does it has to go up before it moves from encouraging to action? Let's hope you are guarding this top secret to announce it pretty soon to the world.
Posted by Jomit on 6/3/2014 at 10:49 AM
Thanks to Jeremiah Peschka for pointing me to this. It's a definitely must have for SQL Server. I am already planning to move some of my data storage out of SQL Server just cause of this...
Posted by Régis Baccaro on 5/15/2014 at 11:09 PM
Had a customer who dropped SQL Server and moved its private trading broker application to MongoDB because of the lack of JSon support in SQL Server.
Posted by SimonTeW on 5/9/2014 at 9:38 PM
I'm surprised by the fact that SQL Server 2014 doesn't support JSON. I Googled "sql server 2014 json datatype" assuming I would find excellent documentation along the lines of the XML datatype methods and looking forward to passing data around in JSON instead of XML. I was blown away when this feature request was the top Google result. 2014 and no JSON? Whatever have the SQL Server team been doing for the last two years? Given JSON's hierarchical structure I would have thought it wouldn't be that difficult to base a new JSON dataype on the XML one.
Posted by dethstar on 3/18/2014 at 2:37 PM
I certainly thought SQL 2014 would include native support for JSON, and I was extremely disappointed that it did not.
Posted by Gabriel MAYA on 3/18/2014 at 12:34 PM
Yes add support, it's too important if you work with JS
Posted by SAS007 on 3/16/2014 at 11:48 PM
Really needed, i hope Microsoft add it ASAP.
Posted by Ivan Paniagua Monroy on 3/7/2014 at 7:35 AM
It won't be included even in the last version SQL 2014.
Microsoft don't hear to community?
Posted by sunilmittal22 on 2/25/2014 at 1:17 AM
It is Feb 2014, and no commitment for native support for JSON Data type . Come on Guys
Posted by kingpoop1 on 1/16/2014 at 1:59 PM
I have a client that requests both XML and JSON. Seems like adding FOR JSON would be automatic and in fact should have come even before FOR XML.
using Javascript objects and arrays to render web page content kinda goes w/o saying. But, this simply does not surprise me at all. I mean look at the histor of SQL Server.
I've been using MSSQL since version 6.5 and it's becoming more and more just like the windows operating system. Too many cooks in the kitchen and you end up with a product that forgot where it came from. Have you installed Windows from scratch recently and then performed a Windows update? 130 Important Updates!!! 2 Optional updates! MSSQL is going in the exact same direction so don't expect your FOR ANYTHING anytime soon.
Posted by peter.lee.js on 10/21/2013 at 5:38 PM

Make JSON the "first-class" data type.

VS guys are already saying:

"JavaScript is a first-class language in Visual Studio 2013"

Posted by Veerendra Kumar on 9/18/2013 at 11:10 PM
I think adding Json support makes the product more hot....
Posted by Eder Côrtes on 8/23/2013 at 12:48 PM
Posted by denn86 on 8/21/2013 at 2:33 PM
Wake up guys!! It's 2013!! No JSON support mean no one is gonna use SQL Server...
Posted by daniel.ling.sungard on 7/23/2013 at 1:29 PM
Big +1 for this.
Posted by MichaelLee on 6/26/2013 at 2:06 PM
Definately +1. XML is out, JSON is in. For obvious reasons.
Posted by Netricity on 4/30/2013 at 3:51 AM
Adding a JSON data type as per XML would be very useful and is an obvious enhancement to SQL Server.
Posted by Mark90Exz on 4/23/2013 at 3:15 PM
I hoped that it will be included in the 2012 version. In VS2012 you can work directly with JSON thanks to WEB API.
Why can't exist a JSON Datatype ? , also in the Entity Framework you can't work with table datatype, i have to use XML don't misunderstand me XML it's great , but it will be nice to work with JSON. I'll like to see it on the R2 Version of SQL.
Posted by Henrry Pires on 4/10/2013 at 7:11 AM
That will be really great
Posted by juanMa2V on 2/26/2013 at 9:26 AM
The only question is... WHEN!?
Posted by ninjadavid on 2/18/2013 at 6:51 AM
Ughhh JSON is not really any lighter than attribute-centroc XML....BUT...JSON SERIOUSLY outperforms XML when it hits a client browser. Just look at the benchmarks for larger data sets. Also, there is expensive overhead to make multiple DB calls when querying SQL server, when you compare to running just one SP. How are you going to get nested data from one SP call without using something like FOR XML? And since current mainstream methods for converting XML to JSON do not output the same exact JSON, depending if there is one, none, or many records in nested XML, we are stuck making weird, arbitrary standards to get the best performance out of both SQL Server and the Browser when dealing with larger, nested data sets. If SQL server just output JSON in the first place, we could have the best of both worlds when it comes to performance and avoid weird, arbitrary conversion code.
Posted by Barlow Tucker on 12/13/2012 at 8:21 AM
JSON support would be awesome! It is so much lighter than XML.
Posted by BenHamiin on 12/11/2012 at 7:58 AM
Other vendors (PostGres) have added JSON support. I would really like to see the same thing from SQL Server.
Posted by alanr on 11/16/2012 at 8:58 AM
SQL Server needs a JSON column type similar to what now exists for XML. It should be possible to query the json doc and create an index on the column.

I would also love to see the ability to use text search on JSON. So you could free text search the title or subject or something else within a JSON doc. Apache SOLR does a good job of this today.
Posted by Saeid Hasani on 11/2/2012 at 12:27 AM
Because JSON is light and its implementations are not fully tested and supported by Microsoft, I vote in favor.
Posted by sundarapandian.pandi on 10/16/2012 at 10:08 PM
I vote in favor for Native support of JSON in SQL SERVER..
Please make this happen soon...
Posted by Stefan Zvonar on 7/10/2012 at 3:35 PM
I support this thread! Please make it happen :)
Posted by bathige on 7/10/2012 at 3:40 AM
I vote in favor of this. The future is JSON with the widely used devices to access data and present them in applications. Currently I'm doing a research of building a replication mechanism from SQL Server to mongoDB and in this I need to convert SQL Server data to JSON. If the SQL Server supports native JSON then my effort would be much more less.
Posted by SAinCA on 6/26/2012 at 10:42 AM
Absolutely ALL traffic between the UI and our middleware API, hosted in IIS7, is JSON. The API simply translates SQL recordsets into JSON and JSON requests into parameterized SP requests. Native JSON support could eliminate 20% of our our application code in one fell swoop. I'm hoping native JSON will also speed our ability to transition from FLEX to HTML5. Please prioritize upward.
Posted by NJDS on 6/25/2012 at 10:52 AM
Was just searching about it and stumbled upon this connect item. We have been struggling with JSON data load in Sql Server . We are advertiser based company and get data feed from several external companies.. Most of the feed sent to us in JSON format. We built in-house solution in C# using NewtonSoft.Json api's to convert to xml and then finally load it into DB using SSIS.. This has been unpleasant wild ride with this solution with lot of loopholes and crashes with different structured data from different external vendors. Now we are looking into MongoDB which makes it piece of cake to be loaded in db. Sql Server is our favourite DBMS but absence of these small practical features is driving us away from Sql Server :-(    C# already have JSON namespaces so I can't fathom why it is not included in Sql Server 2012.
Posted by rwillemain on 6/25/2012 at 7:53 AM
nosql inter-communications facilties within sql server , would be on my highest "wish-list" !
seeing a wild-west array of "big-date" stuff.
Not sure how to filter thru it all ,, but after reading about JSON ! -- sure sounds good to me, but my goodness I could never totally detach from my ms-sql servers.
Posted by Mark Tassin on 6/25/2012 at 7:41 AM
JSON is nothing more than Entity Attribute Tags for a given entity right?

This can be done with SQL now. I don't see the point of this at all.
Posted by Zack Turnbow on 6/25/2012 at 7:00 AM
Since there is a huge driving force for distributed cloud based data, gaining quick access to the data is critical when developing the applications to consume the data. For Microsoft, SQL Server, to stay competitive, I believe it is vital for them to provide JSON support. Please add support for storing JSON and the ability to query it.
Posted by dbproger1 on 6/25/2012 at 5:15 AM
Gotta have JSON. I'm developing a new distributed app and this is the best way to store complex settings with minor variations for some locations. I'm still using JSON, but not SQL Server. Hundreds of licenses get bypassed. (hint, add JSON).
Posted by rveluthattil on 6/18/2012 at 8:54 PM
JSON is the future. There was a time when it was XML and now it is JSON. There is no running away from it. I totally agree with bret. Of course we can use Mongo / Raven and all those but in the database side (not NOSQL :-)) it would be awesome. Applications storing JSON data and retrieving them would be like icing on the cake.
Posted by Sankar Reddy on 6/15/2012 at 9:35 AM
Conor always asks us to provide real world scenarios to support the usecase when asking for suggestions/improvements. So here is one.

We were a complete SQL Server shop and with shifting business [now heavily using HTML5 technology] needs we need to support JSON and since there is no native support, we have stopped using SQL Server and moved to Mongo. We still have some SQL Server but moved the analytics + some additional functionality to Mongo even with all the limitations that come with Mongo.
Posted by Peter Skoglund on 6/14/2012 at 3:41 AM
We need to have good support for JSON and the very important querying and parsing of JSON structures.
Nosql database like Mongodb, Cassandra, RavenDB etc all ready have it.
Posted by barkermn02 on 10/26/2011 at 4:27 AM
I would really lik to see this added to SQL Server,

With HTML5 and Javascript Really being Shuntted into full aplication Environment with Windows 8, iOS Devices Windows Phone OS, Android it would be good to use a remote server API to just call Stored Procedures and get the data back without using C#, VB or any other lang to the rebuild the data as JSON just so Javascript could handle it better would mean less code between your HTML5 App and your Database
Posted by CAD bloke on 10/13/2011 at 4:07 PM
There's always RavenDB ...
Posted by Microsoft on 8/15/2011 at 3:00 PM
Hi Bret

Thanks for filing this request. It is encouraging to see already 6 votes in favor. Let's hope more votes come in :).

Best regards
Sign in to post a workaround.
Posted by Jeremiah Peschka on 6/5/2014 at 12:58 PM
A legitimate workaround is to create a user defined type using CLR: http://msdn.microsoft.com/en-us/library/ms131120.aspx

This won't necessarily supply indexing and it won't get around the overhead of parsing and unknown JSON structure. Tools like NewtonSoft's JSON library carry significant overhead when parsing an unknown JSON structure. Heavy reflection is involved which may make a CLR extension for JSON support a non-starter.
Posted by MarkTab marktab.net on 5/15/2014 at 8:52 PM
http://james.newtonking.com/json Popular high-performance JSON framework for .NET
http://technet.microsoft.com/en-us/library/hh849898.aspx ConvertFrom-Json in PowerShell
http://technet.microsoft.com/en-us/library/hh849922.aspx ConvertTo-Json in PowerShell
Posted by quandary1 on 4/1/2014 at 8:17 AM
http://www.postgresql.org/ with http://www.tpostgres.org
Posted by ScottyWakefield on 3/6/2014 at 10:13 AM
Posted by ercananlama on 7/19/2013 at 6:54 AM
Check the following project:

Posted by ricva0 on 4/23/2013 at 10:44 AM
Posted by leorzz on 8/24/2012 at 1:44 PM