JSON Data Type Support in MariaDB 10.2.x

Amar Gurung May 14th, 2021

Introduction
JSON is a widely used data-interchange format commonly used in web applications, one example being REST APIs requests & responses. This blog will walk readers through which MariaDB version supports JSON datatype, how to upgrade from MariaDB from 10.1.x to 10.2.x, and finally, some hands-on practice using a JSON data type with simple SQL queries using MySQL console.

Which MariaDB version supports JSON data type?
MariaDB 10.2.7 added the JSON alias for LONGTEXT, introducing more compatibility with MySQL’s JSON data type. MariaDB implements this as a LONGTEXT to avoid the JSON datatype contradicting SQL standard, plus MariaDB’s benchmarks indicate that performance is at least equivalent.

To ensure that a valid JSON document is inserted, the JSON_VALID function can be used as a CHECK constraint. This constraint automatically includes all types using the JSON alias from MariaDB 10.4.3.

Steps to upgrade from MariaDB 10.1.x to 10.2.x:

1. vi /etc/yum.repos.d/MariaDB.repo
2. Change the baseurl from 10.1 to 10.2 as shown below:

[mariadb]
name = MariaDB
baseurl = https://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

3. Remove the old version of MariaDB with the command below:

sudo yum remove MariaDB-server

4. Install the most common packages, execute the following command:

sudo yum install MariaDB-server galera MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common

5. MariaDB’s systemd service can be configured to start at boot by executing the following:

sudo systemctl enable mariadb.service

6. MariaDB’s systemd service can be started by executing the following:

sudo systemctl start mariadb.service

7. In case you see too many connection errors. Add the line below in file /etc/my.cnf.d/server.cnf

[mysqld]
max_connections = 1500

8. Verify you can log in and perform DB operations via MySQL console.

Using JSON datatypes in MariaDB 10.2.37
1. Without JSON validation:

2. With JSON Validation:

Summary:
This blog has walked you through JSON data type support in MariaDB 10.2.x support, how to upgrade from MariaDB from 10.1.x to 10.2.x, and has provided readers with a hands-on opportunity to use JSON data type with and without validation on JSON data type along with simple SQL queries using MySQL console.

about the author

Amar Gurung

Amar is a senior module lead as a part of Zaloni's engineering team.