Blog » Filtrando y Eliminando Datos en Columnas JSON de MySQL con LIKE y el Operador ->>

Trabajar con datos JSON en bases de datos relacionales como MySQL es cada vez más común. Nos permite almacenar información semiestructurada de una manera flexible. Sin embargo, a veces necesitamos buscar o modificar datos basados en el contenido específico dentro de esos documentos JSON.

En este post, veremos cómo realizar búsquedas utilizando LIKE en el valor de una clave específica dentro de una columna JSON en MySQL, y cómo usar esta técnica para eliminar registros. Para ello, nos apoyaremos en el útil operador ->>.

El Operador ->> en MySQL

Antes de sumergirnos en las consultas, es fundamental entender el operador ->>. MySQL proporciona varios operadores para interactuar con datos JSON, como -> y ->>.

El operador -> extrae un valor de un documento JSON en una ruta específica, devolviéndolo como un valor JSON.
El operador ->> extrae un valor de un documento JSON en una ruta específica y lo devuelve como un string (cadena de texto) sin comillas.

Esta última característica del operador ->> es justo lo que necesitamos para poder aplicar operadores de comparación de strings como LIKE.

Ejemplo de Búsqueda con SELECT y LIKE en JSON

Imaginemos que tenemos una tabla llamada histories con una columna details de tipo JSON, que almacena detalles de operaciones. Supongamos que dentro del JSON de la columna details, hay una clave msg que contiene mensajes de texto. Queremos encontrar todos los registros donde el mensaje (msg) termine con la frase “como LOCAL, al tener IGV” y además el tipo de historial (TYPE) sea 3.

Aquí está la consulta SELECT para lograrlo:

SELECT details->>'$.msg' AS mensaje_del_detalle
FROM histories
WHERE details->>'$.msg' LIKE '%como LOCAL, al tener IGV'
AND TYPE = 3;

Explicación de la Consulta SELECT:

SELECT details->>'$.msg' AS mensaje_del_detalle: Seleccionamos el valor de la clave msg dentro del JSON en la columna details. Usamos ->> para obtenerlo como texto plano. $.msg es la ruta JSON que apunta a la clave msg en el nivel raíz del documento. AS mensaje_del_detalle asigna un nombre amigable a la columna en el resultado.
FROM histories: Especifica la tabla de la que obtenemos los datos.
WHERE details->>'$.msg' LIKE '%como LOCAL, al tener IGV': Esta es la condición principal. Filtramos las filas donde el texto extraído de details->>'$.msg' coincide con el patrón '\%como LOCAL, al tener IGV'. El signo % al inicio es un comodín que significa “cero o más caracteres”, por lo que la condición busca mensajes que terminen con la frase especificada.
AND TYPE = 3: Agrega otra condición para filtrar solo las filas donde el valor de la columna TYPE es igual a 3.

Esta consulta te devolverá los mensajes que cumplen con los criterios, permitiéndote verificar los datos antes de realizar alguna modificación o eliminación.

Ejemplo de Eliminación con DELETE basada en JSON y LIKE

Ahora, si nuestro objetivo es eliminar las filas que cumplen exactamente las mismas condiciones de búsqueda, podemos adaptar la cláusula WHERE de la consulta SELECT a una sentencia DELETE.

Aquí tienes la consulta DELETE:

DELETE FROM histories
WHERE details->>'$.msg' LIKE '%como LOCAL, al tener IGV'
AND TYPE = 3;

Explicación de la Consulta DELETE:

DELETE FROM histories: Indica a MySQL que elimine filas de la tabla histories.
WHERE details->>'$.msg' LIKE '%como LOCAL, al tener IGV' AND TYPE = 3: Esta cláusula WHERE es idéntica a la de la consulta SELECT. Garantiza que solo se eliminen las filas donde el valor de la clave msg en el JSON termine con “como LOCAL, al tener IGV” y el valor de la columna TYPE sea 3.

¡MUY IMPORTANTE! Prueba Siempre con SELECT Antes de Eliminar

La sentencia DELETE elimina datos de forma permanente. Antes de ejecutar cualquier DELETE que afecte a múltiples filas, es una práctica fundamental y altamente recomendada ejecutar primero la sentencia SELECT con la misma cláusula WHERE.

-- ¡Ejecuta esto primero para ver qué se eliminará!
SELECT *
FROM histories
WHERE details->>'$.msg' LIKE '%como LOCAL, al tener IGV'
AND TYPE = 3;

Verifica cuidadosamente los resultados de este SELECT. Si la lista de filas es precisamente la que deseas eliminar, entonces procede con la sentencia DELETE.

Posibles Problemas y Consideraciones

Aunque las consultas mostradas son correctas sintácticamente, podrías encontrarte con problemas si:

La estructura del JSON varía: Si la clave msg no siempre está en la raíz, o si está anidada de forma diferente, la ruta $.msg no funcionará para todas las filas.
El valor no es puramente texto: Aunque ->> lo convierte a string, valores inesperados podrían causar problemas.
Diferencias de mayúsculas y minúsculas: Aunque LIKE suele ser case-insensitive, las claves JSON son case-sensitive. Si el contenido del mensaje puede variar en capitalización (“tener igv”, “Tener Igv”), quizás necesites usar LOWER(details->>'$.msg') en la cláusula WHERE para una comparación case-insensitive del contenido del mensaje.
Espacios extra o caracteres ocultos: Los espacios al inicio o final del valor extraído pueden impedir una coincidencia exacta con LIKE. Usar TRIM() podría ayudar.
JSON inválido: Si hay documentos JSON mal formados en la columna, la extracción podría fallar para esas filas.

Siempre es útil inspeccionar tus datos JSON para entender su estructura y contenido real cuando una consulta no produce los resultados esperados.

Filtrando y Eliminando Datos en Columnas JSON de MySQL con LIKE y el Operador ->>

Katen Doe

Celebration

5 Easy Ways You Can Turn Future Into Success

Master The Art Of Nature With These 7 Tips

Filtrando y Eliminando Datos en Columnas JSON de MySQL con LIKE y el Operador ->>

Katen Doe

Celebration

5 Easy Ways You Can Turn Future Into Success

Master The Art Of Nature With These 7 Tips

Press ESC to close