JSON_CONTAINS

La función JSON_CONTAINS te permite verificar si un documento JSON contiene un valor específico, ya sea un elemento dentro de un array, un par clave-valor dentro de un objeto, o incluso un subdocumento completo anidado. Es una de las funciones más utilizadas cuando trabajas con columnas JSON en cláusulas WHERE, porque te permite filtrar registros según el contenido de sus datos JSON sin necesidad de extraer valores previamente.

Sintaxis

JSON_CONTAINS(json_doc, valor_candidato [, ruta])

La función recibe dos o tres argumentos. El primero es el documento JSON donde se busca. El segundo es el valor candidato que quieres verificar si está contenido; este valor debe ser un documento JSON válido, por lo que las cadenas de texto deben ir entre comillas dobles dentro de la cadena SQL. El tercer argumento es opcional y especifica una ruta dentro del documento donde buscar. La función devuelve 1 si el valor está contenido, 0 si no lo está, y NULL si alguno de los argumentos es NULL.

JSON_CONTAINS(json_doc, valor_candidato)
JSON_CONTAINS(json_doc, valor_candidato, '$.ruta')

Comportamiento básico

Para entender cómo funciona JSON_CONTAINS, empecemos con ejemplos directos sin tablas. Cuando buscas un valor en un array JSON, debes pasar el valor como un literal JSON válido:

SELECT JSON_CONTAINS('["rojo", "azul", "verde"]', '"azul"') AS contiene;

Observa que el segundo argumento es '"azul"', con comillas dobles dentro de la cadena SQL. Esto se debe a que JSON_CONTAINS espera un valor JSON válido, y en JSON las cadenas van entre comillas dobles. Si pasaras 'azul' sin las comillas dobles internas, MySQL lanzaría un error porque no es un valor JSON válido.

Con números, la situación es más sencilla porque los números no necesitan comillas en JSON:

SELECT JSON_CONTAINS('[10, 20, 30, 40]', '20') AS contiene;

También puedes verificar si un objeto JSON contiene un subdocumento. En este caso, JSON_CONTAINS verifica que todas las claves del candidato existan en el documento y que sus valores coincidan:

SELECT JSON_CONTAINS(
  '{"nombre": "iPhone", "precio": 1299, "activo": true}',
  '{"precio": 1299}'
) AS contiene;

El subdocumento {"precio": 1299} está contenido porque la clave precio existe y tiene el mismo valor. Si alguna clave del candidato no existiera o tuviera un valor diferente, el resultado sería 0.

Caso práctico: catálogo de productos con etiquetas

Imagina una tabla de productos de una tienda en línea donde cada producto tiene un campo JSON con etiquetas y otro con atributos técnicos:

CREATE TABLE productos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    etiquetas JSON,
    atributos JSON
);
 
INSERT INTO productos (nombre, etiquetas, atributos) VALUES
('iPhone 15 Pro', '["smartphone", "apple", "5g", "premium"]',
 '{"marca": "Apple", "almacenamiento": 256, "color": "titanio", "ram": 8}'),
('Samsung Galaxy S24', '["smartphone", "samsung", "5g", "android"]',
 '{"marca": "Samsung", "almacenamiento": 128, "color": "negro", "ram": 8}'),
('MacBook Air M3', '["portátil", "apple", "ultrabook"]',
 '{"marca": "Apple", "almacenamiento": 512, "color": "plata", "ram": 16}'),
('Xiaomi Redmi Note 13', '["smartphone", "xiaomi", "4g", "económico"]',
 '{"marca": "Xiaomi", "almacenamiento": 128, "color": "azul", "ram": 6}'),
('iPad Air', '["tablet", "apple", "wifi"]',
 '{"marca": "Apple", "almacenamiento": 256, "color": "gris espacial", "ram": 8}');

Para encontrar todos los productos que tienen la etiqueta "apple":

SELECT nombre, etiquetas
FROM productos
WHERE JSON_CONTAINS(etiquetas, '"apple"');

Para buscar productos que sean smartphones con 5G, puedes verificar que el array de etiquetas contenga ambos valores pasando un array como candidato:

SELECT nombre
FROM productos
WHERE JSON_CONTAINS(etiquetas, '["smartphone", "5g"]');

Cuando pasas un array como candidato, JSON_CONTAINS verifica que todos los elementos del candidato estén presentes en el array del documento. Es como un operador AND: ambas etiquetas deben existir.

Caso práctico: búsqueda en atributos con ruta

El tercer argumento de JSON_CONTAINS te permite buscar dentro de una ruta específica del documento. Esto es especialmente útil cuando trabajas con objetos JSON y quieres verificar el valor de una clave concreta:

SELECT nombre, atributos->>'$.marca' AS marca
FROM productos
WHERE JSON_CONTAINS(atributos, '"Apple"', '$.marca');

La ruta '$.marca' le indica a JSON_CONTAINS que busque únicamente dentro del valor asociado a la clave marca. Esto es equivalente a extraer primero el valor con JSON_EXTRACT y compararlo, pero más conciso.

Para buscar productos con exactamente 8 GB de RAM:

SELECT nombre, atributos->>'$.ram' AS ram_gb
FROM productos
WHERE JSON_CONTAINS(atributos, '8', '$.ram');

Caso práctico: preferencias de usuario

Considera una tabla donde almacenas las preferencias de notificación de cada usuario como un documento JSON:

CREATE TABLE usuarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100),
    preferencias JSON
);
 
INSERT INTO usuarios (nombre, preferencias) VALUES
('María García', '{"notificaciones": ["email", "sms", "push"], "idioma": "es", "tema": "oscuro"}'),
('Carlos López', '{"notificaciones": ["email"], "idioma": "es", "tema": "claro"}'),
('Ana Martínez', '{"notificaciones": ["email", "push"], "idioma": "en", "tema": "oscuro"}');

Para encontrar usuarios que tienen activadas las notificaciones push:

SELECT nombre
FROM usuarios
WHERE JSON_CONTAINS(preferencias, '"push"', '$.notificaciones');

Y para encontrar usuarios que tienen tanto email como push activados:

SELECT nombre
FROM usuarios
WHERE JSON_CONTAINS(preferencias, '["email", "push"]', '$.notificaciones');

Carlos no aparece porque aunque tiene "email", no tiene "push" en sus notificaciones.

Manejo de NULL

Cuando cualquiera de los argumentos es NULL, la función devuelve NULL:

SELECT
    JSON_CONTAINS(NULL, '"valor"') AS doc_null,
    JSON_CONTAINS('["a", "b"]', NULL) AS candidato_null,
    JSON_CONTAINS('["a", "b"]', '"a"', NULL) AS ruta_null;

Si tu columna JSON puede contener valores NULL, es importante tenerlo en cuenta al escribir consultas. Un WHERE JSON_CONTAINS(columna, ...) donde columna sea NULL simplemente excluirá esa fila del resultado, ya que NULL no es ni verdadero ni falso.

Combinación con otras funciones

Puedes combinar JSON_CONTAINS con otras funciones JSON para construir consultas más elaboradas. Por ejemplo, para contar cuántos productos pertenecen a cada etiqueta:

SELECT
    nombre,
    JSON_LENGTH(etiquetas) AS total_etiquetas,
    CASE
        WHEN JSON_CONTAINS(etiquetas, '"premium"') THEN 'Sí'
        ELSE 'No'
    END AS es_premium
FROM productos;

También es habitual usar JSON_CONTAINS junto con operadores lógicos para crear filtros complejos:

SELECT nombre
FROM productos
WHERE JSON_CONTAINS(etiquetas, '"smartphone"')
  AND JSON_CONTAINS(atributos, '128', '$.almacenamiento');

Es importante no confundir JSON_CONTAINS con JSON_CONTAINS_PATH. Mientras que JSON_CONTAINS verifica si un valor específico existe dentro del documento, JSON_CONTAINS_PATH verifica si una ruta (clave o estructura) existe, sin importar qué valor tenga. En el siguiente artículo veremos JSON_CONTAINS_PATH para verificar la existencia de rutas.