Skip to content

Using JSON’s Arrays for MariaDB Dynamic Columns

The JSON format includes the concept of array. A JSON object cant contain an attribute of array type. We have seen that we can use the MariaDB CONNECT Storage Engine provided UDFs (user defined functions) to implement dynamic columns.

Let us create a table with a text column containing a a JSON string and let us insert some JSON content into this column. In JSON an attribute can be an array. Here the size attribute is a JSON array. We will use this array to manage the list of available sizes for a product.

MariaDB > CREATE TABLE jassets (
       item_name varchar(32) primary key, 
       json_cols varchar(512) 
     );
MariaDB > INSERT INTO jassets VALUES ('T-shirt', 
       Json_Object('blue' color, Json_Array('XL','M') json_size));
MariaDB > INSERT INTO jassets VALUES ('Thinkpad Laptop', Json_Object('black' color, 500 price));
MariaDB > SELECT * FROM jassets;
+-----------------+------------------------------------+
| item_name       | json_cols                          |
+-----------------+------------------------------------+
| T-shirt         | {"color":"blue","size":["XL","M"]} |
| Thinkpad Laptop | {"color":"black","price":500}      |
+-----------------+------------------------------------+

Through the available MariaDB JSON UDFs It is possible to Add or remove values to this array :

MariaDB > UPDATE jassets SET json_cols = json_array_add(json_cols, "S", 0, 'size')  WHERE item_name='T-shirt';
MariaDB > SELECT * FROM jassets;
+-----------------+----------------------------------------+
| item_name       | json_cols                              |
+-----------------+----------------------------------------+
| T-shirt         | {"color":"blue","size":["S","XL","M"]} |
| Thinkpad Laptop | {"color":"black","price":500}          |
+-----------------+----------------------------------------+
MariaDB >
MariaDB > UPDATE jassets SET json_cols = json_array_delete(json_cols, 2, 'size')  WHERE item_name='T-shirt';
MariaDB > SELECT *  from jassets;
+-----------------+------------------------------------+
| item_name       | json_cols                          |
+-----------------+------------------------------------+
| T-shirt         | {"color":"blue","size":["S","XL"]} |
| Thinkpad Laptop | {"color":"black","price":500}      |
+-----------------+------------------------------------+

This new way of addressing the dynamic column problem using JSON arrays gives great flexibility. This capability to use arrays does not exist currently in MariaDB dynamic columns.
This hybrid approach mixing relational fixed column content with JSON schemaless content overcomes the limitations of a pure NoSQL databases. It relies on the very strongly proven and consistent capabilities a relational database.

As I said previously this JSON access through MariaDB CONNECT storage engine should not be confused with the native JSON support available in Oracle/MySQL 5.7 (also in Facebook/MySQL Docstore).

Leave a Reply

Your email address will not be published. Required fields are marked *