Learning MySQL - JSON in MySQL

so next up in our MySQL series is the JSON data type so if you've worked with or if you've worked with any other document database system you're familiar with JSON and working with JSON documents that's what the term document usually refers to as a JSON object so we can actually now as of version 5.7 dot eight of MySQL we can actually save JSON natively inside of the database and there's a whole collection of methods so there's the data type and then there's the JSON function so I'm gonna leave links to both of these pages in the description down below I'm going to be working with a table that I've already got set up so the people table I'm going to be creating a new column in here and showing you how to work with JSON and use some of those functions if you haven't been following along or if you just want to make sure that you've got the most up-to-date version of this table inside your database I have a sequel file here saved as a code just the link is down below in the description as well so you can copy and paste from here into the sequel tab or you can download this expand it get the sequel file from that and then you can go to your database and click on the import tab to import that sequel file and make sure that you've got the people table okay so once we have this people table we're just going to be working with a few of these columns person ID first name last name and we're gonna create a brand new one called person data and inside of that column we're gonna be saving some JSON data now if you're not really familiar with JSON I'll also provide a link here json org is the website for json if you want to learn exactly how json works and what's involved in a nutshell json is arrays objects strings numbers boolean x' and the value null it is supposed to be this transportable data format so you can write everything out as a string and then transport it from one language to another from one server to another from one computer to another and all the different programming languages have adopted JSON as this wonderful interchangeable format or exchangeable format so as long as you're following the rules of creating JSON it doesn't matter if you've got it in a database or using it with JavaScript or Java or Kotlin or c-sharp or something else you should be able to work with the raw JSON data and each language will have its own collection of methods that you can use one of the most important being being able to take it from an object and turn it into a string or from a string and turning it into an object or an array okay so that's JSON and a nutshell we want to create a column or we can save data that is JSON and keep it as JSON data instead of just having this string so let's do an ultra table command we're gonna be altering our people table and we want to add a column we're gonna call ours person data there we go person data and the data type is JSON that's it so we need to do so we will run that there we go now in our table we have this column which is allowed to be null the only valid default value you can have for a JSON column is null so you can't say that it's gonna have some other default object that's going to be put inside there knows the only thing that you are allowed to put in there as a default so we have our type is JSON we have our column great now let's look at actually putting some data into there now just to save us a moment I copied and pasted it in the basic command so we're inserting into people the three columns first name last name person data person ID is an auto increment column so that's automatically going to get the number account type has a default value date of birth has a default and these three are allowed to be null so inside this third one person data this is where we put our JSON objects now we want to put single quotes around it and then we're because we're using double quotes inside the JSON data itself so we're gonna create an object inside each one it's gonna have a value let's call it identity and he's gonna be Superman and the same property identity here is going to be Batman and for the final one we've got the Hulk so you can see that I'm using double quotes around each of the keys and each of the values and I'm using single quotes around the whole object if this were an array it be the same thing or if it were a string it'd be the same thing our JSON is allowed to be object array string number date boolean or null if we are putting values of null true or false so no true or false if we're putting those into our JSON we have to make sure that those are lowercase it is case sensitive for the JSON when we're putting it inside of there MySQL if I were writing a command like a select command or something I can write this like this like this like this it doesn't care it will work either way it's case insensitive but for JSON it is case sensitive it must be all lowercase for those okay so we have our insert statement we're going to run this it's going to insert those three things they're all going to have the same property identity so well click go there we go so says that inserted three rows now we'll come over here we'll browse take a look at this and there we go you can see inside the person data there's our JSON objects now it it really looks like a string if you're reading it inside of here but those are actual JSON objects that's the string representation of them that we're seeing there so back to our sequel tab let's take a look at some of the other things that we can do with this okay again quickly I just copied and pasted in here so I could let you see it while I talk so person data that's going to be the raw JSON data it'll be written out here as a string now there's this cool little character here there is a method called JSON extract this is going to allow us to extract something from the JSON so we say all right what's the object that you were going to be pulling from if you're doing a select statement from a column you can do that you can say this is the column that I'm extracting it from and the second part is the path so the path is inside your object what's the name of the property well all of these paths we would write them written inside of single quotes like this dollar sign dot so this dollar sign represents the document what's the name of the property well I wanted to get the property called identity like that so this line right here this is the equivalent of what we've done right here this single arrow so a hyphen and then the single arrow single greater than sign then inside of quotation marks single or double here and our sequel it doesn't matter inside the method we want to use single quotes but up here doesn't matter dollar sign that's our document which is the person data object we want to get the property called identity so that's going to bring back for us the value of identity here I'm doing with two angle brackets so let's run this and see what the difference is all right so I'll expand that so we can see it first and data there we have it as the string representation of the whole object here's the one with the single angle bracket right here this column and you can see that it remains with the quotation marks around the value those quotation marks get left if you do it this way that's the result of the JSON extract method if you do the JSON extract this is the value that you get there's another method called JSON unquote and if you wrap that around the extract you get the equivalent of this with a two angle bracket which I think is much easier to write then doing JSON unquote open parenthesis JSON extract name of the column comma whatever the path is closed parenthesis curls parenthesis and then your alias for the column it's just much easier to do it this way this is a quick shorthand which works really really well okay so this is how you can extract this now it doesn't have to be just inside the Select Clause you can also do this inside the where Clause so we can say where well let's do the person data with two dollar sign dot identity equals and what's the value well it's one of these so let's do Superman there we are so this is going to retrieve and filter the results so I only get one row back and because I use the double here it means that I can just put the string like this if I'd use the single it means I'm getting the unquoted value back and then I have to do this I have to write the single quotes around the double quotes which again pain-in-the-ass to do so don't bother with that just use the one with the double angle brackets unless you've got some special use case just do it this way and there is there's the one row that matched so we have this one value where this matched the value inside of person data's identity Pete okay there's a handful of other methods that we can use as well well there's actually quite a few but I'm not going to go through all of the methods here just a few of the quick and easy ones you can if you ever get a series of values and you want to take them from those values and turn them into actual JSON values this is how you do it JSON array we can provide a series of values so if I've got a in my PHP code for example let's say I've got a series of variables that I want to feed into here without having to turn the variables into the JSON within my PHP code I can just pass them in like this as arguments and then in my query I'm gonna say JSON array and then give them the value so one true some string and some date 2009 Oh 9 - 12 extra 0 there so that will give me an actual JSON array JSON object same sort of idea except with objects you've got keys and values so let's say I've got the key a which is set to 1 I've got the key B which is set to Steve I've got the key C which is set to true and D which is null there we are then if you want to find out what the oh my my error that I've got right here looking at this I rigged out as I would with JSON just purely out of habit so for the JSON object what you do is here's the key here's the value here's the key here's the value here's the key here's the value here's the key here's the value like that so it's just a comma-separated list of key value key value key value key value as many as you have like that and JSON type we're gonna feed in a column name the person data and it's going to look at that and it's gonna tell me what JSON type that is and we should get something that says object for this so we'll run that okay so JSON array you can see it turned those values that I provided into an array of the values so there's the number there's the boolean there's a string there's a date all of them are inside of the square brackets for the array for the object we got key value key value key value key value note the lower case for the true and the null and then JSON type this one was null that was the default value and then we've got object person data all of these were objects they weren't arrays they weren't scalar values like strings or numbers of dates they were objects all right now sometimes let's say we're going to select just person data we'll simplify this okay all I'm doing is I'm selecting that one column from the table people but if I run this as is this is what I get and you can imagine that if you have a large object with a lot of data inside of it you're gonna get a fairly complex thing and it's gonna be all crushed together hard to read not a lot of spacing so if you want to make it a little bit nicer there is a JSON pretty method this is a very useful one to have now this is going to format this will just call it formed there we are now I've only got one property in one value but you can see it's adding the carriage returns and it would also add indenting as the structure became more and more complex it pretties the JSON forests for display purposes quite useful okay and in those functions there's quite a few others here you do have to be careful because this was the first version there was a few methods that were put in that have since been deprecated JSON append now is replaced with two different versions there's the JSON array append if you're working with arrays there's an append there's an insert JSON merge another one which was dropped very quickly has been replaced by these two JSON merge patch and preserve so I'll show you a quick example with those in just a moment remove replace you want to search you want to find out if it contains something work if there's a property or if there's a value somewhere in your JSON these are great methods to use I really do encourage you to spend a little bit of time and experiment with some of these methods I'm just going to do this one final example for you with merge patch and preserve to explain the difference this is when you're taking some JSON and you're putting new JSON into that column how would you modify it so let's say I've already got a property called name in there and I'm putting a new name property do I want to keep both values or do I want to replace the old one with the new one and that's kind of the difference between patch and preserve so let's take a look at those ones for an example all right these are the examples I'm going to use so let's say this first value right here you could put the column name here so let's just pretend that this is what's in the column right now and I'm going to be putting in this value and if I call patch or preserve it's gonna be a different behavior so if this is what's in the original column value right now and this is what I'm passing into there and I'm using this method to determine what I'm going to do patch will replace so it takes the new value and replaces the old one it patches the old one with the new value preserve means keep the old one and bring the new one in so I'm going to run this and we can see exactly how they work so with patch both of them the same it's replacing Steve with Steve so there's the result with preserve it didn't replace Steve Oh Steve it added Steve - Steve and now this is an array so the key name points to an array instead of just a single string and then if the new thing comes in it's the same you can see Dave replaced Steve inside of here this is the result and with preserve we've got Steve and Dave both values are now inside there all right so that's a very quick intro I know I've been talking for quite a while but that really is just sort of the surface of what you can do with JSON when working with MySQL I do strongly encourage you to go and take a look and experiment with some of these functions that you've got in JSON some amazing stuff you can do with JSON which really helps to offload some of the work that you would be doing in your server-side programming just think about putting these into a functions or store procedures you can really save yourself some time even with triggers you can add JSON functions into those triggers all right if you have any questions feel free to leave them in the comments check out all the links that I've got down below and as always thanks for watching

Channel: Steve Griffith - Prof3ssorSt3v3

Views: 31,424

Rating: 4.978261 out of 5

Keywords: MAD9022, web development, mad9124, mad9023, mad9135, mad9145, mysql, sql, learn mysql, learning mysql, mysql tutorial, sql tutorial, intro to mysql, intro to sql, sql for beginners, mysql for beginner

Id: mL7xnMZNYXM

Length: 19min 11sec (1151 seconds)

Published: Wed Jun 12 2019