La versión de Mysql es 8.0.18-commercial . La clave principal de la tabla es id . He escrito la siguiente consulta que muestra las columnas de nombre de hostname y details
select hostname, details from table t1; hostname: abc123 details: [ { "Msg": "Job Running", "currentTask": "IN_PROGRESS", "activityDate": "2020-07-20 16:25:15" }, { "Msg": "Job failed", "currentTask": "IN_PROGRESS", "activityDate": "2020-07-20 16:35:24" } ] Quiero el valor de Msg solo del elemento que tiene la fecha de activityDate más reciente
Mi salida deseada es mostrar el nombre de hostname junto con el Msg del elemento con la latest date :
hostname Msg abc123 Job failed He escrito la siguiente consulta y se está ejecutando correctamente pero no muestra nada en absoluto. Además, tarda 17secs en ejecutarse.
select hostname, (select Msg from ( select x.*, row_number() over(partition by t.id order by x.activityDate) rn from table1 t cross join json_table( t.audits, '$[*]' columns( Msg varchar(50) path '$.Msg', activityDate datetime path '$.activityDate' ) ) x ) t where rn = 1) AS Msg from table1;"activityDate"STR_TO_DATE() a las columnas de fecha de activityDate derivadas para obtener resultados ordenados por fecha (no por caracteres ).ROW_NUMBER() junto a la cláusula ORDER BY ( en orden descendente ) y agregando una cláusula LIMIT 1 al final de la consultaPor lo tanto, puede reescribir la consulta como
SELECT t1.hostname, j.Msg FROM t1 CROSS JOIN JSON_TABLE(details, '$[*]' COLUMNS ( Msg VARCHAR(100) PATH '$.Msg', activityDate VARCHAR(100) PATH '$.activityDate' ) ) j ORDER BY ROW_NUMBER() OVER ( -- PARTITION BY id ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC) LIMIT 1Actualizar :
Para el caso de tener varios valores de identificación, puede considerar usar la función ROW_NUMBER() dentro de una subconsulta y filtrar los valores que devuelven igual a 1 en la consulta principal:
SELECT id, Msg FROM ( SELECT t1.*, j.Msg, ROW_NUMBER() OVER (PARTITION BY id ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC) AS rn FROM t1 CROSS JOIN JSON_TABLE(details, '$[*]' COLUMNS ( Msg VARCHAR(100) PATH '$.Msg', activityDate VARCHAR(100) PATH '$.activityDate' ) ) j ) q WHERE rn= 1 Otro método utiliza la función ROW_NUMBER() junto con la cláusula LIMIT que contiene una subconsulta correlacionada y funciona para registros con múltiples valores de identificación :
SELECT t.id, ( SELECT j.Msg FROM t1 CROSS JOIN JSON_TABLE(details, '$[*]' COLUMNS ( Msg VARCHAR(100) PATH '$.Msg', activityDate VARCHAR(100) PATH '$.activityDate' ) ) j WHERE t1.id = t.id ORDER BY ROW_NUMBER() OVER (ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC) LIMIT 1 ) AS Msg FROM t1 AS tTal vez soy de la vieja escuela, pero el campo de fecha debe almacenarse como un campo separado, además del JSON, para permitir consultas fáciles.
¿El ID se incrementa automáticamente y los datos se insertan en el orden de la marca de tiempo? En caso afirmativo, puede ejecutar una consulta como esta para obtener la última fila para cada nombre de host:
SELECT id, hostname, details FROM table t1 WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.hostname = t1.hostname AND t2.id > t1.id) ;