A Function to Split JSON Data

Question

Post reply

A Function to Split JSON Data

ricva

Default port

Points: 1404
More actions
September 23, 2009 at 12:18 am

#204887

Comments posted to this topic are about the item A Function to Split JSON Data

Viewing 13 posts - 1 through 12 (of 12 total)

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

jeff-797231 SSC Rookie Points: 41 More actions · Answer 1

jeff-797231

SSC Rookie

Points: 41

September 23, 2009 at 6:54 am

#1057459

Wouldn't this be a good spot to use a CLR function?

ricva Default port Points: 1404 More actions · Answer 2

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 🙂 ).

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 3

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.

ricva Default port Points: 1404 More actions · Answer 4

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.

i.jones SSC Enthusiast Points: 176 More actions · Answer 5

Don't you think if you've ended up with JSON at the database, something is wrong?

ricva Default port Points: 1404 More actions · Answer 6

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.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 7

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[/url]. (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

Girl SSC Journeyman Points: 96 More actions · Answer 8

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?

ricva Default port Points: 1404 More actions · Answer 9

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?

Girl SSC Journeyman Points: 96 More actions · Answer 10

I want to have that table with a relational id to the row where the jason string belongs

Girl SSC Journeyman Points: 96 More actions · Answer 11

I got it now.

I wanted to join a table with a function - the one that consumes jSon

I did it with a cross apply.. 🙂

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 12

@Ric,

Ok... so I'm only 14 years late in seeing this the first time. 😀

I've not needed to work with JSON up until now. It's gotten out of hand in our databases and people are doing all sorts of weird stuff. I need to test it for performance (it's loaded with RBAR) but it looks like your wonderful function is going to go a long way in helping me clean things up. Thank you VERY much for putting this together and for the great article, as well!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)