JSON en MariaDB con Dynamic Columns

Hoy vamos hablar de como añadir una funcionalidad NoSQL a la bbdd relacional MariaDB, fork de MySQL.
Esta nueva funcionalidad se llama "dynamic columns" y fue soportada en su primera versión dentro de MariaDB 5.3 

Con dynamic columns se puede almacenar un conjunto "de diferente columnas" en una columna de tipo BLOB.
También te permite solicitar toda la información en formato JSON, a través de la función COLUMN_JSON.

Mariadb se puede instalar de source o desde paquetería (rpm,deb), en mi caso tengo una centos 6.4 y voy a instalar la versión mas avanzada de MariaDB hasta el momento (maridb10.0.4 en versión alpha).

Se pueden bajar e instalar todos estos paquetes (rpm) del siguiente enlace:

MariaDB-10.0.4

• MariaDB-10.0.4-centos6-x86_64-client.rpm
• MariaDB-10.0.4-centos6-x86_64-common.rpm
• MariaDB-10.0.4-centos6-x86_64-compat.rpm
• MariaDB-10.0.4-centos6-x86_64-server.rpm

Una vez instalada y levantada la instancia MariaDB, vamos a crear un base de datos llamada SUPER con una tabla de VENTAS y vamos a realizar las operaciones.

[root@hispabigdata mariadb]# mysql -p mysql

Enter password: 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 10.0.4-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]> create database super;

Query OK, 1 row affected (0.22 sec)

MariaDB [mysql]> use super;

Database changed

MariaDB [super]> 
create table ventas (id int primary key,dynamic_cols blob)engine=innodb;


Query OK, 0 rows affected (3.56 sec)

Es hora de insertar datos con la función COLUMN_CREATE:

MariaDB [super]> insert into ventas values (1,COLUMN_CREATE('Product', 'Fregona', 'Precio',2));

Query OK, 1 row affected (0.06 sec)

Podemos ir añadiendo columnas progresivamente, por ejemplo moneda,fabricante...etc y todo sin cambiar la estructura de la tabla, es decir dinamicamente.

MariaDB [super]> insert into ventas values (2,COLUMN_CREATE('Product', 'Perfume', 'Precio', 25, 'Moneda', 'Euros'));

Query OK, 1 row affected (0.05 sec)

Seguimos añadiendo:

MariaDB [super]> insert into ventas values (3,COLUMN_CREATE('Product', 'Champu', 'Precio', 5, 'Moneda', 'Euros', 'Fabricante', 'Hispabigdata'));

Query OK, 1 row affected (0.03 sec)

Ahora vamos a consultar las columnas que tienen cada uno de los registros con la función COLUMN_LIST:

MariaDB [super]> select id, column_list(dynamic_cols) from ventas;

+----+------------------------------------------+

| id | column_list(dynamic_cols)                |

+----+------------------------------------------+

|  1 | `Precio`,`Product`                       |

|  2 | `Moneda`,`Precio`,`Product`              |

|  3 | `Moneda`,`Precio`,`Product`,`Fabricante` |

+----+------------------------------------------+

3 rows in set (0.88 sec)

Y sus valores, para esto utilizaremos la función COLUMN_GET

MariaDB [super]> select id, column_get(dynamic_cols,'Product' as char) as producto from ventas;

+----+----------+

| id | producto |

+----+----------+

|  1 | Fregona  |

|  2 | Perfume  |

|  3 | Champu   |

+----+----------+

3 rows in set (0.03 sec)


MariaDB [super]> select id, column_get(dynamic_cols,'Product' as char) as producto,column_get(dynamic_cols,'Moneda' as char) as Moneda,column_get(dynamic_cols,'Precio' as int) as Precio,

column_get(dynamic_cols,'Fabricante' as char) as Fabricante from ventas;

+----+----------+--------+--------+--------------+

| id | producto | Moneda | Precio | Fabricante   |

+----+----------+--------+--------+--------------+

|  1 | Fregona  | NULL   |      2 | NULL         |

|  2 | Perfume  | Euros  |     25 | NULL         |

|  3 | Champu   | Euros  |      5 | Hispabigdata |

+----+----------+--------+--------+--------------+

Y por último mostramos todos los valores en formato JSON con la función COLUMN_JSON:

MariaDB [super]> select id, COLUMN_JSON(dynamic_cols) from ventas;
+----+------------------------------------------------------------------------------+
| id | COLUMN_JSON(dynamic_cols)                                                    |
+----+------------------------------------------------------------------------------+
|  1 | {"Precio":2,"Product":"Fregona"}                                             |
|  2 | {"Moneda":"Euros","Precio":25,"Product":"Perfume"}                           |
|  3 | {"Moneda":"Euros","Precio":5,"Product":"Champu","Fabricante":"Hispabigdata"} |
+----+------------------------------------------------------------------------------+
3 rows in set (0.04 sec)