MariaDB JSON Data Type

In this MariaDB tutorial, we will look at MariaDB JSON Data Type and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB JSON data type
• MariaDB JSON data type performance
• MariaDB JSON data type Error

MariaDB JSON data type

We’ll learn about the MariaDB JSON data type in this part, which is described with illustrated syntax and an example.

The MariaDB’s JSON data type is a replacement for LONGTEXT, which was designed to work with MySQL’s JSON data type. Because it violates the SQL standard, MariaDB supports the JSON data format as a LONGTEXT instead, and MariaDB’s benchmarks show that efficiency is at least comparable.

The sample example of MariaDB JSON datatype by using the following query:

EXAMPLE:

CREATE TABLE Json_demo(
user_name varchar(20) not null,
json_value json
CHECK(JSON_VALID(json_value)));

INSERT INTO JSON_DEMO(user_name,json_value)
values('JohnY_K','{"json_id":1,"json_name":"Anthony"}');

SELECT * FROM JSON_DEMO;

In the first query, we have created a table called JSON_DEMO with columns as USER_NAME and JSON_VALUE. The JSON_VALUE column is generated by the JSON Datatype. In the second query, we have inserted one record in the JSON_DEMO table by using the INSERT INTO statement.

In the last query, we have tried to retrieve all records including the JSON datatype value also in the JSON_DEMO table by using the SELECT statement.

Read: How to load files into MariaDB

MariaDB JSON data type performance

In this section, we will know about the performance of the JSON datatype in the MariaDB in detail.

The main purpose of using the JSON datatype in MariaDB comes with two advantages over storing JSON strings in the text field and data validation. The JSON documents will automatically be validated and invalid documents will produce an error.

The JSON VALID function can be used rather than the CHECK constraint to ensure that a valid JSON document is stored. This constraint is automated and includes types from MariaDB 10.4.3’s JSON alias.

Read: MariaDB Reserved Words

MariaDB JSON data type Error

In this section, we will know about the type of Error that arises while using the JSON datatype in MariaDB.

In MariaDB, the JSON datatype can’t be one of the following data types (invalid, Function, and Date). The example of error shown in the MariaDB JSON data type by the following query:

EXAMPLE:

CREATE TABLE Json_demo(
user_name varchar(20) not null,
json_value json
CHECK(JSON_VALID(json_value)));

INSERT INTO JSON_DEMO(user_name,json_value)
values('David_TS','invalid');

SELECT * FROM JSON_DEMO;

In the second query, the error came while inserting a long text value or string of JSON datatype of the JSON_VALUE column in the JSON_DEMO table by using the INSERT INTO statement.

Now, let’s remove the error of the JSON data type in the JSON_DEMO table by the following query:

ERROR REMOVE EXAMPLE:

CREATE TABLE Json_demo(
user_name varchar(20) not null,
json_value json
CHECK(JSON_VALID(JSON_VALUE)));

INSERT INTO JSON_DEMO(user_name,json_value)
values('JohnY_K','{"json_id":2,"json_name":"George"}');

SELECT * FROM Json_demo;

In the preceding query after the CREATE TABLE statement of the JSON_DEMO table, we have inserted another record in the JSON_DEMO table by using the INSERT INTO statement. And in the json_value column, we have inserted string or long text value as “json_id:2, json_name: George”.

If we want to retrieve all records from the JSON_DEMO table, then we will use the SELECT statement.

You may also like to read the following MariaDB tutorial.

• MariaDB vs Postgres
• MariaDB Drop Table
• MariaDB COUNT Function
• MariaDB Variables Tutorial
• MariaDB Insert If Not Exists

In this MariaDB tutorial, we have discussed the MariaDB JSON Data Type and also discusses some sample examples related to it. There are lists of the topic that comes under discussion:

• MariaDB JSON data type
• MariaDB JSON data type performance
• MariaDB JSON data type Error