Jorge Arrambide

Especialista en programación web en java

Base de datos

Ordenar Json en MySQL 8

En en artículo anterior, te mostré como crear un json en MySQL. Ahora te muestro como puedes ordenar la información de un arreglo json.

Requerimientos

  • MySQL 8.0.2
  • Workbench

Problema: ¿Cómo ordenar los elementos de un arreglo json (json rray)?

Cuando usamos la función JSON_ARRAYAGG, Mysql 8.0.2 no puede ordenar los elementos de dicho arreglo.

--      ESTO NO FUNCIONA =(

SELECT 
	JSON_ARRAYAGG(JSON_OBJECT('id', r.id,'nombre', r.nombre))
FROM
	tutoriales.referencias r
 WHERE r.idUsuario = 1
 ORDER BY r.nombre asc -- Nomas no jala =/
 ;

Solución

Para ordenar un arreglo json en MySQL usaremos la función ROW_NUMBER, como lo explico en el siguiente vídeo:

https://youtu.be/oXa6wgCpqhM

Query

Usaremos las funciones JSON_OBJECT Y JSON_ARRAYAGG. 

-- Resultado final =)

SELECT
  JSON_ARRAYAGG(jarray.dato)
FROM (
    -- Consulta del paso 2     
	select JSON_OBJECT('id', r.id,'nombre', r.nombre) AS dato,
		ROW_NUMBER()  OVER ( order by r.nombre asc)
	from tutoriales.referencias r

) jarray
WHERE 1=1;

Crear documento json a partir de tablas relacionales en MySQL

Requerimientos

  • MySQL 8.0.2
  • Workbench

Query

Usaremos las funciones JSON_OBJECT Y JSON_ARRAYAGG. 

SELECT
	JSON_OBJECT('id',u.id, 'nombre', u.nombre, 'paterno' ,u.paterno, 'materno' ,u.materno, 
    'direcciones',
			(
			SELECT 
			JSON_ARRAYAGG(JSON_OBJECT('calle', d.calle ,'colonia' ,d.colonia, 'municipio' , m.descripcion, 'estado' , e.descripcion ))
			FROM
				tutoriales.direccion d
						INNER JOIN tutoriales.municipio m
						ON m.id = d.id
						INNER JOIN tutoriales.estado e
						ON e.id = m.idEstado
			WHERE d.idUsuario = u.id
			),
	 'referencias', (
			SELECT 
				JSON_ARRAYAGG(JSON_OBJECT('id', r.id,'nombre', r.nombre,
                'telefonos', (
                
					SELECT JSON_ARRAYAGG(JSON_OBJECT ('numero',rt.numero, 'tipoTelefono', tt.descripcion))
							FROM referenciatelefono rt
								INNER JOIN tipotelefono tt
								ON tt.id = rt.idTipoTelefono
							WHERE rt.idReferencia = r.id
                
                )
                ))
			FROM
				tutoriales.referencias r
			 WHERE r.idUsuario = u.id
		)
    )
AS dato
FROM usuario u 
WHERE u.id = 1;

Script para crear tablas relacionales

CREATE DATABASE  IF NOT EXISTS `tutoriales` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `tutoriales`;
--
-- Table structure for table `direccion`
--
DROP TABLE IF EXISTS `direccion`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `direccion` (
  `id` int NOT NULL AUTO_INCREMENT,
  `idUsuario` int NOT NULL,
  `calle` varchar(45) NOT NULL,
  `colonia` varchar(45) NOT NULL,
  `idMunicipio` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `estado`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `estado` (
  `id` int NOT NULL,
  `descripcion` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `municipio`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `municipio` (
  `id` int NOT NULL AUTO_INCREMENT,
  `descripcion` varchar(100) NOT NULL,
  `idEstado` int NOT NULL,
  `idMunicipio` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `referencias`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `referencias` (
  `id` int NOT NULL AUTO_INCREMENT,
  `idUsuario` varchar(45) NOT NULL,
  `nombre` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `referenciatelefono`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `referenciatelefono` (
  `id` int NOT NULL AUTO_INCREMENT,
  `idReferencia` int NOT NULL,
  `numero` varchar(45) NOT NULL,
  `idTipoTelefono` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `telefono`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `telefono` (
  `id` int NOT NULL,
  `numero` varchar(45) DEFAULT NULL,
  `idTipoTelefono` int NOT NULL,
  `idUsuario` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `tipotelefono`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipotelefono` (
  `id` int NOT NULL,
  `descripcion` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `usuario`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `usuario` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nombre` varchar(100) DEFAULT NULL,
  `paterno` varchar(100) DEFAULT NULL,
  `materno` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Script para insertar información

INSERT INTO `direccion` VALUES (1,1,'Calle 10','Jardines de Anahuac',1),(2,1,'Pico de Socampa','Balcones de las Puentes',1);

INSERT INTO `estado` VALUES (14,'Jalisco'),(19,'Nuevo León');

INSERT INTO `municipio` VALUES (1,'San Nicolás de los Garza',19,46),(2,'Linares',19,33),(3,'Monterrey',19,39),(4,'Guadalajara',14,39),(5,'Puerto Vallarta',14,67);

INSERT INTO `referencias` VALUES (1,'1','Norma Treviño'),(2,'1','Mérida Arrambide'),(3,'1','Astrid Arrambide'),(4,'1','Santiago Arrambide');

INSERT INTO `referenciatelefono` VALUES (1,1,'8111323221',2),(2,1,'8183305866',1),(3,2,'5599888221',2),(4,3,'8167626161',2),(5,4,'8177188112',2);

INSERT INTO `telefono` VALUES (1,'8122154101',2,1),(2,'8183305836',1,1),(3,'8189898989',3,1),(4,'8111312929',2,2);

INSERT INTO `tipotelefono` VALUES (1,'Casa'),(2,'Celular'),(3,'Trabajo');

INSERT INTO `usuario` VALUES (1,'Jorge','Arrambide','Hernandez'),(2,'Santiago','Arrambide','Treviño'),(3,'Astrid','Arrambide','Treviño'),(4,'Mérida','Arrambide','Treviño');

Solucionar error al crear o importar función de MySQL

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

Para solucionar el error se puede hacer de dos maneras:

  • Ejecutar el siguiente script en MySQL
  • Agregar la siguiente línea de código al archivo mysql.ini

log_bin_trust_function_creators = 1;

Lo anterior hace que la configuración no sea tan “estricta” al revisar funciones no deterministas.