August 30, 2015 at 9:40 pm
Comments posted to this topic are about the item JSON Select - Easily query values from JSON
August 31, 2015 at 2:11 am
This could come in very handy. XML is a dying format and I deal with lots of json data. It's good to see movement in this direction. I need to play with the functionality, but a nice option would be to read the json from web services, particularly REST.
August 31, 2015 at 2:36 am
SQL 2016 which is at stage CTP2 has native support for JSON.
August 31, 2015 at 4:42 am
True, but millions won't be upgrading to 2016 straight away, possibly years.
This is a great library and I'm sure people wish it was around a few years ago.
August 31, 2015 at 5:23 am
Thats very true too, but I'm referring to point b. in the article: Wait for Microsoft to implement JSON Support and then upgrade. Upper-middle-management will have to be a bit patient.
August 31, 2015 at 6:57 am
Nice article - well done. I thought I would add an additional option to the mix. I am feeling extra cynical this am 🙂
Option 5. Explain to upper management that the developers claim to "saving" time by not using a normalized database actually created a more impactful situation where getting any useful data quickly and efficiently out of this database will become more painful over time as more data flows into the system. Now you have a text data store where trying to get the data that you need to run the business and actually make meaningful decisions on takes far more complexity than doing it right the first time.
Like every tool JSON has its place in the useful stack - but replacing an application store where it typically would be written as a relational store, not such a good long term plan.
August 31, 2015 at 8:00 am
Scott, you're right. For the sake of an extra 1 to 2 hours of initial development time the individual json values should be sent to SQL as separate columns or the json broken apart by a stored proc and then inserted into columns. Either way the native power and full range of SQL functionality can then be applied to the business data.
I'm not knocking the json/sql library, I think it's very cleverly designed by Joshua and I'm sure it will be warmly greeted by many developers or DBAs who already have a json column which is, until now, very awkward to query.
Heck, I just remembered we've got a column of json! It's only intended to be squirted into a web page for browsers to read, but with this new library I could query that data if there was a future requirement to.
August 31, 2015 at 8:51 am
Like every tool JSON has its place in the useful stack - but replacing an application store where it typically would be written as a relational store, not such a good long term plan.
I agree with Scott.
JSON, I think, is best applied as a data transport language - the most frequent use case being the data exchanged between web service provider and consumer.
August 31, 2015 at 9:25 am
Pulling data in from a web service from within a database routine is an interesting idea, in fact I inherited an application that did exactly that in a previous job (and that approach is definitely not an easy one to get right! It was a bit of a nightmare and we ended up rewriting much of the application due to some major performance issues).
I haven't included that functionality thus far, and in fact there are pretty restrictive constraints on what parts of .NET you can use in CLR code in SQL Server that make this difficult to achieve. It can be done but you have to add the assembly with the "external_access" permission set and then you need to be very careful how and what you're doing. As it seems many DBAs are reluctant to enable CLR integration at all, you might be pushing your luck with this one!
August 31, 2015 at 9:52 am
I agree that it's generally better to opt for individual columns for values rather than using JSON or XML or whatever else, however I've worked in places where it's very painful to get an SQL script run in a production environment and I can see how people get tempted to avoid that pain by using a JSON column. This is exactly what happened to a team I worked with in the past but it eventually became a problem when it came to reporting. This is where I got the idea for JSON Select from 😀
Personally I prefer a well designed database but with terms like MVP (that's "Minimum Viable Product", for the unindoctrinated :-)) being overused these days I can only assume we'll see more of the JSON-column style of development.
August 31, 2015 at 12:00 pm
Josh, I want to reiterate that I really enjoy and appreciate your article and hope that you will write more in the future. I was just stating that all too often we (DBAs) are the victims of some very poor design decisions. All too often I have to swing in like a superhero and fix databases that were designed and implemented by development staff that just chased the latest and greatest technology while ignoring the business needs; good for my career, bad for my blood pressure. The tide is turning where the business is seeing that getting the data out is just as important as putting the data in and without the ability to visualize, analyze, and act on this data there may was well be no data.
August 31, 2015 at 2:46 pm
Gary7512 (8/31/2015)
True, but millions won't be upgrading to 2016 straight away, possibly years.This is a great library and I'm sure people wish it was around a few years ago.
I'm one of those that won't be transitioning. We're still on 2008 R2.
September 2, 2015 at 9:40 am
Great article Josh! I have not yet played around with SQL Server 2016 but, based on everything, Microsoft is trying to make it more "big data friendly". That would be difficult without adding support for JSON. Periodically in my I have ETL tasks that require me to consume JSON data; until now I have been using Phil Factor's parseJSON function[/url]. Again, nice work.
-- Itzik Ben-Gan 2001
September 2, 2015 at 9:55 am
SQL-DBA (8/31/2015)
This could come in very handy. XML is a dying format and I deal with lots of json data. It's good to see movement in this direction. I need to play with the functionality, but a nice option would be to read the json from web services, particularly REST.
JSON is becoming more common but XML is far from a "dying format" (much to the chagrin of some DBAs I know). Web services, for example, are made up of XML. WSDL is based on and written in XML... SOAP, RDF, RSS ... all based on and written XML as well. If you wanted to consume json via a web service you could do so using using C#, see this article which describes how to do so using .NET, a language based on XML.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply