JSON_SEARCH

La función JSON_SEARCH busca un valor de texto dentro de un documento JSON y devuelve la ruta donde lo encontró. Mientras que JSON_CONTAINS te dice si un valor existe (verdadero o falso), JSON_SEARCH te dice dónde está exactamente ese valor dentro de la estructura del documento. Esto resulta muy útil cuando trabajas con documentos JSON complejos y necesitas localizar en qué parte de la jerarquía se encuentra un dato específico.

Sintaxis

JSON_SEARCH(json_doc, modo, valor_buscado [, caracter_escape [, ruta ...]])

El primer argumento es el documento JSON donde se realiza la búsqueda. El segundo es el modo de búsqueda: 'one' devuelve la ruta de la primera coincidencia encontrada, mientras que 'all' devuelve un array con las rutas de todas las coincidencias. El tercer argumento es el valor de texto a buscar, que puede incluir el comodín % para coincidencias parciales (similar a LIKE). Opcionalmente, puedes especificar un carácter de escape y una o más rutas donde limitar la búsqueda. La función devuelve NULL si no encuentra coincidencias.

JSON_SEARCH(json_doc, 'one', 'valor')
JSON_SEARCH(json_doc, 'all', 'patrón%')
JSON_SEARCH(json_doc, 'all', 'valor', NULL, '$.ruta')

Es importante saber que JSON_SEARCH solo busca valores de tipo cadena de texto. No encontrará números, booleanos ni valores null.

Comportamiento básico

Empecemos buscando un valor dentro de un array simple:

SELECT JSON_SEARCH(
  '["rojo", "azul", "verde", "amarillo"]',
  'one',
  'azul'
) AS ruta;

La función encontró "azul" en la posición con índice 1 del array (los arrays JSON empiezan en 0). Ahora busquemos en un objeto con estructura más compleja:

SELECT JSON_SEARCH(
  '{"producto": "iPhone", "specs": {"color": "negro", "almacenamiento": "256GB"}}',
  'one',
  'negro'
) AS ruta;

La ruta $.specs.color indica exactamente dónde se encontró el valor "negro" dentro del documento anidado.

Cuando usas el modo 'all' y el valor aparece en varios lugares, obtienes un array con todas las rutas:

SELECT JSON_SEARCH(
  '{"color_primario": "azul", "colores_secundarios": ["rojo", "azul"], "tema": "azul"}',
  'all',
  'azul'
) AS rutas;

El comodín % funciona como en la cláusula LIKE, coincidiendo con cualquier secuencia de caracteres:

SELECT JSON_SEARCH(
  '["smartphone", "smartwatch", "tablet", "smart TV"]',
  'all',
  'smart%'
) AS rutas;

Los tres elementos que comienzan con "smart" fueron encontrados. El comodín _ también está disponible para coincidir con un solo carácter.

Caso práctico: localizar etiquetas en un catálogo

Imagina un catálogo de productos donde cada producto tiene etiquetas en distintos niveles de su estructura JSON:

CREATE TABLE catalogo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100),
    metadata JSON
);
 
INSERT INTO catalogo (nombre, metadata) VALUES
('iPhone 15 Pro',
 '{"categoría": "smartphone", "etiquetas": ["premium", "5g", "fotografía"], "fabricante": {"nombre": "Apple", "país": "EEUU"}}'),
('Cámara Sony A7',
 '{"categoría": "fotografía", "etiquetas": ["profesional", "mirrorless", "fotografía"], "fabricante": {"nombre": "Sony", "país": "Japón"}}'),
('DJI Mini 4 Pro',
 '{"categoría": "dron", "etiquetas": ["fotografía", "vídeo", "portátil"], "fabricante": {"nombre": "DJI", "país": "China"}}');

Para encontrar en qué parte de cada documento aparece la palabra "fotografía":

SELECT
    nombre,
    JSON_SEARCH(metadata, 'all', 'fotografía') AS ubicaciones
FROM catalogo;

En la Cámara Sony A7, "fotografía" aparece tanto en la categoría como en las etiquetas, por lo que se devuelven ambas rutas. En los demás productos aparece una sola vez, y la función devuelve directamente una cadena en lugar de un array.

Para buscar todos los productos de fabricantes cuyo nombre empieza con una letra específica:

SELECT nombre, metadata->>'$.fabricante.nombre' AS fabricante
FROM catalogo
WHERE JSON_SEARCH(metadata, 'one', 'S%', NULL, '$.fabricante.nombre') IS NOT NULL;

El cuarto argumento NULL indica que usamos el carácter de escape predeterminado. El quinto argumento limita la búsqueda a la ruta $.fabricante.nombre.

Caso práctico: búsqueda en configuraciones de usuario

Considera un sistema donde los usuarios almacenan configuraciones con múltiples niveles de anidamiento:

CREATE TABLE configuraciones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario VARCHAR(100),
    config JSON
);
 
INSERT INTO configuraciones (usuario, config) VALUES
('María García',
 '{"tema": "oscuro", "idioma": "español", "notificaciones": {"email": "activo", "sms": "inactivo", "push": "activo"}, "panel": {"vista": "compacta", "orden": "reciente"}}'),
('Carlos López',
 '{"tema": "claro", "idioma": "español", "notificaciones": {"email": "activo", "sms": "activo", "push": "inactivo"}, "panel": {"vista": "detallada", "orden": "nombre"}}');

Para encontrar todos los campos donde un usuario tiene el valor "activo":

SELECT
    usuario,
    JSON_SEARCH(config, 'all', 'activo') AS campos_activos
FROM configuraciones;

Esto te permite saber exactamente qué notificaciones tiene cada usuario activadas sin necesidad de consultar cada campo por separado.

Para buscar todos los campos que contienen la palabra "compacta" o cualquier valor que empiece con "comp":

SELECT
    usuario,
    JSON_SEARCH(config, 'all', 'comp%') AS coincidencias
FROM configuraciones;

Carlos no tiene ningún valor que comience con "comp", por lo que obtiene NULL.

Caso práctico: auditoría de datos sensibles

JSON_SEARCH puede usarse para auditar documentos JSON y encontrar si contienen patrones de datos que no deberían estar almacenados, como fragmentos de emails:

SELECT
    id,
    nombre,
    JSON_SEARCH(metadata, 'one', '%@%') AS contiene_email
FROM catalogo;

Si algún documento contuviera accidentalmente una dirección de email en cualquier campo, JSON_SEARCH la localizaría. Cuando el resultado es NULL, significa que no se encontró ninguna coincidencia.

Manejo de NULL

Cuando el documento JSON es NULL, la función devuelve NULL:

SELECT JSON_SEARCH(NULL, 'one', 'valor') AS resultado;

Cuando no se encuentra el valor buscado, también devuelve NULL:

SELECT JSON_SEARCH('{"clave": "valor"}', 'one', 'inexistente') AS resultado;

Esta es la forma de verificar si un valor de texto existe dentro de un documento JSON: si JSON_SEARCH devuelve NULL, el valor no está presente. Por eso en las cláusulas WHERE se usa IS NOT NULL para filtrar:

SELECT nombre
FROM catalogo
WHERE JSON_SEARCH(metadata, 'one', 'premium') IS NOT NULL;

Combinación con otras funciones

Un patrón muy útil es combinar JSON_SEARCH con JSON_EXTRACT para obtener valores dinámicamente basándose en la ubicación de otro valor:

SELECT
    nombre,
    JSON_SEARCH(metadata, 'one', 'fotografía') AS ruta_encontrada,
    JSON_EXTRACT(
        metadata,
        JSON_UNQUOTE(JSON_SEARCH(metadata, 'one', 'fotografía'))
    ) AS valor_verificado
FROM catalogo
WHERE JSON_SEARCH(metadata, 'one', 'fotografía') IS NOT NULL;

Observa que JSON_SEARCH devuelve la ruta entre comillas, por lo que necesitas JSON_UNQUOTE para pasarla como argumento a JSON_EXTRACT. Este patrón, aunque poco frecuente, demuestra cómo las funciones JSON de MySQL se complementan entre sí para resolver problemas complejos. En el siguiente artículo veremos JSON_TYPE para determinar el tipo de un valor JSON.