You may have often heard about the type of clothes or food a person may like. Just like that, the value in a variable can be of a different type. These types define the class of a variable and to which category it belongs and which operations can be used on them. For a programmer or developer, it’s very common to use many data types every day while coding. Within databases like MySQL database, MySQL works so well with many data types as its open source platform. Our topic is to cover the JSON (JavaScript Object Notation) datatype in databases without adding complex examples and unnecessary details. Therefore, you have to log in from your Linux system and open its terminal application further.

Update System

Working within any Linux system, we have to strictly make sure that the machine is already up to date and in compliance with the current requirements. For that specific purpose, we have been making our system up to date by consuming the “apt” package in the “update” instruction shown below.

Upgrade System

As the system is up to date, we should be sure that the machine is upgraded as well. For this, we have to use the “apt” instruction with the “upgrade” keyword.

On execution of this instruction, it requires the affirmation if the system needs to get upgraded. In response, you have to press “y” and “Enter” to continue.

Install MySQL Server

After the update and upgrade steps, we are finally able to install the MySQL server on our Linux machine. Without the MySQL server, we will be unable to try the queries to take a look at the JSON datatype of the database. The SQL server can be configured on the Linux machine by executing the install instruction on the terminal query area. This instruction must be followed by the keyword “mysql-server” to correctly configure it.

While installing, we will be informed of the total space it acquires after the full installation of our system. Along with that, it would want to confirm whether we want to continue this process of configuration for MySQL or not. If you don’t have any issues, tap “y” to go on.

It might take up to 5 minutes on your Linux machine or according to the network speed for it to complete the full installation and configuration.

Launch MySQL Database Console

It’s time to get some expertise with MySQL JSON datatype. So, open the command-line interface for mysql by casting off the mysql query with the “-u” option to specify a user with a password to continue login from the SQL. Prefer to use the root user to avoid inconvenience.

Create New Database

On successfully launching the console of MySQL, you have to create a random database: test. Use the newly made “test” database for further processing via the “use” query.

Create Table with JSON Datatype Column

We will be initializing the main topic implementation with the creation of a table with a column of “JSON” datatype in the “test” database. For this, we have been using the CREATE TABLE instruction in the query area of the MySQL command line to generate a table named “Record”. This table has been initialized with three different columns: ID, Name, and info. The first two columns are integer and varchar type respectively. But the third one is of the JSON datatype. As we don’t have added any data to this table, the SELECT query is also returning the Empty Set.

Add JSON Data

The use of “INSERT INTO” instruction is to add records within the already made columns of a table: “Record”. The first two columns represent the integer and varchar values respectively. While the third column contains the JSON data for all 5 records. The JSON data consists of key-value pairs in a separate section covered within the single commas.

Now, utilizing the SELECT query with the “*” character has been outputting the whole table “Record” along with its usual data columns and the JSON data.

Display JSON Data

Now, we will be using some simple instructions to select and display the JSON datatype column along with the usual data type columns of the table “Record”. For this purpose, we are utilizing the SELECT instruction once again to fetch all the records of ID, Name, and info column with a condition. We are limiting the JSON data record display by specifying the “salary” key from the JSON datatype column “info” to display a one-column record for the JSON column “info”.

The JSON datatype column “info” contains the multi-valued key “addr” in it. So, we have been utilizing the SELECT instruction to display the inner values of the “Addr” key.

The shown below SELECT query is utilizing the JSON_EXTRACT function to only get the specified records from the “info” column. The JSON_UNQUOTE function will return the JSON value as a normal string. It also has a condition to only display records having salary=56000.

JSON_TYPE Function

The use of the JSON_TYPE() function can display the type of a JSON data column. We have tried and found out that the JSON datatype is represented as an Array type.

JSON_ARRAY Function

This function can be cast off to display the JSON data in an array format. We have been displaying the record for the key “age” as “5”. Also, use the now() function to display the date and time.

Conclusion

This article can be utilized as a basic help for getting hands-on basic knowledge as well as the high-end knowledge of JSON datatype and its usage in MySQL. Our introductory paragraph covers the importance and usage of different data types in databases. After that, we discussed the ways to update and upgrade the system followed by the installation of the MySQL database server. The most important part of this article covers the use of MySQL servers to create a record of the JSON datatype and manipulate it by different database functions.