Installing and Using JSON on MySQL 5.7

As we know that MySQL come up with JSON datatype equivalent(but not completely) to MongoDB's Document Store Database.

In this post, we are going to see how to Install and Use MySQL's new JSON datatype.

Installing MySQL - JSON Plugin:

1. In order to use JSON datatype in MySQL, we need to have MySQL 5.7.12 or higher versions.

• Download the latest version of MySQL 5.7.12 or higher from here.

2. Installing the X Plugin

• A non-root user will have access to install the X Plugin on MySQL database, since he have to perform INSERT operations on the database.
• There are 3 types of Plugin installations available. 
• Using MySQL installer for Windows:
• Launch MySQL Installer for Windows.
• Click the Reconfigure option. Use Next and Back to configure for following actions:
• In Accounts and Roles, confirm the current root account password
• In Plugin and Extensions, check mark the Enable X Protocol/MySQL as a Document Store check box.
• In Apply Server Configuration, click Execute.
• Click Finish to close the installer.
• Using MySQL Client Program for Centos/Redhat/Linux flavours:
• Open terminal window
• Login to MySQL with root credentials.
• Issue the following statement to install X plugin

• mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

Note: Replace mysqlx.so with mysqlx.dll for windows

3. Verifying that the X Plugin has been installed.

• When we installed the plugin correctly, without any issues it will show up in the plugins list.
• Login to MySQL and issue below statement

• mysql -u user -p
  
  mysql> show plugins;
  ---------------------------------------------------------------------------------------
  mysqlx                      | ACTIVE   | DAEMON             | mysqlx.so          | GPL 
  ---------------------------------------------------------------------------------------

• You will get ACTIVE message as above.

4. MySQL Shell (Optional)

• MySQL has released MySQL Shell as an interactive client especially designed for the JSON.
• To install MySQL Shell on Windows and Linux.
• Windows:
• Download the Windows (x86, 64-bit), MSI Installer package from here
• When prompted, click Run
• And click Next.
• Linux:

• $ yum install mysql-shell -y

For more information about usage of MySQL Shell click here

Etablishing a session with MySQL Shell:

mysqlsh --uri user@localhost

Logging to MySQL JSON with specific database name:

mysqlsh username@localhost/databasename

Example:

mysqlsh root@localhost/test_json_db
Creating a session to 'root@localhost/test_json_db'
Enter Password: *********

After the successful login you will now prompted to MySQL Shell like below:

mysql-js>