 Hi everyone. Thanks for staying. I know that it's been an all day. So, I'm Chaitra. I'm a developer with MySQL Optimicity. And today I'll be talking about how one can use SQL with JSON. That's the same hardware statement. Okay, so that's the agenda for today. So, I'll be briefly talking about the JSON format. Today we'll move on to how JSON is supported in MySQL. That's followed by the new JSON functions, which can be used to convert JSON data to relational and back to JSON. So, I guess most of us know what JSON stands for. It's the JavaScript object rotation. And the primary structural unit of a JSON is an object. I have an example here. So, the object, JSON object has a list of key value pairs. And the keys and the values are separated by colon. And the key is usually a string. And then the values can be a string or a number or another JSON object or a JSON array. So, the second structure is the JSON array. So, JSON array has a list of values separated by commas. So, as I said, each of these values can be in turn a JSON object or a JSON array. So, this is called nested values. So, I have an example here. The first one is the JSON array having a JSON object as value. And the second one is a JSON object having a JSON array as value. So, these are called nested values. We'll look into how we can use SQL operations on these data. So, we'll look into how, what kind of support we have in MySQL for handling JSON data. So, we introduced JSON feature in MySQL 5.7. There were three parts to it. The first is the JSON data type. And the second is a set of JSON functions which can be used to create, modify and query the data. And the third is a generated column feature. So, the generated column helps you to index particular key in JSON column. So, first I'll briefly look at the JSON data type. So, why a separate data type? You can ask and store JSON data in a text column. But MySQL has its own data type, JSON. And this is stored internally as a block. So, what advantage do we get having this separate data type? The first is the automatic validation of your JSON data. So, when you insert a data, if the JSON data is invalid, it raises an error. So, at insertion itself, you will get to know whether your JSON, it's a valid JSON object or a JSON array. Then the second advantage is that since it is internally stored as block, we have easy access to the keys and the value members. So, you will see the difference between storing it as a text and storing it as a JSON. So, I have two examples here. We are creating two tables, one with a JSON object. We have inserted a JSON object, another one as a JSON array. So, the first one is information about people which has ID and the name. So, I insert it like this, I mean any SQL statement. And then you do a select start from people, you get a JSON object. Then you have a table called jobs, which has job info, which is a JSON column and it has a JSON array. So, you do a select start from jobs and then you get the JSON array. So, that's about the JSON data type. So, we look into the JSON functions now. So, there are plenty of functions. I'll just touch base on a couple of functions to create and modify and vary the JSON. As I, as you see in the previous slide, you can actually insert JSON data directly like this. Or you can use the JSON object and JSON array functions. So, the JSON object takes a list of key value words, it validates the data and then it creates a JSON object for you. And the signal with JSON array takes a list of values and then it creates, so that's for creation. So, once you have the data in a real world, the data keeps changing. So, what happens if I have, okay, the old data has only ID and name, but the new data also has an address. So, we have like this, say for example, the new data that came in has additional member called address. Now, we want to insert this member into the old data. So, the existing old JSON data doesn't have this address member. So, what do you do? You have a function to help you do that. So, you update your table with JSON insert function. It takes three arguments basically. The first one being the JSON document which needs to be updated. And the second one is the part which needs to be inserted. So, what JSON insert does is it searches for all the documents. If the part that you specify here is not found, then it is going to insert the part for you and then it will initialize the value with whatever you have given here. So, that's what JSON insert does. So, now if we want to update this address to something that is not null basically. So, you might want to use JSON replace. So, what JSON replace does is if the part exists. So, if this part inside your document exists, then it is going to update with a new value. So, you see that both the documents have address with new log. So, the next one is, so once you have added the data, updated the data, you want to delete the data as well. So, you can use JSON remove which actually removes this part from this document. So, you have now gone back to the old format where you have the ID and A. You do not have address anymore. So, this can be used and we have couple of more functions like JSON set which does both JSON insert and JSON replace together. So, that is I mean plenty of more functions are there. So, we can use based on whatever is the need for you. So, I will talk about the path once I go to the JSON table. So, once you have created the data, modified the data, now we want to query the data. So, there are again not many number of functions to do the same. So, I have concentrated on three of them here. So, the first one is JSON extract function. What does it do? It extracts a value for a particular path inside your document. So, here I am trying to extract this path in your JSON document info. I want to look into a member named name and then you get back the value. So, in this query I am trying to compare that value with J. If it is J, if it matches then I will get a one. If it does not match because the second document did not have J. So, you get a C. So, JSON extract can also be used. There is a shorthand for the same for your readability purpose. You can use this as well, the arrow mark. So, now on the left hand side is a column, JSON column on the right hand side is the path. And then you can just do selecting for. This can be used in your ware process to filter out or you know get the data that is required for your operation. Okay, the next one is JSON countries. So, JSON countries actually tells whether a particular value is found inside a document. So, you have a JSON array here developer and support engineer which was there in our job info. So, you get a one for that. So, this returns a true or false whether a particular value is found in a JSON document or not. So, the next is a JSON search. JSON search helps you to find a path for a particular value. So, you are searching for a value, a person named Jane in a JSON document. It is going to return the path for you. You see that the first row, first document had Jane. So, you will get the path for that and the next document did not have it. So, you get one. You have a question? Yes. Will that search every path in that case? Yes. So, I will get that. So, you have the second document which says one or all. So, one means return immediately after you find the first path to it. So, the all means it is going to search every single path and then give you all the paths to go. So, along with these functions we have plenty more. I have listed down. So, there are all these functions that you can take advantage of. So, along with varying, modifying, creation and everything you also have utility functions like JSON pretty which will pretty print your JSON document. Then it is there is JSON valid which is going to tell you whether it is a valid JSON document or not so on and so forth. So, I mean it is pretty extensive for you to play around and see what suits you best. So, that is about the 5.7 support. So, now we will move on to what is new in 8.0. The most powerful JSON function which will help you to convert JSON data into relational and then you know thereby take advantage of all the SQL operations. So, that is the JSON table function. So, the JSON table function is the first table function. It is not says it does not return a scalar set. It actually returns a result set which can be used as a table. So, let us see let us go through this example. So, I have this data about people which has names and addresses and everything. Now, I want to visualize this into a relational table like this which has two columns name and address and then I have the names and then each row being created for every name that is found. So, now what do you get by doing this? You can start doing all the relational operations on I mean the SQL operations on this. So, how do you do this? It is pretty simple once you get the hang of it. So, you just do a select star from G1 the table where the JSON documents are present and then write the emote the JSON table function. So, JSON table function first you need to mention from where the data is coming from. So, that is your JSON column. So, JSON column then you say that if you know the path already. So, in this case I know that it is there I want to extract information from people data. So, I will give the path. So, I will just talk a bit more about the path here. The daughter symbol of this example says that that is the root that is where I am starting to look for. And then a dot means that it is an object. So, it will be followed by a key name. So, that is your member inside your JSON column. I am looking for a member named people. And then inside this array everything I want you to search for all the data inside this. So, you can mention the path or if you do not know the path you can just say start here itself. So, then that is followed by as is as you see here we have two dimensions here created from this particular data. One is the columns and the second is the rows. So, how do you tell JSON table to create that? So, you mention the columns and their paths from where we extract from. So, I am mentioning a column name and then the what is the type of it and then you give the path inside this document. This is the path I am looking for this is where you want to get the data from. So, then you have the address and then you give the path for the address that is followed by your table name. So, now once you give the table name values you can use that and then start using all the scale operations. So, yes. What happens if you get the data that is wrong or you don't have a name that is wrong enough? This one? Yeah. So, you have particular set of data types that are being supported. I think it is going to error out if there is no supported data type. Say for example, along with the standard data types we also support date, time and things like that. So, anything that is supported it is going to be taken otherwise. So, that is about it. So, what happens now? So, you can start doing all the relational operations that you can leverage. So, here you see that we can start using this data and then say where people dot name like John. So, you will get all the rows with starting name John. So, what we had previously was three rows now it is filtered and you get this. So, this is what you can do once you convert your JSON data into relational data. So, not all the time your JSON data is simple and straightforward. You will have all this listed arrays, listed paths and you want to extract this listed data as well into relational table. So, I have this data of father, mother and then the marriage data and then there is another set of data regarding the children and then age. So, what I want to visualize is this where each child inside this has a row of its own. So, what do you do there? Because you want the father data as well and you want the children's data as well. So, what do you do there? So, we have something called listed paths. So, in this particular table you also see that there is additional information which is not present in the JSON document. So, we have something called the IDs generated for each father and then there is ID generated for each of the children as well. So, how do you do that? So, I have then followed by the path inside this family. So, here you want to look into everything then you specify the columns. So, the first one is ID for ordinality. So, once you mention this then there is an ID that is being generated for every row that is extracted from here. Then you mention the path for the father. So, you get this information. Then you also say whether the marriage status. So, if there is a marriage date then that means that the person is married. So, you indicate it by one. So, for the second name you do not have a marriage date. So, you get a zero here. So, you can use this exist path. Then you have mentioned the listed path here. So, which is like go into inside this you want to get into the children's data and then create columns for each of those. So, the child ID which is the ID for ordinality. Then you have the child's name. Then you have the age. So, what can you do now with this data? You are getting leveraging to use all the SQL operations. So, here I am trying to aggregate the age of the children. So, you can do that. So, you are doing the number of children found and then you have the average age. So, that is what JSON table can do for you. So, okay. So, now you have the data in relation to the relational format. How do you get back to the JSON? So, you want to store the data back. So, you have something called JSON aggregations that is new in age. And it is I think back for data 57 as well. So, we have two functions, two aggregation functions. One is the JSON array and another is the JSON object. So, the array actually creates an array for you aggregates all the JSON objects and then creates an array for you and then the object that creates the objects aggregation. So, you can use your buy and then use the aggregations. So, using this aggregation function, you can put back the JSON data, I mean the relational data back to the JSON format. So, I have here a query which does the same. So, you see that the third name that we had originally is missing here. So, you have only the John Smith and John Johnson. So, how do you do that? You have your old JSON table function here. And then you say that okay, I want to create an array of this using the name and address. And then I want to put it to the equal state array. So, it is as simple as that. So, I will show you how it can be done with the nested arrays as well. See here. So, what we have here is along with the original data, you also have the average age that we just calculated and then the number of children as well. So, how do you do that? You have your JSON table function to extract the data in relation format and now you use average and count and then you got the age and the count and then you merge it to the family data. So, you use JSON merge patch which will merge it to the old existing family data and then you get this family object which now has along with the children name, father, mother and management. It also has aggregated data like the average age and the children and so on and so forth. So, that is about my presentation. That is all I had. So, if you have any questions, I am ready to take. Can you turn to the page time? So, from here the bottom part. Yes. So, the first one has address now and the second one has address New York. Is it possible to have the first item to have, not to have address at all? You did not have address before. I am trying to update it to have address here. So, what do you want to do now? You want to delete it? Delete for item number one, but not for item number two. Yes. You can specify JSON remove and then you have a where clause. You have a where and then specify which document to do it. So, where and you can query, right? You can use this. So, where info dot the name equal to say J, you do not want it. You do not want it only for J, isn't it? This is the first document. So, you can use JSON remove and then specify your where clause here, where it matches to only the first document. You can do that. So, what do you want to do? No, actually I think. So, JSON extract is not returning 1 and 0. JSON extract is actually returning a value found in that part and I have an equality clause here with J. So, this one returns J. J for the first row and then it just. And this one just returns true or false based on the value of this value present. You find out which objects are present on the previous slide of the address. Here? Yes. You find out the first row is missing the address. So, you want to validate. If you want to validate. For example, then I can use JSON search which can give you the parts. If the part returns null, that means that that part is not valid. I will be available I guess till 6 o'clock.