Using JSON as Dynamic Columns with MariaDB

MariaDB CONNECT storage engine handles access to JSON files through standard SQL. It comes with a set of UDFs (user defined functions) to manipulate the JSON format. This JSON content can be stored in a normal text column. This approach can be used to implement dynamic columns. The dynamic column concept was first introduced with MariaDB 5.3.

This schema-less flexibility offered by a free content JSON column gives MariaDB a NoSQL flavor. This creates more flexibility for developers than the usual relational database static column definition.
Let us create a table with a column json_cols of type varchar(512). This column can contain a JSON string that will embed dynamic attributes.

MariaDB > CREATE TABLE jassets (
       item_name varchar(32) primary key, /* A common attribute for all items */
       json_cols varchar(512)  /* JSON data will be stored here */
     );

Let us insert some JSON content into this column. This content represent content that would not fit in a strictly typed predefined column set. This schemaless JSON content can be freely modified as in NoSQL database. This is a very classic pattern for modeling products and attributes in an ecomerce web site.

MariaDB > INSERT INTO jassets VALUES ('MariaDB T-shirt', Json_Object('blue' color, 'XL' size));
MariaDB > INSERT INTO jassets VALUES ('Thinkpad Laptop', Json_Object('black' color, 500 price));
MariaDB > SELECT * FROM jassets;
+-----------------+-------------------------------+
| item_name       | json_cols                     |
+-----------------+-------------------------------+
| MariaDB T-shirt | {"color":"blue","size":"XL"}  |
| Thinkpad Laptop | {"color":"black","price":500} |
+-----------------+-------------------------------+

We cant access a subset of the attributes embedded in this JSON column's content.

SELECT item_name, JsonGet_String(json_cols, 'color') AS color FROM jassets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+
2 rows in set (0.00 sec)

It possible to add or remove a attributes from the dynamic column

MariaDB > UPDATE jassets SET json_cols=Json_Object_Delete(json_cols, 'price')
    -> WHERE JsonGet_String(json_cols, 'color')='black';
MariaDB > UPDATE jassets SET json_cols=Json_Object_Add(json_cols, '3 years' warranty)
    -> WHERE item_name='Thinkpad Laptop';
 
MariaDB > select * from jassets;
+-----------------+----------------------------------------+
| item_name       | json_cols                              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"color":"blue","size":"XL"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+
2 rows in set (0.00 sec)

We can also list all attributes names of the dynamic columns, or get them together with their values in JSON format:

SELECT item_name, Json_Object_List(json_cols) FROM jassets;
+-----------------+-----------------------------+
| item_name       | Json_Object_List(json_cols) |
+-----------------+-----------------------------+
| MariaDB T-shirt | ["color","size"]            |
| Thinkpad Laptop | ["color","warranty"]        |
+-----------------+-----------------------------+

As we have seen before we can also get the full JSON value of the column as it is plain text. This is very useful for a client application that directly manipulate JSON (JAVASCRIPT app for example).

SELECT item_name, json_cols FROM jassets;
+-----------------+----------------------------------------+
| item_name       | json_cols                              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"color":"blue","size":"XL"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

This new way of addressing the dynamic column problem with JSON format gives great flexibility. This approach is open to the use of a JSON friendly language like JAVASCRIPT on the client side. In some use cases the full JSON content will be sent to the client were it will be created, validated and modified.

This JSON access through MariaDB CONNECT storage engine should not be confused with the native JSON support available in MySQL 5.7 (also in Facebook Docstore). Storing JSON in a binary optimized format or storing JSON in a standart text have respective advantages and drawbacks.

The advantage of the text format is that it works with any storage engine : innoDB, Tokudb, RocksDB.
If the JSON content is handled by the client there will be no overhead paid on the MariaDB server for validation and parsing. The binary format also has a space consumption impact.

On the contrary the binary format might be more efficient for server side manipulations.

Leave a Reply Cancel reply