Creating JSON Structure (Part-2):
In our previous tutorial, we discussed creating a simple JSON file using sample data sets.
We also came to know the details about the usage of a json.Net framework for serializing data into JSON format. We leveraged C# and Visual Studio to create a simple console application to use data and key values provided by us and then serialized those key values into JSON structure. Now, let’s see what this tutorial will cover as we move ahead.
In this tutorial, we will discuss the ways to create more complex JSON structure. We will create arrays with multiple sets of data and also look into the ways to create nested structure in JSON.
Most of the JSON files used for data transmission between various systems contain more complex data structures. Thereby, learning about the complex JSON structure will help you in creating test data based on the JSON schema requirements.
Table of Contents:
Writing the Code
We will be referencing our previous post in this tutorial. Hence I would suggest everyone to go through the earlier tutorial first, before proceeding onto this one.
We will use the same JSON data that we used in our previous tutorial. We will also follow-up on the same set of code that we wrote for our previous JSON example.
Let’s start now.!!
Adding Array with Multiple Data into JSON
To add an array to the JSON, let us add an array key to our previous data set.
Our data set will become as shown below:
Adding an array object to the JSON is similar to that of adding any other key values. Values can be assigned directly at the time of declaration of the array itself. Once the values have been assigned to an array, then the JSON newtonsoft will serialize the objects into key-value pairs.
To add the Array in the JSON, we will declare it in the “Employee” class itself. (Please refer to our previous tutorial for details)
namespace jsonCreate { class Employee { public string FirstName = "Sam"; public string LastName = "Jackson"; public int employeeID = 5698523; public string Designation = "Manager"; public string[] KnownLanguages = { "C#", "Java", "Perl" }; } }
As you can see we have directly declared the Array in the Employee class. Do not make any changes in the main method. Creating a different class for JSON object will help us in keeping objects organized.
Whenever there are changes in the JSON structure or when you want to add another set of data, all you need to do is to make the changes in that particular class file only rather than making changes all over the project. This means that your Main method will remain the same most of the time and the changes will only happen inside the classes.
Let’s execute the program and create our JSON file with the array.
Now copy the content and paste here to validate if the created JSON is valid or not.
Click on the Validate JSON button to validate it. The JSON key-value pairs will be arranged and validation will be performed on the given data set.
Performing Operations on Data before Assigning it to JSON keys
Let’s assume that we have some data and we want to perform some operation on that data before assigning it as values to the JSON keys.
In such a case, how will we do that?
For Example: Let’s say that the employee ID that we passed into the JSON is made of two parts, first three letters denote the location code and the last 4 digits denote the employee number. Concatenating both will give us the employee ID of an employee.
In case if we receive the Location code and Employee Number separately, then we will have to concatenate them together to form an employee ID. Only then we can pass it through the JSON.
In order to overcome these type of scenarios, we need to perform operations on the data before we assign it to a key.
Let’s have a look at how this can be done.
Let’s go back to our employee class and create another class, inside which we will perform all the operations.
Here we will create another class to contain and perform the operations on the employee data.
Let’s create a new class “EmployeeData”.
The class has been created, and now let’s create a method with public access specifier and return type as our class “Employee”. We have provided the method name as “EmployeeDataOps”. However, you can provide your own name. In order to make this more simpler, I am not passing any parameter within this method.
As we described the return type as a class, we will have to return an instance of the Employee class. To do that we will create a class object inside the method.
Here, we have created an object for the Employee class with the name EmpObj and at the end of the method, we have returned the object.
Let’s define two integers inside the EmployeeData class representing the Full location code and the employee number. Once declared we will use it to perform operations and then assign values to the respective keys.
int locationCode = 569; int employeeNumber = 8523;
Now, as we have the location code and the employee number, we can perform operations on them to find the employee ID. To do this we will write a simple code to concatenate both the integers.
int empID = int.Parse(locationCode.ToString() + employeeNumber.ToString());
This will simply concatenate both the integers forming the employee ID. We have stored the employee ID under the variable “empID”, and now we will pass this variable to “employeeID” in EmpObj.
Employee EmpObj = new Employee(); EmpObj.employeeID = empID; return EmpObj;
The whole sample code will look as shown below:
Did you notice that we have removed the value that we earlier assigned to the employeeID variable in the Employee class? We did this as we are returning the value from EmployeeDataOps() method. Hence, the data to the variables will be fed from this method itself. This removes the necessity of directly declaring values.
As we are done with the method now, we will need to add an instance of this method to the main method so that this method can be called.
To do this we will create another class object in the main method for “EmployeeData” class.
EmployeeData empData = new EmployeeData();
Once we have created a class object, we will now assign the method inside this class to the Emp object that we created earlier for the employee class.
emp = empData.EmployeeDataOps();
Finally, the code inside the main method will resemble like this:
Let’s put some test data:
Location Code = 123
Employee Number = 9874
We will put this data into the code and with the final changes in the main method. We have now completed our code. Now, let us run the code and validate our JSON.
This is the JSON that was created:
As you can see, the new concatenated value for the employee ID has been entered into the JSON value.
Let’s copy and paste this JSON here to validate its structure. Put the text into the JSON lint site.
Use the validate button to validate the structure as shown below:
Creating a Nested JSON Structure
The example that we discussed until now uses mainly string and numeric values inside an array or object. But JSON can also be used to express an entire JSON object by using the same notion as an array element. The object members inside the list can use their own objects and array keys.
In Introduction to JSON which is one of our earlier tutorials, we had a first look at how a nested JSON looks like. In that tutorial, we assume that the employee also has a Car and the JSON should contain all the details about the employee car also.
So the JSON structure that we get at the end will be similar to this:
Here, we have the employee JSON with all the data, then we also have a Car JSON object nested inside the employee JSON. Car object has its own set of keys and values.
Let’s try to create this JSON programmatically.
For this, we will start with the same JSON that we created in our previous tutorial. To make it easier we will create the new JSON object (i.e. Car JSON) in a new class. We will add a new class car and will add all the objects inside this class with a public access specifier.
Now, we can either add the value directly over here or we can write a new class and create a custom method with a class object return type to assign the values similar to what we did in the previous tutorial. For the sake of convenience, we will assign the value directly to the key variables.
Now we have created a new class with the objects and values. In the next step, we will add this to the Employee JSON structure, so that when the JSON serialization happens, the key-values from the Car class should also get serialized along with the employee class as nested JSON.
In order to do that, first, we will need to add a class type object car in the Employee class. This object will be used to store the values present in the Car class.
As shown above, we have assigned the new variable with data type as Car class. Now let’s go to the EmployeeDataOps() method that we created inside the EmployeeData class. We will write the code to call the variables and values from the Car class.
First, let’s create a class object for car class:
Car carObj = new Car();
This object will contain all the data from the car class. Once we have declared all the data from the car class into this object the next step will be to assign this data (data contained inside the car object) to the car variable that we created for holding this data.
In order to do this, we will simply use the Employee object that we created to access the car variable. And then we can directly assign the car object with the data to the to the car variable.
EmpObj.car = carObj;
That’s it. We have created a variable in one class then created another object to access the value from another class, then we assigned the value to the first variable.
Now, let us run our program and see if it can create the desired JSON.
As shown above, we see that a car json key has been created and it contains all the data that we entered in the Car class as the key and values. Now, we will again copy the JSON content and navigate here to validate the JSON.
Just copy all the JSON content into the text area and click on the “Validate JSON” button.
So, the JSONlint site has arranged our data and validated it perfectly. We can see that the “car” object has been arranged in the JSON structure as we required. Using the same process, you can create multiple levels of nested JSON. Just keep on adding the JSON object to the class and assign its value to a class variable.
As you can see we don’t even have to change any code in our main method.
Using an Excel Sheet as Data Source for JSON
In our previous tutorials, we discussed several ways to create different structures of JSON. But there was a big issue with all our structures, we were always hard coding the values for the keys.
In this tutorial, we will discuss the ways through which we can use an excel sheet to feed the data to the JSON keys. I would recommend you to go through all the tutorials that we discussed earlier before proceeding with this one as we will be discussing the same code that we wrote in the previous tutorials.
Going on a step by step basis will help you in understanding the whole concept in a better way.
I hope you guys have understood the basic code to create a JSON, in this part we will take forward the same code structure.
First, let’s create an excel file with JSON data.
We have created an employeeData.xlsx file with the following details.
Before we start writing the code for extracting values from the excel, we will need to add an assembly reference to our project. To access office object, C# offers us the Microsoft Office Interop. These are quite helpful in providing easy access to the office objects.
As we are using excel in this project, we will use Microsoft Office Interop Excel assembly reference.
To install it, right click on the References in your solution explorer and then select Manage NuGet Packages. Write Microsoft Office Interop Excel in the search bar and the search result will display the required package.
Once you get Microsoft Office Interop Excel click on the Install button to install it.
Once the installation is complete, you can see that the Microsoft Office Interop Excel has been added to the list of assembly references in the project.
To start with, let’s first assign the different excel elements.
Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Here, we have assigned values to the Excel application, excel workbook and excel worksheet. Once these are defined, we will be using these in our next steps to access the values in the excel sheet.
What are the steps that we generally follow, if we want to fetch a value from an excel sheet?
First, we access the excel application, then we open the excel workbook and the excel worksheet and later we locate the element based on its row and column values. We are going to do something similar here.
This code will access the excel application.
xlApp = new Microsoft.Office.Interop.Excel.Application();
This code will open up the workbook with the given name present at the given location.
xlWorkBook = xlApp.Workbooks.Open(@"D:\json\ employeeData.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Now, we will write a code to access the particular worksheet inside the workbook. We have a worksheet named “Sheet1” ( the default name in the excel sheet)
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets["Sheet1"];
As we have accessed the WorkSheet, now the next step will be to find the correct column and the correct data. First, we will search for a column with the “Key”.
For Example, First, let’s search for the column with value as “FirstName”. Once we find the value, we will extract the column number. Then as we know the first row contains the heading and the second row contains our data, so, we will use the column number and the row number to extract the exact data.
This will store the column number for the FirstName in the variable.
var colmnVal = xlWorkSheet.Columns.Find("FirstName").Cells.Column;
Now, we will use the column number of the FirstName to extract the value from the cell below it. As the know, the value method will only return string type, so we will store this in a string variable.
string frstName = xlWorkSheet.Cells[2, colmnVal].Text.ToString();
Now, we have the value of the First Name stored in the variable. So, we will use the employee object that we defined in our method to assign the value.
Please remove all the values that you have assigned/hardcoded in the Employee class as we will be returning the values using our method.
But there is one issue with this, the “.Text” function always returns a string value. So, if we want to extract the value of an employee ID which is an integer, it will also be extracted as a string. So, we will have to convert this string into an integer before assigning it to the JSON object. To do that we will directly parse the value to an integer.
So, the code for employeeID will look as shown below:
var colmnEmpID = xlWorkSheet.Columns.Find("employeeID").Cells.Column; string emplyID = xlWorkSheet.Cells[2, colmnEmpID].Text.ToString(); int emplyIDint = Int32.Parse(emplyID);
At the end, we will parse the string value to an integer as our JSON recognizes employeeID as an integer value.
So, the overall code for fetching data for all 4columns will look as shown below:
Now, all we need to do is to assign the variables that we created with the data from the excel sheet to the employee objects.
Everything is set, we will now build the project. Once the build is complete we will execute the program to generate the JSON.
Following JSON will be generated:
Now, let’s compare the data from the JSON with that in the excel sheet.
As shown above, the JSON data matches the data in all 4 columns of the excel sheet. Let’s validate the JSON that our program has generated. To do that we will again visit here. Just copy all the JSON content into the text area and click on the “Validate JSON” button.
Hurray! We have created a valid JSON using the data from the excel.
Exercise for you:
Create a three-level nested JSON. Create a parent JSON Company and nest the employee JSON that we created earlier along with the car JSON.
Conclusion
We have now reached the end of our tutorial. It has been a long tutorial but we learned several things. We learned how to create a simple JSON using c# programming and the benefits of categorizing different sets of JSON data into a different class. We also used our programming skills to add arrays and even another JSON structure inside a parent JSON.
Lastly, we worked on the ways to fetch data from another data source to feed the values to the JSON keys.
Hope you all enjoyed the whole series of JSON tutorials so far.
Tutorial #4: Using JSON for Interface Testing