MySQL Tutorial
MySQL

JSON Path

JSON Path es la sintaxis que MySQL utiliza para indicar la ubicación de un valor dentro de un documento JSON. Cada vez que usas funciones como JSON_EXTRACT, JSON_SET o el operador ->, necesitas proporcionar una expresión JSON Path para decirle a MySQL exactamente dónde buscar o dónde actuar dentro del documento. Comprender esta sintaxis es fundamental para trabajar con datos JSON en MySQL, ya que prácticamente todas las funciones JSON la utilizan.

Sintaxis

Toda expresión JSON Path comienza con el signo $, que representa la raíz del documento. A partir de ahí, usas notación de punto o corchetes para navegar a los elementos internos:

-- Notación de punto para acceder a una clave de objeto
$.clave
 
-- Notación de corchetes para acceder a una clave de objeto
$."clave con espacios"
 
-- Índice numérico para acceder a un elemento de array
$[0]
 
-- Combinaciones para rutas anidadas
$.objeto.subclave
$.array[2].nombre

El símbolo $ solo, sin ningún acceso adicional, se refiere al documento completo. Esto puede parecer trivial, pero es útil en funciones como JSON_KEYS donde necesitas indicar en qué nivel del documento quieres operar.

Comportamiento básico

La forma más habitual de navegar por un objeto JSON es la notación de punto. Supongamos que tenemos una tabla de productos con una columna JSON para atributos:

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    detalles JSON
);
 
INSERT INTO productos (nombre, detalles) VALUES
('Portátil Gaming', '{
    "marca": "ASUS",
    "modelo": "ROG Strix G16",
    "especificaciones": {
        "procesador": "Intel i9-13980HX",
        "ram": "32GB DDR5",
        "gpu": "RTX 4070",
        "almacenamiento": {"tipo": "NVMe SSD", "capacidad": "1TB"}
    },
    "puertos": ["USB-C", "USB-A", "HDMI 2.1", "RJ45"]
}');

Para acceder a valores en diferentes niveles de profundidad:

SELECT
    detalles->>'$.marca' AS marca,
    detalles->>'$.especificaciones.procesador' AS cpu,
    detalles->>'$.especificaciones.almacenamiento.tipo' AS tipo_disco
FROM productos
WHERE id = 1;
marcacputipo_disco
ASUSIntel i9-13980HXNVMe SSD

Cada punto representa un nivel de profundidad en el objeto. La ruta $.especificaciones.almacenamiento.tipo atraviesa tres niveles: primero accede a especificaciones, dentro de ese objeto accede a almacenamiento, y dentro de ese sub-objeto accede a tipo.

Para acceder a elementos de un array JSON, usas corchetes con un índice numérico que empieza en 0:

SELECT
    detalles->>'$.puertos[0]' AS primer_puerto,
    detalles->>'$.puertos[1]' AS segundo_puerto,
    detalles->>'$.puertos[3]' AS ultimo_puerto
FROM productos
WHERE id = 1;
primer_puertosegundo_puertoultimo_puerto
USB-CUSB-ARJ45

Si intentas acceder a un índice que no existe, MySQL devuelve NULL en lugar de un error:

SELECT detalles->>'$.puertos[10]' AS puerto_inexistente
FROM productos
WHERE id = 1;
puerto_inexistente
NULL

Desde MySQL 8.0.21, también puedes usar la palabra clave last para referirte al último elemento del array, e incluso hacer aritmética con ella:

SELECT
    detalles->>'$.puertos[last]' AS ultimo,
    detalles->>'$.puertos[last - 1]' AS penultimo
FROM productos
WHERE id = 1;
ultimopenultimo
RJ45HDMI 2.1

Comodín de array: [*]

El asterisco dentro de corchetes selecciona todos los elementos de un array. Esto devuelve un array JSON con los valores extraídos:

SELECT detalles->'$.puertos[*]' AS todos_los_puertos
FROM productos
WHERE id = 1;
todos_los_puertos
["USB-C", "USB-A", "HDMI 2.1", "RJ45"]

El comodín [*] es especialmente útil cuando tienes arrays de objetos y quieres extraer un campo específico de cada elemento. Imaginemos una tabla de pedidos:

CREATE TABLE pedidos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT,
    lineas JSON
);
 
INSERT INTO pedidos (cliente_id, lineas) VALUES
(1, '[
    {"producto": "Teclado mecánico", "cantidad": 1, "precio": 89.99},
    {"producto": "Ratón inalámbrico", "cantidad": 2, "precio": 45.00},
    {"producto": "Alfombrilla XL", "cantidad": 1, "precio": 29.99}
]');
SELECT lineas->'$[*].producto' AS productos_del_pedido
FROM pedidos
WHERE id = 1;
productos_del_pedido
["Teclado mecánico", "Ratón inalámbrico", "Alfombrilla XL"]

En este caso, $[*] navega a cada elemento del array raíz, y .producto extrae el campo producto de cada objeto.

Comodín de objeto: .*

Cuando un nivel del JSON es un objeto y quieres acceder a todos sus valores (sin importar la clave), usas .*:

SELECT detalles->'$.especificaciones.*' AS todos_los_valores
FROM productos
WHERE id = 1;
todos_los_valores
["Intel i9-13980HX", "32GB DDR5", "RTX 4070", {"tipo": "NVMe SSD", "capacidad": "1TB"}]

El comodín .* devuelve todos los valores del objeto especificaciones. Observa que uno de los valores es a su vez un objeto (almacenamiento), que aparece completo.

Comodín recursivo: **

El doble asterisco realiza una búsqueda recursiva en todos los niveles del documento. Es extremadamente potente para encontrar valores sin importar dónde estén anidados:

SELECT JSON_EXTRACT(detalles, '$.especificaciones**.tipo') AS tipos_encontrados
FROM productos
WHERE id = 1;
tipos_encontrados
["NVMe SSD"]

El patrón $.especificaciones**.tipo busca cualquier clave llamada tipo dentro de especificaciones, sin importar cuántos niveles de profundidad haya. Ten en cuenta que ** debe ir siempre seguido de un acceso de clave o índice. No puedes usar ** al final de la expresión.

Caso práctico: respuestas de API

Un uso frecuente de JSON Path es navegar por respuestas de APIs externas almacenadas directamente en la base de datos:

CREATE TABLE respuestas_api (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(255),
    respuesta JSON,
    recibido_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO respuestas_api (endpoint, respuesta) VALUES
('/api/envio/tracking', '{
    "codigo_seguimiento": "ES2024ABC123",
    "estado": "en_transito",
    "origen": {"ciudad": "Madrid", "pais": "ES"},
    "destino": {"ciudad": "Barcelona", "pais": "ES"},
    "eventos": [
        {"fecha": "2024-03-15 08:00", "ubicacion": "Madrid", "descripcion": "Recogido en almacén"},
        {"fecha": "2024-03-15 14:30", "ubicacion": "Zaragoza", "descripcion": "En tránsito"},
        {"fecha": "2024-03-16 09:15", "ubicacion": "Barcelona", "descripcion": "En reparto"}
    ]
}');
SELECT
    respuesta->>'$.codigo_seguimiento' AS tracking,
    respuesta->>'$.estado' AS estado,
    respuesta->>'$.origen.ciudad' AS origen,
    respuesta->>'$.destino.ciudad' AS destino,
    respuesta->>'$.eventos[last].descripcion' AS ultimo_evento,
    respuesta->>'$.eventos[last].fecha' AS fecha_ultimo
FROM respuestas_api
WHERE endpoint = '/api/envio/tracking';
trackingestadoorigendestinoultimo_eventofecha_ultimo
ES2024ABC123en_transitoMadridBarcelonaEn reparto2024-03-16 09:15

Con una sola consulta hemos navegado por cuatro niveles diferentes del documento: raíz, objetos anidados, array de eventos y el último evento del array.

Caso práctico: configuraciones anidadas

Las aplicaciones suelen almacenar configuraciones con varios niveles de profundidad:

CREATE TABLE configuracion_app (
    app_id INT PRIMARY KEY,
    config JSON NOT NULL
);
 
INSERT INTO configuracion_app VALUES
(1, '{
    "general": {"nombre": "Mi Tienda Online", "moneda": "EUR", "idioma": "es"},
    "pagos": {
        "pasarelas": ["stripe", "paypal", "transferencia"],
        "stripe": {"public_key": "pk_live_xxx", "webhook_secret": "whsec_xxx"},
        "impuestos": {"iva": 21, "iva_reducido": 10}
    },
    "envio": {
        "gratuito_desde": 50.00,
        "zonas": [
            {"nombre": "Península", "coste": 4.99, "dias": 3},
            {"nombre": "Baleares", "coste": 8.99, "dias": 5},
            {"nombre": "Canarias", "coste": 12.99, "dias": 7}
        ]
    }
}');
SELECT
    config->>'$.general.nombre' AS tienda,
    config->>'$.pagos.impuestos.iva' AS iva,
    config->>'$.envio.gratuito_desde' AS envio_gratis_desde,
    config->>'$.envio.zonas[0].coste' AS coste_peninsula,
    config->'$.pagos.pasarelas' AS pasarelas
FROM configuracion_app
WHERE app_id = 1;
tiendaivaenvio_gratis_desdecoste_peninsulapasarelas
Mi Tienda Online2150.004.99["stripe", "paypal", "transferencia"]

Manejo de NULL y rutas inexistentes

Cuando una ruta JSON Path no coincide con ningún valor del documento, MySQL devuelve NULL. Esto es un comportamiento consistente en todas las funciones JSON:

SELECT
    config->>'$.general.nombre' AS existe,
    config->>'$.general.telefono' AS no_existe,
    config->>'$.seccion_inventada.campo' AS tampoco_existe
FROM configuracion_app
WHERE app_id = 1;
existeno_existetampoco_existe
Mi Tienda OnlineNULLNULL

Este comportamiento hace que el código sea seguro frente a documentos con estructuras variables: nunca obtendrás un error por acceder a una ruta que no existe, simplemente recibirás NULL.

Combinación con otras funciones

Las expresiones JSON Path se usan en prácticamente todas las funciones JSON de MySQL. Puedes usarlas para extraer (JSON_EXTRACT), modificar (JSON_SET, JSON_INSERT, JSON_REPLACE), eliminar (JSON_REMOVE), buscar (JSON_CONTAINS_PATH, JSON_SEARCH) y mucho más.

Si una clave JSON contiene espacios, puntos u otros caracteres especiales, debes encerrarla entre comillas dobles dentro de la expresión Path:

SELECT JSON_EXTRACT(
    '{"precio total": 150, "I.V.A.": 21}',
    '$."precio total"'
) AS precio;
precio
150

Sin las comillas dobles, MySQL interpretaría el espacio o el punto como un delimitador de ruta y la expresión fallaría.

En el siguiente artículo veremos cómo crear índices sobre datos JSON.

Escrito por Eduardo Lázaro

AnteriorTipo de dato JSONJSONSiguienteJSON IndexJSON