How to use JSON Data Type in MYSQL Database by Practical Example

in this tutorial, you will learn how to use MySQL JSON data type to store and get the JSON object in the database.

Introduction to MySQL JSON data type:

MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these below advantages

• Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
• Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up sub objects or nested values directly by key or array index without reading all values before or after them in the document.

Practical Implementation using MySQL Database:

will see queries and tables by practical example.

First step is to cerate the database:

To define a column whose data type is JSON, you use the following syntax:

Notice that a JSON column cannot have a default value. When you query data from the JSON column, the MySQL optimizer will look for compatible indexes on virtual columns that match JSON expressions.

MySQL JSON data type example:

Suppose, we have to track the Employee data and their actions on our company. Every employee has employee address. In this tutorial we have manage the employee data by using their H. No, City and Pin code, which are in employee address object.

1.Create the table employee_details in database json_example:

create database json_example;
use json_example;
create table emplyee_details( 
 id int auto_increment primary key, 
 emp_name varchar(36), 
 emp_email varchar(36), 
 emp_mobile varchar(36), 
 emp_address json
);

Each employee in the employee table has an id that uniquely identifies the employee. An employee also has a name, email, mobile and address.

2. Insert employee data into table by taking address as Json object:

3. Query to get all employee details from employee_table:

select * from employee_details;

This query returns the following output:

4. Query to get particular employee details with id:

select emp_name, emp_email, emp_mobile, emp_address from employee_details where id=2;

This query returns the following output:

5.To pull values out of the JSON columns, you use the column path operator ( ->):

select id, emp_name, emp_email address->'$.city' address from employee_details;

This query returns the following output:

Notice that data in the address column is surrounded by quote marks. To remove the quote marks, you use the inline path operator (->>) as follows:

select id, emp_name, emp_email, emp_address ->>'$.city' emp_address from employee_details;

This query returns the following output:

As you can see in the following output, the quote marks were removed.

6. To get particular employee details by taking city as input from address column:

select id, emp_name, emp_email, emp_address ->>'$.city' emp_address from employee_details where emp_address ->>'$.city'='Mumbai';

This query returns the following output:

Conclusion: In this tutorial, we have learned about the MySQL JSON data type and how to use it for storing JSON object in the database.