Utilizando JSON en Mysql
Crear tabla
Creamos la siguiente tabla para hacer pruebas y este ejemplo.
CREATE TABLE `json_example` ( `id` INT NOT NULL AUTO_INCREMENT , `value` JSON NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
Insertar una fila con JSON simple
Esta es la forma para insertar un JSON simple, es decir, sin clave/valor.
INSERT INTO `json_example` (`value`) VALUES ( '["Example 1", "Example 2", "Example 3"]' );
Insertar una fila con un JSON de clave/valor
A continuación, insertaremos tres registros que sí contienen datos en formato JSON con key/value.
INSERT INTO `json_example` (`value`) VALUES ('{"identifier": 102, "name": "example"}');
INSERT INTO `json_example` (`value`) VALUES ('{"identifier": 103, "name": "other content"}');
INSERT INTO `json_example` (`value`) VALUES ('{"identifier": 104, "name": "example 2"}');
Listar un valor según su clave, con comillas
Utilizando JSON_EXTRACT podremos listar los valores de una determinada clave, en todos los registros.
SELECT JSON_EXTRACT(value,'$.name') AS name FROM json_example;
Buscar en un JSON de clave/valor, con comillas
Del mismo modo, podremos filtrar según el valor que necesitemos buscar.
SELECT JSON_EXTRACT(value,'$.name') AS name FROM json_example HAVING name = 'example';
Listar un valor según su clave, sin comillas
Podremos utilizar JSON_UNQUOTE para que los resultados que retorna no contengan las comillas.
SELECT JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) AS name FROM json_example;
Buscar en un JSON de clave/valor, sin comillas
El siguiente ejemplo es el mismo que el anterior, pero filtrando por la clave name.
SELECT JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) AS name FROM json_example HAVING name = 'example';
Buscar en un JSON de clave/valor, sin comillas utilizando LIKE
Ejemplo de cómo filtrar utilizando LIKE sobre el valor de una determinada clave.
SELECT JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) AS name FROM json_example WHERE JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) LIKE '%example%';
Actualizar un registro según un valor
Del siguientem odo podremos actualizar un registro, utilizando LIKE como condición para los registros en que vamos a operar.
UPDATE json_example SET value = JSON_REPLACE(value, '$.name', 'example 2 updated') WHERE JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) LIKE '%example 2%';
Eliminar una clave/valor de un registro
A continuación, un ejemplo de cómo eliminar un par de key/value filtrando con LIKE utilizando JSON_REMOVE.
UPDATE json_example SET value = JSON_REMOVE(value, '$.name') WHERE JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) LIKE '%example%';
Añadir una clave/valor a un registro
Ejemplo de añadir un nuevo conjunto de clave/valor a un regitro, utilizando JSON_INSERT.
UPDATE json_example SET value = JSON_INSERT(value, '$.name', 'new example') WHERE JSON_UNQUOTE(JSON_EXTRACT(value,'$.identifier')) = 102;
Eliminar un registro según un valor
Para eliminar un registro basándonos en el valor de una clave, podremos hacer así:
DELETE FROM json_example WHERE JSON_UNQUOTE(JSON_EXTRACT(value,'$.name')) = 'example 2 updated';