Obtener el MAX y el MIN en un objeto json dentro de una columna json

Información previa

A veces tenemos que obtener información contenida en columnas tipo json, existentes en nuestra base de datos.

Aquí vamos a ver cómo podemos hacerlo.

En el momento de escribir este artículo no encontré forma de hacerlo en Laravel 9. Si te animas hay un post en Laracast - Is there a way to get the max of a JSON array in MYSQL (no hace falta ser usuario de pago)

Escenario

Tabla con una columna tipo json llamada data. Su contenido tipo es el de abajo, y buscamos el MIN y el MAX del valor response_time

En este escenario el valor está normalizado number

{
	"data": {
		"csq": -62,
		"icc": "8944501801213377498",
		"imei": "869640059692007",
		"device": "modem",
		"sunset": 1652071940,
		"sunrise": 1652124780,
		"datetime": 1652082623,
		"response_time": 11.29
	},
	"message": "successful",
	"success": true
}

Sql

mysql> SELECT MAX(JSON_EXTRACT(data, "$.data.response_time")) as response  FROM table;
+----------+
| response |
+----------+
| 55.88    |
+----------+
1 row in set (0,03 sec)

mysql> SELECT MIN(JSON_EXTRACT(data, "$.data.response_time")) as response  FROM table;
+----------+
| response |
+----------+
| 6.15     |
+----------+
1 row in set (0,04 sec)

Bonus

Es posible, sobre todo si trabajamos en un proyecto no depurado todavia en el que guardamos datos a los bruto, sin normalización o verificación, que nos salgan valores anormales.

Entonces es bueno obtener los datos de un número limitado de la respuesta ordenados para ver si es algo que falla.

SELECT id, JSON_EXTRACT(data, "$.data.response_time") as response  FROM tabla ORDER BY JSON_EXTRACT(data, "$.data.response_time") DESC LIMIT 20;
+--------+----------+
| id     | response |
+--------+----------+
| 224623 | 600.47   |
| 200807 | 598.7    |
| 177004 | 598.54   |
| 177006 | 598.29   |
| 177005 | 598.29   |
| 177008 | 597.68   |
| 153187 | 596.45   |
| 153189 | 596.33   |
| 129369 | 595.87   |
| 129368 | 595.85   |
| 129371 | 595.33   |
|  10806 | 588.38   |
|  10807 | 588.35   |
|  10809 | 588.19   |
| 273886 | 25.08    |
|  24709 | 22.62    |
| 265865 | 22.32    |
|  92768 | 20.92    |
| 275543 | 20.91    |
| 122073 | 20.15    |
+--------+----------+
20 rows in set (1,30 sec)

Evidentemente aquí hay una desviación bastante clara que debemos limpiar o revisar.

Aviso

Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como esta, sin que ello implique ningún obligación ni responsabilidad por parte de Castris

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.