Is MySQL 5.6 better than the new introduced JSON- a data type by MySQL 5.7!

Have you gone through the news shared by top app development company? If not yet, then turn your senses here to find the insights about MySQL 5.7.

 

Yes, You heard it true ! MySQL 5.7 introduces new data type in its resources we are familiar with. And Yes, it's JSON . JSON which is

popularly known as JavaScript Object Notation enable us to access JSON-encoded data where we can store JSON format data in

text, it's native support embraces us with the Auto-validation as well as storage Optimization .

 

Though MySQL is not the standalone Database offering JSON storage, infact there are many DB like MongoDB, PostgreSQL,Oracle and many

more also support JSON format .

Why JSON is the best Data Type ?

Traditional database have earlier us based table structures that stores all our data . Till the time the all your tuple records are of same shape,

have similar data type and of same quantity no issues have been raised and it works well. What if the data has frequent sets of attributes for

each row?

 

The best example to be considered to understand the scenario is online shopping portal that deals in variety of products . A Shirt is sold by

color, size while Ladies handbags do not have Size attribute while Jeans needs length to be specified. Here we conclude with either a large set

of table having empty space , as most of the attributes do not match with the product or as part of solution we can make use of EAV(Entity-

Attribute-Value) pattern but, that's quite tedious to work with.

 

Here Comes JSON : that stores data inside data( nested approach) eliminating blank space thus saving memory. JSON format is widely used

in mobile application when to dealt with calling web-services and now will make sense at database level too.With the new features of JSON

allows we cannot access only queries but can access the values nested under JSON data.

How to create JSON table and insert values into it ?

// creating table in JSON

CREATE TABLE EMPLOYEES (data JSON);

 

Insert into employees Values ( '{“id” : 1, “name “: “jhon”}');

 

Here table named employees is created with attribute Id and Name

How to retrieve Value from JSON table ?

Select * from Employees;

Output :

+ .............................................+

| data                                       |

+                                             +

| {“id” :1 , “Name” : “jhon”}    |

1 rows in set (0.00 sec).

So these were the basics of database that you might be familiar with . Now here is the quick stack of new features of JSON type that are

introduced in MySQL 5.7

JSON Type specification

 

- Supports utf Mb4 character-set.

- workload optimization

- Validations and parsing on insert.

- Quicker array list access

- Supports all the JSON types – Numbers , string, Bool , objects & arrays.

- Being Extended for – Date, time, date-time ,time-stamp.

Working with JSON Arrays

 

To store the JSON object we need to work with JSON data type.

Here i need to append or you can say add a field named tag inside the table. For that i'll use following SQL statement.

ALTER TABLE Employee ADD COLUMN (tags json);

 

once the JSON field is added into table you can add the strings data in value format. Whenever we retrieve JSON field the value fetched is the

string representation of the JSON field.

Select Name , tag from Employee LIMIT 5;

LIMIT – It specifies how many records to be displayed at single time.

Output:

 

Adding or Removing Value in JSON

 

The tag field contains list of values without it's Key but, MySQL knows working with data that allows us to add or remove values as per our

need.

 

To Add a value we use JSON_ARRAY_APPEND() function.

 

UPDATE people SET tags = JSON_ARRAY_APPEND(tags, "$", "Lorem") WHERE id= 0;

JSON_ARRAY_APPEND() Arguments :

 

1) tag -> Column to be appended.

2) $ -> Path within particular column.

3) Lorem -> Value to be appended.

Output:

 

Outlining future of MySQL and JSON

 

This Article has given you brief insights of what you can achieve when JSON combines with MySQL. JSON is quite easy and familiar language

one has ever known. Are you curious to know more about MySQL and want to implement it with your current application? Then have a word

with the best app development company Hyperlink Infosystem and we will guide you. We are just a word away. Contact us now for mobile app development and the best reasonable quote.