 Hey guys, welcome to SS1 Tech, so still decide and today we are going to see how we can load the data from the CSV file to the JSON with the nested hierarchy. So it will be the formatted JSON that we want to generate from the CSV file. So before going forward, if you haven't watched the last video of this video series, so I would strongly recommend to watch that video because this is the continuation of that video. On that video we have discussed how we can load the data into the CSV file from the JSON with the nested hierarchy. So here we are going to do the reverse. So let me quickly see the source and the formatted JSON that we want to generate from that CSV file. So here our source is available under the output folder of the blob storage and here the sales underscore json.csv file. So it is having the total six column with the sales order ID, sales order date, country, item, quantity and value. So we want to generate the formatted JSON by using this CSV file. So how we can do that, we will see in this video what we want to see in the formatted JSON that should be like sales order ID will be the first column. Then will be the sales order date, then the sales country. So these three columns should be available like this and after that item, quantity and value. So these three will be into the nested JSON. So as we can see for the sales, it is having total three items. So that's why we can see total three like this. So how we can do that in the Azure data factory. So we cannot use the copy data activity to convert the CSV file to the JSON file because it will not be formatted like this. That will be having sales order ID, sales order date, country, item, quantity and value. Everything will be in sequence and that will not be having the nested JSON that we are seeing here. So how we can do that? Let me go into the browser and we will try to implement this in practical. So let me go into the Azure data factory. Here let me try to add a new data flow. And this time our source file is available at the Azure Blob Storage. So let me try to create a new data set for the same. Let me click on name. As your Blob Storage click on continue. File that is the delimited text click on continue. Here let me try to select the link service that we have already created. Let me browse this, go to the output folder and under the output folder we are having the file that is sales underscore JSON dot CSV. So this is the file we have created in the last video. So let me click on OK first row as header. So that is true. Let me click on OK. So this will be going to create a new data set with the delimited text to data preview this. So it will be having all the columns like six columns and the values on that. So here as we can see we are having one one one. So we don't want to keep the values like one one one like this. We should be going to have only a single row and for the item quantity and value are repeating. So these three should be coming as an array. So how we can do that? For that first we need to create a new column and that will be the sales column and which will be having the sub columns like item quantity and value. So how we can achieve that? We need to use the derived column transformation and under the derived column transformation on this we are required to add a new column that column is the sales column. But this column will be having the sub columns with the item quantity and value. So how we can do that? We can go and open the expression builder here. So in this expression builder if we can go in the sales and click on this plus symbol here we can see the add sub column. So once we click on the add sub column then it is asking the name. So first will be the item. So we can call this column as item and the expression value. This value should be coming as an item from your source. So that is okay. Next we can add another column and this sub column the name of this will be the quantity and under the expression here we can see like item is the first one, second is the quantity. So we can specify values like item, second value will be like quantity and third value for this will be like value like this. So it should be having total three columns. So if we can go here and we can check. So once we save this then we can see total three sub columns item quantity and value. So this item value is coming from the item of your source, quantity is coming from the quantity of the source then value is coming from the value of the source. I have specified the same name so that is why it might be confusing. So let me try to add the item id here quantity value something this number. Let me try to save so we can see so item id quantity value quantity number. So these values are the column names and whatever the value that we are getting from the source will be assigning over here. Now let me try to save and finish. So this sales is having total three columns and item quantity and value. Let me go into the data preview and try to refresh. So this time we should be able to see all the records with the sales as we can see then here quantity, number and the value. So all these are here and coming properly. Next what we need to do next we are required to use the aggregate transformation because this is coming as in columns but these are not coming as an array. So how we can convert that? So for that we have to use the aggregate transformation. Under the aggregate transformation here in the group by we need to select the columns so SOID is okay the first column we want to do the grouping SO date and the last column will be the sales country. So as we can see in the JSON file SOID, SO date and country. So these three are the grouping columns and after that this will be item quantity and value is the aggregate. So if we can go on the aggregate side. So here we need to add the column which is the sales and here for converting those we need to use the collect function. So what this collect function will do collect function will be going to convert your sales column values that is the item quantity and value into the array. So let me save and finish go to the data preview and try to refresh. So this time we should be able to see the data as expected like sales order ID, date and country and after that here we can see the sales. So it is converted properly into an array. So it is having the values like sales order ID 1, 2 and 3. So these item IDs are here. So next we can directly dump into the sync. So here we can go and try to add a new sync. Let me try to add a new data set as your blob storage. Then we want to keep this into the JSON file. So we need to select the JSON click on continue. Here we can select the link service. Then we want to keep this file into the input folder. Click on OK. Everything looks OK. Click on OK. So now let me try to save all these and let me go into the input folder of the container and here let me try to remove the JSON file that we are already having that is a sales.json. Let me try to remove this file from the source. Here let me try to add a new pipeline and under this pipeline we are going to use the data flow one and try to execute this. Once it will be executed, then we should be able to see the file under this input folder and that file will be having the formatted JSON from the CSV file. So it got executed successfully. Let me go into the blob storage and here let me try to refresh it. So here we should be able to see one more file that is the sales.json file. Once we are going to see the data under this, so it should be having the formatted JSON as we have already seen there. So as we can see the sales order ID one, then the sales order date and after that here we can see the sales. So this sales is having the nested value of the item quantity and value. So here as we can see the sales order ID one, two and three as well. So it is having the formatted JSON as we were expecting. So let me try to repeat again what we have done in this video. So in the data flow, first we have added the source and after that we have added this derived column. So in this derived column transformation we have added the new column which is the sales and under the sales column we have written the expression to add the subcolumns. So under the subcolumns like the item ID, quantity and value and we are assigning the value on these column from the actual source which is the item, quantity and value. And after doing this the data which we have already seen is the string value. So we need to convert this string into the complex data type that is the array. So for that we are required to use the aggregate transformation. Under this aggregate transformation we have written all the columns. Those are not going to part of the nested sub value. So those are in the group by like the SOID, SO date and country. Whatever the value we want to keep in the nested JSON that is coming here. So that is the only sales and for this column we need to convert this into the JSON format. So we have to use the collection with that complex type of column. So that we have done and after that we are simply loading into the destination site. So this is what we have done in this video. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. See you in the next video.