MySQL 5.7: Introduced Awesome JSON Data Type

This is one of the interesting topics, in the new version of MySQL 5.7 we can store JSON formatted data.
Now a day JSON data type is a common feature for all new coming RDBMS product versions like: PostgreSQL 9.4 and SQL Server 2016 have already introduced this.

Now We can say, we have MySQL 5.7 with the RDBMS + NOSQL concepts.

We can store JSON data as strings, but the JSON Data types have the advantage of enforcing that each stored value is valid according to the JSON rules.

The size of JSON documents stored in JSON columns is limited to the value of the max_allowed_packet system variable.

The JSON columns cannot have a default value.

MySQL 5.7 also introduced a different function to manipulate and manage JSON data in MySQL.
Tomorrow, I will demonstrate these functions.

Small demonstration JSON Data Type:
Create table with JSON Data type:

1 2 3 4 5 6 7

CREATE TABLE tbl_TestJSON ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSON ,Address JSON );

Insert few sample record:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

INSERT INTO tbl_TestJSON VALUES ( 1 ,'Sales' ,'{"firstName": "Anvesh", "lastName": "Patel"}' ,'{"address" : "Hyderabad"}' ) ,( 2 ,'Production' ,'{"firstName": "Neevan", "lastName": "Patel"}' ,'{"address" : "Ahmedabad"}' ) ,( 3 ,'Animation' ,'{"firstName": "Eric", "lastName": "Lorn"}' ,'{"address" : "USA"}' );

You can search key value in WHERE clause:

1 2	SELECT *FROM tbl_TestJSON WHERE EmployeeDetails->"$.firstName"="Neevan";