Database Assignment: Implementing JSON Field in MySQL Customer Table

This assignment focuses on implementing a JSON field within a MySQL customer table. It begins by outlining the structure of the customer table and then demonstrates how to add a JSON field named 'customers_contacts' using an SQL query. The assignment then proceeds to insert three data values into this JSON field, each containing customer number, last name, and first name. Subsequently, it provides the SQL query to display the customer details that include data in the JSON field. The document also provides an overview of JSON, explaining its structure and advantages such as efficient data validation and improved query performance. It also highlights the disadvantages, including the lack of support for MySQL memory tables and slower modification speeds compared to other languages. The assignment concludes by summarizing the process of adding a JSON field, inserting data, and displaying customer details, along with a discussion of the benefits and drawbacks of using JSON in MySQL.

Documentation
Introduction:
JSON (JavaScript Object Notation) which is the lightweight format which is used to
interchange data. The JSON format are easy for the human to read, write and understand and
easy for the computer to parse them and extract data. The task of the assignment is to implement
the JSON field into the existing customer table of the classic mode database. Then insert 3 data
values into the customer table and to finally display the customer details which contains data in
the JSON field. The assignment also describes about JSON together with its significant
advantages and disadvantages (Tahaghoghi & Tahaghoghi, 2011).
Task:
Adding JSON Field to Customers Table:
The initial structure of the Customer table is given below:
A JSON field named “customers_contacts” is added to the Customer table using the following
query (Glendon, 2011):
Query:
ALTER TABLE customers ADD COLUMN customers_contacts JSON DEFAULT NULL;
Now the structure of the customer table is modified as shown below:

Inserting data into JSON Field:
The customers table’s “customers_contacts” JSON field is inserted with the following keys data:
 customerNumber
 contactLastName
 contactFirstName
The SQL query used to insert 3 values to the JSON field of the customer table is given below
Query:
update customers set
customers_contacts='{"customerNumber": 103, "contactLastName": "Schmitt",
"contactFirstName": "Carine"}'
where customerNumber=103;
update customers set
customers_contacts='{"customerNumber": 112, "contactLastName": "King",
"contactFirstName": "Jean"}'
where customerNumber=112;
update customers set
customers_contacts='{"customerNumber": 114, "contactLastName": "Ferguson",
"contactFirstName": "Peter"}'
where customerNumber=114;

Output:
Displaying Customers with “customers_contacts” column which has data:
The SQL query to display the list of rows which contains the data in “customers_contacts”
column is given below.
Query:
select * from customers where customers_contacts is not null;
Output:

JSON:
JSON (JavaScript Object Notation) (Stokes, 2018) which is the lightweight format which is used
to interchange data. The JSON format are easy for the human to read, write and understand and
easy for the computer to parse them and extract data. JSON data are built basically on two
structure namely,
1) Name/ value pair
2) Ordered list of values
In JSON Name/ value pair are defined inside curly braces ({}), with each name value pair
separated by comma.
The ordered list of values are defined inside the square brackets ([]), with each value separated
by comma.
Advantages:
Following are advantages of storing data in JSON field of MySQL
1) The documents are validated successfully before storing the data in the JSON column
2) It is easy to integrate document queries into SQL with the help of JSON
3) The performance is improved by indexing on values of data in the JSON field
4) As JSON document are stored in optimized binary format in the JSON field, it allows
faster access to object members and array elements
Disadvantages:
Following are disadvantages of storing data in JSON field of MySQL
1) JSON field does not support MySQL memory tables. In creating temporary table, it
creates disk-based table rather than memory.
2) JSON modification is slower in MySQL compared to PHP or Python.
Conclusion:
Thus, the assignment describes about the JSON with its usage together advantages and
disadvantages of using JSON field type. The application finally adds a JSON field named
customer_contacts to the customer table. Inserts 3 data into the customer table with three keys of
data namely the customer number, customer last name and customer first name. Then finally the
list of customer details which contains data in the customer_contacts field is listed.

References:
Tahaghoghi, S. & Tahaghoghi, S.M.M., (2014). Learning MySQL. Sabastopol, CA: O’Reilly
Publisher.
Glendon, M.L. (2011). Fundamentals of Database Management Systems. New York, NY: John
Wiley and Sons, Inc.
Stokes, D. (2018). MySQL and JSON: A Practical Programming Guide. Boston, MA: McGraw
Hill Professional.