Flexible Data Insertion Procedure With JSON Datatype

For a long time now I have been laughing at these guys who are all hyped up about No-SQL databases. I have always thought that why bother handling data objects that you cannot determine their structure in the first place? That sort of thing should be left for application servers to handle the JSON and XML tangled mess. We should just leave SQL for 'Structured Relational Data', but with time many famous RDBMS' like Oracle, MySQL and Microsoft SQL Server started to introduce support for JSON data objects and I thought to myself '"OMG! Not these guys too unsure". It seemed like everyone was going this direction, so afraid of being left in the stone age, I packed my travel bag (*just an expression, totaly just tried out various things on my pc*) and embarked on a quest to find the ultimate application for these pesky JSON rodents.
So what is the advantage of non-tabulated data objects like JSON... Well the most obvious one is that they are strings so you can pass many variables to a function/procedure at once in a single string variable. Your function/procedure can then look for just the variables it needs and can ignore the rest, e.g foo('{important:16}') and foo('{useless:true,important:16}'). The neat thing about this is you can still write your piece of code withought being mindfull of the useless variable, your function definition will just focus on the important ones.
I have recently been challenged with writing functions that insert data into tables. Just as you think you have it right, the system owner says can you add these fields too... mad Great now i have to edit the function code again. As a matter of fact this happens so often that by the end of it all your function code becomes thicker than the amazon jungle and harder than ever to debug. Imagine having to extend the function addStudent(name) to be able to catter for subject and score, addStudent(name,subject,score). Now what if i want to add just the student and subject, but leave the score for later on. Sudenly the function call starts to have port holes, e.g. addStudent('Tawonga','SQL Scripting', null)... Nulls?! like seriously Nulls in function calls? so if my table has 40 columns and i just want to add a record with values in 3 columns I have to have a function call with 37 nulls? Forget it! blink
Ok i think we can have a single procedure that can be called like addStudent('{"name":"Tawonga","subject":"SQL Scripting"}'), this would be nice. As a matter of fact in the 40 column example i would only need to pass the object {col1:val,col2:val,col3:val} and the rest of the 37 fields will have the default table values. But wait, json objects can also be passed as an array [{col1:val,col2:val},{col1:val,col2:val,col3}] and our procedure can have looping functionality to insert multiple records from one call. Now we can change our respective table anyhow without being scared that changes will break the insert procedure... preeeety neat! +1 to JSON, yey!! blush
After discovering this while doozing I whipped out my Jedi light keyboard and got to the badass code whooping.
1. We take the passed JSON object and determine its depth,
2. If the JSON object is multi record then we will loop the process multiple times to insert every record, else we will do one insert iteration
3. Each iteration will create a prepared insert statement of the form insert into tbl (json keys of the current record) values (json values of the current record) and making tbl a parameter means we can reuse this procedure for any table.
Great! so the code in mysql looks something like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_json_insert`(IN `tbl` TEXT,IN `jd` JSON)LANGUAGE SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT 'insert into table from json object'BEGIN/*=======================================================================================================================Version: 1.0Author: Tawonga D. MsiskaDate: 11-July-2017License: IV Internal Apps 1.0Syntax: call pr_json_insert(tbl,'{"col":"val"[,"col":"val"][...]}' or '[{"col":"val"[,"col":"val"][...]}[,{"col":"val"[,"col":"val"][...]}[...]]')Description: Inserts a record or multiple records into the table specified by tbl. The data has to be specified in akey value pair json object where the key is the column to insert and value is the data to insert in therespective column. There can be multiple columns for the json object to insert multiple values of a singlerecord. Where more than one record is required to be inserted into the table then the data must be specifiedas a json array of the key value pairs of the json record objectExample: pr_json_insert ('test', '{"student":"tawonga","subject":"JAVA Codding","score":"39"}')pr_json_insert ('test','[{"student":"tawonga","subject":"SQL Scripting","score":"96.70"},{"student":"tawonga","subject":"HTML Codding","score":"73.20"},{"student":"tawonga","subject":"PHP Scripting","score":"67.53"}]');=======================================================================================================================*/set @rt='';set @multi = if(json_depth(jd)=3,1,0); #--== if there we need to insert mutliple records, else asume single recordset @recs = if(@multi=1,json_length(jd),1); #--== set the number of records to inserset @r=0; #--== set the record to begin with, first record will always be at 0#--== Loop through all data records and insert themREPEATset @jdat = if(@multi=1,json_extract(jd,concat('$[', @r,']')),jd); #--== get the record set to insertset @stmt = ''; #--== initialize the insert statement to blankset @cols = replace(replace(replace(json_keys(@jdat),'"','`'),'[','('),']',')'); #--== Column clause of insertset @vals = 'values ('; #--== initialize values clauseset @imax = json_length(@jdat); #--== loop as long as index is less than this valueset @i=0; # initialize index to begin from zero (json arrays start from zero)#--== loop through json values to get values clauseREPEATset @vals=concat(@vals,json_extract(@jdat,concat('$.',json_extract(json_keys(@jdat),concat('$[',@i,']'))))); #--== append value to values clause set @vals=concat(@vals,if(@i+1 < @imax,',',')')); #--== append values delimiter if this will execute again, else this is the last iteration so append values clause closure set @i = @i + 1;  UNTIL @i >= @imax END REPEAT;  set @stmt=concat('insert into ', tbl,' ', @cols,' ',@vals); #--== prepare the SQL  PREPARE stmt1 FROM @stmt;  set @rt=concat(@rt, @stmt,'; '); #--== run the prepared statement  EXECUTE stmt1;  DEALLOCATE PREPARE stmt1;  set @r = @r + 1;UNTIL @r >= @recs END REPEAT;END
Tested this out last night and it works great, I think i will do an update procedure too.

Comments

Post a Comment

Popular posts from this blog

Creating HTML Tables from SQL

Image
If you are like me you have probably come across this scenario You have wasted your precious time developing a super nice and complex select query to fetch your data from an RDBMS, but you have been doing this in a database client and your SQL is working pretty well. Cute, now you need your application like PHP to run this SQL and produce and HTML table that you should echo to your user. Great! Just when you thought the codding job was done now you need to start referencing php syntax aswell and this code takes you all morning to write since you keep confusing SQL syntax and PHP syntax 😠😠 Well you can get on the internet of things and use a php code like this one here  by David Walsh, or if you are crazy like me you can make your sql write the HTML code for you so you just simply echo the result to your browser. Lets begin: 1. The Data Set: we first of all need to get the data set that we want to convert into an HTML table. This is simple enough we just run our...
Read more

SQL Pivot Tables

Image
So i am a data analyst and my job in what i would like to call data mining exercises entails cross referencing large data sets that have little to no structured relationships. So i find myself writing huge complex select queries that at some point have to pivot the data around just to prepare it to be cross referenced with another complex query. Lucky for me its not much of a hustle when working with Oracle and Microsoft SQL server. The moment we start talking about MySQL, boy oh boy... that's a totally different story. There are workarounds to achieve this. One famous solution on the internet goes like this: Assuming we have a table that keeps track of student test scores like the one shown in the figure to the left. Lets also assume that we want to pivot this data by year. Thus our rows will have the student name and subject, our columns would be the respective year and the data cells would contain the average score.  The solution is to create computed column...
Read more