Utilizando JSON en Mysql

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';

Escribe una respuesta