• No products in the cart.
Base de Datos, MySQL

Trabajar con JSON en MySQL

Las bases de datos SQL tienden a ser rígidas.

Si usted ha trabajado con ellos, estaría de acuerdo en que el diseño de la base de datos, aunque parece más fácil, es mucho más difícil en la práctica. Las bases de datos de SQL creen en la estructura, por eso se llama lenguaje de consulta estructurado.

En el otro lado del horizonte, tenemos las bases de datos NoSQL, también llamadas bases de datos sin esquema que fomentan la flexibilidad. En las bases de datos sin esquema, no hay restricción estructural impuesta, sólo los datos que se guardarán.

Aunque cada herramienta tiene su caso de uso, a veces las cosas requieren un enfoque híbrido.

¿Qué pasa si pudiera estructurar algunas partes de su base de datos y dejar que otros sean flexibles?

MySQL versión 5.7.8 introduce un tipo de datos JSON que le permite lograr eso.

En este tutorial, usted va a aprender.

  1. Cómo diseñar las tablas de base de datos utilizando campos JSON.
  2. Las diversas funciones basadas en JSON disponibles en MYSQL para crear, leer, actualizar y eliminar filas.

¿Por qué utilizar JSON?

En este momento, usted probablemente se está preguntando por qué querría usar JSON cuando MySQL ha estado atendiendo a una amplia variedad de necesidades de bases de datos incluso antes de que introdujera un tipo de datos JSON.

La respuesta está en los casos de uso en los que probablemente usaría un enfoque de cambio de marca.

Dejame explicarte con un ejemplo.

Supongamos que está construyendo una aplicación web en la que tiene que guardar la configuración / preferencias de un usuario en la base de datos.

Generalmente, puede crear una tabla de base de datos independiente con los campos id, user_id, key y value o guardarlo como una cadena formateada que puede analizar en tiempo de ejecución.

Sin embargo, esto funciona bien para un pequeño número de usuarios. Si tiene cerca de mil usuarios y cinco claves de configuración, está mirando una tabla con cinco mil registros que se ocupa de una característica muy pequeña de su aplicación.

O si está tomando la ruta de cadena formateada, código extraño que sólo compone la carga del servidor.

El uso de un campo de tipo de datos JSON para guardar la configuración de un usuario en tal escenario puede ahorrar espacio en una tabla de base de datos y reducir el número de registros que se guardaban por separado para que coincidan con el número de usuarios.

Y obtendrá la ventaja adicional de no tener que escribir ningún código de análisis de JSON, el ORM o el lenguaje de ejecución se encarga de ello.

El esquema

Antes de sumergirnos en usar todo el material de JSON en MySQL, vamos a necesitar una base de datos de ejemplo para jugar.

Así que, vamos a obtener nuestro esquema de base de datos fuera de la manera primero.

Vamos a considerar el caso de uso de una tienda en línea que alberga varias marcas y una variedad de productos electrónicos.

Dado que los diferentes componentes electrónicos tienen diferentes atributos (comparar un Macbook con un limpiador Vacuumn) que los compradores están interesados en, por lo general se utiliza el patrón de modelo de valor de atributo de entidad (EAV).

Sin embargo, dado que ahora tenemos la opción de usar un tipo de datos JSON, vamos a eliminar EAV.

Para empezar, nuestra base de datos se llamará e_store y tiene tres tablas que sólo se denominan, marcas, categorías y productos, respectivamente.

Nuestras marcas y categorías de las tablas serán bastante similares, cada uno con un id y un campo de nombre.

 

CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

CREATE TABLE `e_store`.`brands`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

CREATE TABLE `e_store`.`categories`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

 

El objetivo de estas dos tablas será alojar las categorías de productos y las marcas que proporcionan estos productos.

Mientras estamos en ello, vamos a seguir adelante y sembrar algunos datos en estas tablas para usar más tarde.

/* Brands */
INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Samsung');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Nokia');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Canon');

/* Types of electronic device */
INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Television');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Mobilephone');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Camera');

A continuación, es el área de negocios de este tutorial.

Vamos a crear una tabla de productos con los campos id, name, brand_id, category_id y attributes.

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

 

Nuestra definición de tabla especifica restricciones de clave externa para los campos brand_id y category_id, especificando que hacen referencia a la tabla de marcas y categorías respectivamente. También hemos especificado que las filas referenciadas no se deben permitir borrar y si se actualizan, los cambios también deberían reflejarse en las referencias.

El tipo de columna del campo de atributos se ha declarado como JSON, que es el tipo de datos nativo ahora disponible en MySQL. Esto nos permite usar las diversas construcciones relacionadas con JSON en MySQL en nuestro campo de atributos.

Aquí está un diagrama de relación de entidad de nuestra base de datos creada.

 

 

Nuestro diseño de base de datos no es el mejor en términos de eficiencia y precisión. No hay columna de precios en la tabla de productos y podríamos hacer con poner un producto en varias categorías. Sin embargo, el objetivo de este tutorial no es enseñar el diseño de la base de datos, sino más bien cómo modelar objetos de diferente naturaleza en una sola tabla utilizando las funciones JSON de MySQL.

Las Operaciones CRUD

Veamos cómo crear, leer, actualizar y eliminar datos en un campo JSON.

CREAR

Crear un registro en la base de datos con un campo JSON es bastante simple.

Todo lo que necesita hacer es agregar JSON válido como el valor de campo en su instrucción de inserto.

/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Prime' ,
    '1' ,
    '1' ,
    '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Octoview' ,
    '1' ,
    '1' ,
    '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Dreamer' ,
    '1' ,
    '1' ,
    '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Bravia' ,
    '1' ,
    '1' ,
    '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Proton' ,
    '1' ,
    '1' ,
    '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

En lugar de desplegar el objeto JSON usted mismo, también puede utilizar la función JSON_OBJECT incorporada.

La función JSON_OBJECT acepta una lista de pares clave / valor en la forma JSON_OBJECT (clave1, valor1, clave2, valor2, … clave (n), valor (n)) y devuelve un objeto JSON.

/* Let's sell some mobilephones */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Desire' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "5.11 x 2.59 x 0.46 inches" ,
        "weight" ,
        "143 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Passion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
        "body" ,
        "6.11 x 3.59 x 0.46 inches" ,
        "weight" ,
        "145 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Emotion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
        "body" ,
        "5.50 x 2.50 x 0.50 inches" ,
        "weight" ,
        "125 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "5.00 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android KitKat v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Sensation' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
        "body" ,
        "4.00 x 2.00 x 0.75 inches" ,
        "weight" ,
        "150 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "3.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Lollypop v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Joy' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "7.00 x 3.50 x 0.25 inches" ,
        "weight" ,
        "250 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "6.5 inches" ,
        "resolution" ,
        "1920 x 1080 pixels" ,
        "os" ,
        "Android Marshmallow v4.3"
    )
);

Observe la función JSON_ARRAY que devuelve una matriz JSON cuando se pasa un conjunto de valores.

Si especifica una sola tecla varias veces, sólo se conservará el primer par de clave / valor. Esto se llama normalizar el JSON en términos de MySQL. Además, como parte de la normalización, las claves de objeto se ordenan y se elimina el espacio en blanco adicional entre pares clave / valor.

Otra función que podemos utilizar para crear objetos JSON es la función JSON_MERGE.

La función JSON_MERGE toma varios objetos JSON y produce un único objeto agregado.

/* Let's sell some cameras */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Explorer' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV III"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Runner' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        JSON_OBJECT("processor" , "Digic DV II") ,
        JSON_OBJECT("scanning_system" , "progressive") ,
        JSON_OBJECT("mount_type" , "PL") ,
        JSON_OBJECT("monitor_type" , "LED")
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Traveler' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        '{"processor": "Digic DV II"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Walker' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LED"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Jumper' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

Hay mucho que sucede en estas declaraciones de inserto y puede ser un poco confuso. Sin embargo, es bastante simple.

Sólo estamos pasando objetos a la función JSON_MERGE. Algunos de ellos se han construido utilizando la función JSON_OBJECT que vimos anteriormente, mientras que otros han pasado como cadenas JSON válidas.

En el caso de la función JSON_MERGE, si una clave se repite varias veces, su valor se conserva como matriz en la salida.

Una prueba de concepto está en orden supongo.

/* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

Podemos confirmar que todas nuestras consultas se han ejecutado correctamente utilizando la función JSON_TYPE que nos da el tipo de valor del campo.

/* output: OBJECT */
SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;

LEER

Correcto, tenemos algunos productos en nuestra base de datos para trabajar.

Para valores típicos de MySQL que no son del tipo JSON, una cláusula where es bastante directa. Simplemente especifique la columna, un operador y los valores que necesita para trabajar.

Heurísticamente, cuando se trabaja con columnas JSON, esto no funciona.

/* It's not that simple */
SELECT
    *
FROM
    `e_store`.`products`
WHERE
    attributes = '{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"}';

 

Cuando desee reducir las filas utilizando un campo JSON, debe familiarizarse con el concepto de una expresión de ruta.

La definición más simple de una expresión de ruta (creo que los selectores de JQuery) es que se utiliza para especificar qué partes del documento JSON para trabajar.

La segunda pieza del rompecabezas es la función JSON_EXTRACT que acepta una expresión de ruta para navegar a través de JSON.

Digamos que estamos interesados en la gama de televisores que tienen al menos un puerto USB y HDMI.

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

 

El primer argumento a la función JSON_EXTRACT es el JSON para aplicar la expresión de ruta a la que se encuentra la columna de atributos. El símbolo $ simboliza el objeto con el que trabajar. Las expresiones de ruta $ .ports.usb y $ .ports.hdmi se traducen en «tomar la clave USB en puertos» y «llevar la clave hdmi en puertos», respectivamente.

Una vez que hemos extraído las claves que nos interesan, es bastante sencillo usar los operadores MySQL como> en ellos.

Además, la función JSON_EXTRACT tiene el alias -> que puede utilizar para hacer sus consultas más legibles.

Revisando nuestra consulta anterior.

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND `attributes` -> '$.ports.usb' > 0
AND `attributes` -> '$.ports.hdmi' > 0;

 

ACTUALIZAR

Para actualizar los valores de JSON, vamos a utilizar las funciones JSON_INSERT, JSON_REPLACE y JSON_SET. Estas funciones también requieren una expresión de ruta para especificar qué partes del objeto JSON se deben modificar.

La salida de estas funciones es un objeto JSON válido con los cambios aplicados.

Modifiquemos todos los teléfonos móviles para tener una propiedad de chipset también.

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm'
)
WHERE
    `category_id` = 2;

The $.chipset path expression identifies the position of the chipsetproperty to be at the root of the object.

Let us update the chipset property to be more descriptive using the JSON_REPLACE function.

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm Snapdragon'
)
WHERE
    `category_id` = 2;

¡Pan comido!

Por último, tenemos la función JSON_SET que utilizaremos para especificar que nuestras televisiones son bastante coloridas.

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
    `attributes` ,
    '$.body_color' ,
    'red'
)
WHERE
    `category_id` = 1;

 

 

Todas estas funciones parecen idénticas, pero hay una diferencia en la forma en que se comportan.

La función JSON_INSERT sólo agregará la propiedad al objeto si ya no existe.

La función JSON_REPLACE sustituye la propiedad sólo si se encuentra.

La función JSON_SET agregará la propiedad si no se encuentra en otro lugar.

BORRAR

Hay dos partes para eliminar que vamos a ver.

El primero es eliminar una determinada clave / valor de sus columnas JSON mientras que la segunda es eliminar filas usando una columna JSON.

Digamos que ya no proporcionamos la información mount_type para las cámaras y deseamos eliminarla para todas las cámaras.

Lo haremos utilizando la función JSON_REMOVE que devuelve el JSON actualizado después de quitar la clave especificada basada en la expresión de la ruta de acceso.

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
    `category_id` = 3;

Para el segundo caso, tampoco proporcionamos teléfonos móviles que tengan la versión Jelly Bean del sistema operativo Android.

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

Como se indicó anteriormente, el trabajo con un atributo específico requiere el uso de la función JSON_EXTRACT para aplicar el operador LIKE, primero hemos extraído la propiedad os de los teléfonos móviles (con la ayuda de category_id) y eliminado todos los registros que contienen la cadena Jellybean .

 

Fuente: https://scotch.io/tutorials/working-with-json-in-mysql

Like

CEO Inleggo

Comments

  • Luis Gonzalo Canetti Morales
    1 diciembre, 2018

    Excelente, Muchas gracias…

  • Nicolas Rodriguez
    15 enero, 2018

    Hola que tal… Buen ejemplo… Te hago una consulta… Se puede leer un json, como si fuera una tabla «mysql»…. Es decir yo tengo un json, se llama «clientes.json» que lo obtengo de otra base de datos y que subo a mi sitio y lo alojo en la raiz del mismo… este archivo tiene los datos de los clientes de esta base de datos. puede hacer un select y crear una query desde este json? es decir simular que el archivo fuera una tabla?

Sorry, the comment form is closed at this time.