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

Status : 

 


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

Description

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 Riley Major on 5/4/2015 at 11:37 AM
Looks like this will be available in SQL Server 2016:

"Additional capabilities in SQL Server 2016 include: ... Native JSON support to offer better performance and support for your many types of your data."

http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx
Posted by Hrair K on 5/1/2015 at 11:24 AM
Dear SQL Server Team,

I used to work for Microsoft for 9 years, I STILL LOVE Microsoft very much. Out here in the non-Microsoft world, JSON is the #1 choice for many different data exchange interfaces. In the next version of SQL Server, native JSON support absolutely needs to be supported for SQL Server to continue to be a compelling, if not usable, product. SSIS also needs to be able to import and output JSON-formatted files! Honestly, this should be a free give-away to those of us who own a license of SQL Server 2014 already, that is how desperately we need the product to work with JSON. Thanks for hearing me out.
Posted by Daniel Macho on 1/26/2015 at 4:05 AM
Another alternative: https://jl45sql.wordpress.com/2012/06/20/json-formatted-output-in-sql-server/

Posted by Daniel Macho on 1/26/2015 at 3:27 AM
while we wait for native support here you have some implementation that apparently works

https://www.simple-talk.com/sql/t-sql-programming/producing-json-documents-from-sql-server-queries-via-tsql/
Posted by JSON4SQL on 1/20/2015 at 5:51 AM
We have developed a JSON type for SQL Server available for purchase at http://www.json4sql.com. It is a port of the binary JSON type just released for PostgreSQL with several other features added. We have found performance in our applications to exceed that of the native XML type both in terms of query performance, simplicity, and storage size.

It offers:
•Table value functions for returning elements of JSON objects / columns as a result set
•A fluent API for creating, modifying, and accessing object properties
•JSONPath expressions to set, remove, and get data within JSON objects (similar to XPath expressions for XML)
•Aggregations for converting result sets into objects or arrays (substitute for FOR JSON recommended by original poster)
•Binary storage for fast data access without reparsing
Posted by Ostati on 1/14/2015 at 9:29 AM
At the moment there are 933 upvotes that MS is just saying "F"-you to... I lost count how many poeple I've talk to at various user groups etc. and they're all in agreement that SQL should support JSON. Maybe I shoud just consider other RDBMS solutions?...
Posted by juanMa2V on 12/6/2014 at 12:03 PM
This would make my life so much easier!!!
Posted by SAS007 on 12/3/2014 at 8:31 AM
This feedback most voted since two years without any action or reply !!!!
Really shame on you
Posted by Tom from Hemet on 11/30/2014 at 10:39 PM
Sure, the Azure platform offers several NOSQL solutions with true JSON support now, but that's not the issue. Many of us have a workflow that we are comfortable with using SQL Server and do not want to invest time and money splitting between SQL and NOSQL options. Also, many of us work with legacy database systems which would benefit hugely from native JSON support. I sincerely hope Microsoft realizes the importance of JSON support before its too late.
Posted by Karl He on 11/8/2014 at 12:28 AM
Please don't close the issue as "Won't fix".
Posted by Jens Frandsen on 10/16/2014 at 12:45 PM
Please consider adding this feature. I've used MS SQL Server almost exclusively since 1999. When XML support came out, I boasted how great it was ... it's fantastic. Now I'm discouraged anytime JSON comes up.

A) Some users would upgrade for this feature. It was the first thing I looked for when checking out SQL 2014
B) There are many api's (web services) and js gui's (grids, suggests, menu's, etc) out there that work exclusively with JSON
C) JSON is a wonderful data format. It's light weight and integrates seamlessly into js objects
D) Current workarounds can take control away from the db admin.
E) Current workarounds are inefficient
F) Current workarounds are slower to build
G) I'm pretty sure all major competing SQL Servers have this feature already ... most for quite some time.
H) We're paying good $$ while open source alternatives have this feature for free.
I) Who will migrate to MS SQL that uses this feature?



Posted by onurg on 10/2/2014 at 3:29 AM
Oracle 12 has JSON support.
Posted by mistarSteve on 9/30/2014 at 11:51 AM
Pretty astounded that SQL 2014 doesn't even have this. Really no excuse for it w/ the increasing popularity of JSON and people realizing for many cases XML is inappropriate. If features like this continue to be ignored SQL Server is going to slip to 'good enough for Grandpa' status in the tech world...
Posted by David Lafayette on 9/23/2014 at 1:16 PM
Lack of JSON support in MS SQL is on the short list of why we may need to migrate away from MS SQL. I would love to remove this item from that list.
Posted by Jovan Popovic (MSFT) on 9/10/2014 at 7:46 AM
There are a lot of scenarios for JSON usage we would need to consider.
As an example, is something like transforming relational queries to JSON format (e.g. with something like FOR JSON) and JSON string values or nvarchar values in cells to tables (e.g. using some table value function like OPEN_JSON(json_data)) sufficient enough? With these features we could easily return JSON to client apps and enable some analytic against the JSON data stored in tables using standard Sql.

If we store JSON data in a NVARCHAR() column would it be important to update particular properties of JSON data or it would be acceptable to just overwrite entire content of the cell when it need to be changed? For this scenario we can already create CLR type that internally contains JSON text and update some particular properties using xpath or json path.
Do we need something like the current XML functions such as query(), nodes(), exists(), value(), etc.
How important would be to enable access to JSON data via some HTTP end-point (something like REST service within Sql Server similar to the services we have in DocumentDb)? Maybe adding support for JSONP so JavaScript client applications could directly fetch data from Sql server?
Do wee need indexes that include properties of JSON or we need to be able to create computed columns based on properties of json strings?
Are there any additional features we would like to have?

Could we brainstorm what would be the best set of features that could be considered as JSON support in Sql server?
Posted by jay_nani on 8/27/2014 at 11:00 PM
Came back to this connect item after 2 years but still no comment or resolution from MS. Weird when you guys just launched DocumentDB DB with full JSON support . Interesting to see that MS continue to ignore huge SQL developer community requests. MS needs to wake up before their billion dollar revenue product fully taken over by open source DB's that has been supporting this functionality for few years now.
Posted by John Kotuby on 8/6/2014 at 10:35 AM
Of course native JSON support should be in SQL Server. I am shocked that it's not already there. Who is running that show at MS anyway? Do you need more layoffs before you guide the ship properly?
Posted by Rei on 7/29/2014 at 1:19 PM
Yes, please consider NoSQL on premise.
Within our company NoSQL has been one a major topic on the developers and architects request, from the DBA architecture team.
o new databases as a 2 columns one being a json blob the other a key
o replication against Elastic Search bucket
o Documents get synchronized asynchronously and ES still maintains its non-blocking eventual consistency
o powerful search capability of Lucene
o As the json object gets more complex and bigger, Protocol Buffers truly shines and helps reduce some latency for the binary store.
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
Hey!

Make JSON the "first-class" data type.

VS guys are already saying:

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

http://msdn.microsoft.com/en-us/library/vstudio/hh334522.aspx
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
Quando??
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.
Regards.
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
Michael