Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A Function to Split JSON Data Expand / Collapse
Author
Message
Posted Wednesday, September 23, 2009 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 2:38 PM
Points: 102, Visits: 202
Comments posted to this topic are about the item A Function to Split JSON Data


Post #792379
Posted Wednesday, September 23, 2009 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 24, 2009 6:36 AM
Points: 1, Visits: 5
Wouldn't this be a good spot to use a CLR function?
Post #792583
Posted Wednesday, September 23, 2009 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 2:38 PM
Points: 102, Visits: 202
I have been using this for quite a while, although not as complete as it is now. In order to maintian compatibility with earlier versions of SQL Server, I left it in T-SQL. Also, most of companies I have consulted for are not willing to allow CLR's (yet ).


Post #792700
Posted Wednesday, September 23, 2009 12:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 01, 2013 8:49 AM
Points: 79, Visits: 1,328
Question about the use of the CONTINUE keyword in the function. Why was this chosen over using an IF ELSE IF structure? Just curious if there was some performance issues or something else that swayed you toward it.
Post #792863
Posted Wednesday, September 23, 2009 3:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 2:38 PM
Points: 102, Visits: 202
Good question. I have not done any tests if it is quicker to use IF ELSE or CONTINUE. I used CONTINUE because it made the code easier to read and maintain (for me). There are quite a few checks that are being made and I reordered them as the function grew in size. Using CONTINUE helped me in knowing when the processing was finished for that part and I was ready to get the next character. The code "blocks" are easier for me to see, and edit, or highlight and move.




Post #792977
Posted Monday, September 28, 2009 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 13, 2009 7:18 AM
Points: 18, Visits: 73
Don't you think if you've ended up with JSON at the database, something is wrong?
Post #794412
Posted Monday, September 28, 2009 10:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 2:38 PM
Points: 102, Visits: 202
Not at all, it really depends on your design. I certainly would not recommend it for every solution. Is storing and processing of XML data the best way to do accomplish a task? (Sometimes it can be.) I also use the function just in the database itself, stored procedures calling other stored procedures or functions.

When doing a system design we consider ease of maintenance for the client, loads on different places in the system, volume of data, security and so forth. Does a AJAX call have to have the C# code process all the parameters into a stored procedure? Some designs require that more work is done in the database, rather than in the client or service portions of the code.

Here are several examples where I felt the JSON format is useful.
Example one, consider a web page that searches for authors and books. The page has input parameters for first name, last name and ISDN number. The page uses a AJAX call to a web method which creates the call into the stored procedure.

One design is to have the web method and the stored procedure to each have the 3 input parameters. My method, using the function, allows us to have only one input parameter. The advantages are:
1) the web method becomes very simple, maintenance is lower.

2) if I need to add more parameters, like a book title, I need to only change the web page and the stored procedure. The web method does not need to change.

3) validation of parameters need only take place in the stored procedure.

Example two, the results consists of 20,000 records. I create a temporary table to store pointers to results, and have ranking, sort order, paging, and other parameters. The user gets 1 page of 20 records instead of all the records. I store the request criteria in the JSON format. The next time the same requests comes in I test to see if that result set has already been processed and, if so, return those results. This saves time because the search, and sorting does not have to be made again. Storing the criteria can be used for analysis reporting on the searches.

Example three, occasionally I need to split string values into tables. This function allows this by surrounding the string with brackets. I eliminate having to have two split functions.

Example four, storing application options and configuration, user preferences, security information. In some cases it is easier to store the information as a string rather than having many columns.



Post #794610
Posted Friday, April 22, 2011 4:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 561, Visits: 2,418
Don't you think if you've ended up with JSON at the database, something is wrong?

It is as wrong as having XML at the database. If you are supporting applications that use XML for communicating data structures, then there can be no logical difference in supporting JSON or YAML. Whether you like it or not, SQL Server now has the built-in tools to fully-support XML. We have, in effect, 'swallowed the camel' already.
Well done with this one, Ric. I must admit that I'd been developing mine for some time as well (I've a YAML shredder that had to do JSON as well) and didn't spot that you'd published your JSON shredder before I did mine at 'Consuming JSON Strings in SQL Server' on Simple-Talk. (strangely, Google never popped it up)
I suppose it should be easy to do a CLR version, since the .NET library actually has two JSON shredders, though I believe that one is deprecated. the other one seems a bit 'special-purpose', and I haven't worked out how this helps to get a result that can then be put into a table.



Best wishes,

Phil Factor
Simple Talk
Post #1097381
Posted Friday, October 05, 2012 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 4:59 AM
Points: 12, Visits: 33
If I have a table with many fields and only one has Json format..
I could use the function in order to consume the Json format- I would need to run the Split Json Data for every row and still have a link to the main table - an ID.
Have someone tried it before?
I don't want to construct a heavy cursor to do that. I would prefer to bulk it out.
Suggestions?
Post #1369025
Posted Friday, October 05, 2012 12:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 2:38 PM
Points: 102, Visits: 202
I am not quite sure what you are trying to do. If one cell has a JSON format, what do you hope to do after the function returns a table?


Post #1369248
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse