JSON Select - Easily query values from JSON

  • Joshua Healy

    SSC Veteran

    Points: 240

    Comments posted to this topic are about the item JSON Select - Easily query values from JSON

  • SQL-DBA

    Hall of Fame

    Points: 3004

    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.

  • laurens-1042919

    SSC Veteran

    Points: 201

    SQL 2016 which is at stage CTP2 has native support for JSON.

  • Gary7512

    SSC-Addicted

    Points: 469

    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.

  • laurens-1042919

    SSC Veteran

    Points: 201

    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.

  • Scott Abrants

    SSCommitted

    Points: 1503

    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.

  • Gary7512

    SSC-Addicted

    Points: 469

    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.

  • GoofyGuy

    SSCertifiable

    Points: 6029

    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.

  • Joshua Healy

    SSC Veteran

    Points: 240

    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!

  • Joshua Healy

    SSC Veteran

    Points: 240

    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.

  • Scott Abrants

    SSCommitted

    Points: 1503

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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.

  • Alan Burstein

    SSC Guru

    Points: 61026

    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.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

  • Alan Burstein

    SSC Guru

    Points: 61026

    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.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

  • DennisPost

    SSCrazy

    Points: 2691

    Nice work Joshua!

    I really like the ability to index!

    @alan, I've been using the PhilFactors fabulous funky function as well.

    I get the feeling I'll be using JSON Select before long.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply