JSON_TABLE

La función JSON_TABLE es una de las herramientas más potentes para trabajar con JSON en MySQL. Permite convertir un documento JSON en un conjunto de filas y columnas, como si fuera una tabla relacional. Esto es especialmente valioso cuando recibes datos JSON de APIs externas, logs o configuraciones y necesitas consultarlos con SQL estándar.

A diferencia de JSON_EXTRACT, que extrae valores individuales, JSON_TABLE puede "desenrollar" arrays completos en múltiples filas y mapear propiedades del objeto a columnas con nombre y tipo definido. Se utiliza en la cláusula FROM como si fuera una tabla, y puede combinarse con JOIN para cruzar datos JSON con tablas relacionales.

Sintaxis

JSON_TABLE(
    expresion_json,
    ruta_base COLUMNS (
        nombre_columna tipo_dato PATH 'ruta_json' [ON EMPTY {NULL | DEFAULT valor | ERROR}]
                                                   [ON ERROR {NULL | DEFAULT valor | ERROR}],
        nombre_columna FOR ORDINALITY,
        NESTED PATH 'ruta_json' COLUMNS (
            ...
        )
    )
) AS alias

Los elementos principales de la sintaxis son:

La expresion_json es la fuente de datos: puede ser una columna de tipo JSON, una variable o una cadena JSON literal.

La ruta_base es la ruta JSON que establece el contexto desde el cual se evalúan todas las rutas de las columnas. Si apunta a un array, se genera una fila por cada elemento del array.

La cláusula COLUMNS define las columnas del resultado. Cada columna tiene un nombre, un tipo de dato SQL y una ruta relativa al contexto base. FOR ORDINALITY genera un contador de fila automático. NESTED PATH permite expandir arrays anidados.

Las cláusulas ON EMPTY y ON ERROR controlan qué sucede cuando un valor no existe o cuando hay un error de tipo. Por defecto, ambas devuelven NULL.

Comportamiento básico

Empecemos con un ejemplo sencillo. Supongamos que tienes un documento JSON con información de un pedido y quieres extraer sus elementos como una tabla:

SELECT *
FROM JSON_TABLE(
    '{"pedido_id": 1001, "cliente": "María García", "total": 259.97}',
    '$' COLUMNS (
        pedido_id INT PATH '$.pedido_id',
        cliente VARCHAR(100) PATH '$.cliente',
        total DECIMAL(10,2) PATH '$.total'
    )
) AS pedido;

pedido_id	cliente	total
1001	María García	259.97

La ruta base '$' apunta a la raíz del documento. Cada columna define un nombre, un tipo SQL y una ruta que indica de dónde extraer el valor dentro del documento. MySQL convierte automáticamente los valores JSON a los tipos SQL especificados.

Expandir arrays en filas

La capacidad más poderosa de JSON_TABLE es convertir arrays JSON en múltiples filas. Cuando la ruta base apunta a un array, se genera una fila por cada elemento:

SELECT *
FROM JSON_TABLE(
    '[
        {"nombre": "iPhone 15 Pro", "precio": 1299.99, "cantidad": 1},
        {"nombre": "Funda silicona", "precio": 49.99, "cantidad": 2},
        {"nombre": "Cargador USB-C", "precio": 35.99, "cantidad": 1}
    ]',
    '$[*]' COLUMNS (
        fila_num FOR ORDINALITY,
        nombre VARCHAR(100) PATH '$.nombre',
        precio DECIMAL(10,2) PATH '$.precio',
        cantidad INT PATH '$.cantidad'
    )
) AS items;

fila_num	nombre	precio	cantidad
1	iPhone 15 Pro	1299.99	1
2	Funda silicona	49.99	2
3	Cargador USB-C	35.99	1

La ruta '$[*]' selecciona todos los elementos del array. La columna fila_num FOR ORDINALITY genera un número secuencial que indica la posición de cada elemento en el array, comenzando desde 1.

FOR ORDINALITY

La cláusula FOR ORDINALITY crea una columna de tipo entero sin signo que actúa como contador de filas. Es el equivalente a un ROW_NUMBER() dentro del contexto de JSON_TABLE. Resulta útil para saber la posición original de cada elemento en el array:

SELECT posicion, nombre
FROM JSON_TABLE(
    '["Electrónica", "Ropa", "Hogar", "Deportes"]',
    '$[*]' COLUMNS (
        posicion FOR ORDINALITY,
        nombre VARCHAR(50) PATH '$'
    )
) AS categorias;

posicion	nombre
1	Electrónica
2	Ropa
3	Hogar
4	Deportes

Observa que cuando los elementos del array son valores simples (no objetos), la ruta de la columna es '$', que se refiere al elemento actual.

ON EMPTY y ON ERROR

Estas cláusulas controlan el comportamiento cuando un valor no se encuentra o cuando hay un error de conversión de tipo:

SELECT *
FROM JSON_TABLE(
    '[
        {"nombre": "iPhone 15 Pro", "precio": 1299.99, "descuento": 10},
        {"nombre": "Funda silicona", "precio": 49.99},
        {"nombre": "Cable USB-C", "precio": "gratis"}
    ]',
    '$[*]' COLUMNS (
        nombre VARCHAR(100) PATH '$.nombre',
        precio DECIMAL(10,2) PATH '$.precio',
        descuento INT PATH '$.descuento'
            DEFAULT 0 ON EMPTY
            DEFAULT -1 ON ERROR
    )
) AS items;

nombre	precio	descuento
iPhone 15 Pro	1299.99	10
Funda silicona	49.99	0
Cable USB-C	NULL	-1

La segunda fila no tiene descuento, así que se aplica DEFAULT 0 ON EMPTY. Si en lugar de un valor válido hubiera habido un error de conversión, se aplicaría DEFAULT -1 ON ERROR. Puedes usar NULL, DEFAULT valor o ERROR (que lanza una excepción) para cada caso.

Para la columna precio de la tercera fila, el valor "gratis" no se puede convertir a DECIMAL, pero como no especificamos ON ERROR, se aplica el comportamiento por defecto que devuelve NULL.

NESTED PATH para arrays anidados

Los documentos JSON reales suelen tener estructuras anidadas. NESTED PATH permite expandir arrays dentro de arrays, generando un producto cartesiano controlado:

SET @pedido = '{
    "pedido_id": 1001,
    "cliente": "María García",
    "items": [
        {
            "producto": "iPhone 15 Pro",
            "precio": 1299.99,
            "accesorios": ["Funda", "Protector pantalla"]
        },
        {
            "producto": "MacBook Air M3",
            "precio": 1399.00,
            "accesorios": ["Funda portátil", "Ratón inalámbrico", "Hub USB-C"]
        }
    ]
}';
 
SELECT *
FROM JSON_TABLE(
    @pedido,
    '$.items[*]' COLUMNS (
        item_num FOR ORDINALITY,
        producto VARCHAR(100) PATH '$.producto',
        precio DECIMAL(10,2) PATH '$.precio',
        NESTED PATH '$.accesorios[*]' COLUMNS (
            accesorio_num FOR ORDINALITY,
            accesorio VARCHAR(100) PATH '$'
        )
    )
) AS detalle;

item_num	producto	precio	accesorio_num	accesorio
1	iPhone 15 Pro	1299.99	1	Funda
1	iPhone 15 Pro	1299.99	2	Protector pantalla
2	MacBook Air M3	1399.00	1	Funda portátil
2	MacBook Air M3	1399.00	2	Ratón inalámbrico
2	MacBook Air M3	1399.00	3	Hub USB-C

Cada item se expande con sus accesorios. El accesorio_num se reinicia para cada item padre, indicando la posición dentro de su array anidado. Las columnas del nivel superior (producto, precio) se repiten para cada accesorio.

Caso práctico: procesar respuestas de API

Imagina que almacenas respuestas de una API de envíos en una columna JSON y necesitas analizar los datos de seguimiento:

CREATE TABLE envios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pedido_id INT,
    respuesta_api JSON
);
 
INSERT INTO envios (pedido_id, respuesta_api) VALUES
(1001, '{
    "transportista": "MRW",
    "numero_seguimiento": "MRW-2025-00847",
    "eventos": [
        {"fecha": "2025-06-10 09:00:00", "estado": "Recogido", "ubicacion": "Madrid"},
        {"fecha": "2025-06-10 14:30:00", "estado": "En tránsito", "ubicacion": "Zaragoza"},
        {"fecha": "2025-06-11 10:15:00", "estado": "En reparto", "ubicacion": "Barcelona"},
        {"fecha": "2025-06-11 12:45:00", "estado": "Entregado", "ubicacion": "Barcelona"}
    ]
}');

SELECT
    e.pedido_id,
    t.transportista,
    t.num_seguimiento,
    t.fecha_evento,
    t.estado,
    t.ubicacion
FROM envios e,
JSON_TABLE(
    e.respuesta_api,
    '$' COLUMNS (
        transportista VARCHAR(50) PATH '$.transportista',
        num_seguimiento VARCHAR(100) PATH '$.numero_seguimiento',
        NESTED PATH '$.eventos[*]' COLUMNS (
            fecha_evento DATETIME PATH '$.fecha',
            estado VARCHAR(50) PATH '$.estado',
            ubicacion VARCHAR(100) PATH '$.ubicacion'
        )
    )
) AS t
WHERE e.pedido_id = 1001;

pedido_id	transportista	num_seguimiento	fecha_evento	estado	ubicacion
1001	MRW	MRW-2025-00847	2025-06-10 09:00:00	Recogido	Madrid
1001	MRW	MRW-2025-00847	2025-06-10 14:30:00	En tránsito	Zaragoza
1001	MRW	MRW-2025-00847	2025-06-11 10:15:00	En reparto	Barcelona
1001	MRW	MRW-2025-00847	2025-06-11 12:45:00	Entregado	Barcelona

Gracias a JSON_TABLE, cada evento de seguimiento se convierte en una fila independiente que puedes filtrar, ordenar y unir con otras tablas como cualquier dato relacional.

Caso práctico: importar datos JSON en tablas

Un uso muy habitual de JSON_TABLE es importar datos JSON masivos a tablas relacionales. Si recibes un archivo JSON con productos de un proveedor, puedes insertarlos directamente:

SET @catalogo = '[
    {"sku": "ELEC-001", "nombre": "Monitor 27 4K", "precio": 449.99, "stock": 25},
    {"sku": "ELEC-002", "nombre": "Teclado mecánico", "precio": 129.99, "stock": 80},
    {"sku": "ELEC-003", "nombre": "Ratón ergonómico", "precio": 79.99, "stock": 120}
]';
 
INSERT INTO productos (sku, nombre, precio, stock)
SELECT sku, nombre, precio, stock
FROM JSON_TABLE(
    @catalogo,
    '$[*]' COLUMNS (
        sku VARCHAR(20) PATH '$.sku',
        nombre VARCHAR(100) PATH '$.nombre',
        precio DECIMAL(10,2) PATH '$.precio',
        stock INT PATH '$.stock'
    )
) AS catalogo_proveedor;

Esta consulta inserta tres filas en la tabla productos directamente desde el JSON, sin necesidad de procesamiento externo.

Caso práctico: JSON_TABLE con JOIN

Como JSON_TABLE produce un resultado tabular, puedes combinarlo con JOIN para cruzar datos JSON con tablas existentes:

CREATE TABLE preferencias_usuario (
    usuario_id INT PRIMARY KEY,
    nombre VARCHAR(100),
    preferencias JSON
);
 
INSERT INTO preferencias_usuario VALUES
(1, 'María García', '{"categorias_favoritas": [6, 7, 8]}'),
(2, 'Carlos López', '{"categorias_favoritas": [2, 4]}');

SELECT
    p.nombre AS usuario,
    c.nombre AS categoria_favorita
FROM preferencias_usuario p,
JSON_TABLE(
    p.preferencias,
    '$.categorias_favoritas[*]' COLUMNS (
        cat_id INT PATH '$'
    )
) AS fav
JOIN categorias c ON c.id = fav.cat_id;

usuario	categoria_favorita
María García	Smartphones
María García	Portátiles
María García	Accesorios electrónicos
Carlos López	Ropa
Carlos López	Deportes

La consulta "desenrolla" el array de IDs de categorías favoritas con JSON_TABLE, y luego une cada ID con la tabla categorias para obtener los nombres. Este patrón es fundamental cuando trabajas con datos semiestructurados que hacen referencia a entidades relacionales.

Manejo de NULL

Cuando la expresión JSON de entrada es NULL, JSON_TABLE no produce filas:

SELECT *
FROM JSON_TABLE(
    NULL,
    '$[*]' COLUMNS (valor INT PATH '$')
) AS t;

El resultado está vacío. Lo mismo ocurre si la ruta base no coincide con ningún elemento del documento.

Si una propiedad específica no existe en algún elemento del array, la columna correspondiente recibe NULL (o el valor definido con DEFAULT ... ON EMPTY):

SELECT *
FROM JSON_TABLE(
    '[{"a": 1, "b": 2}, {"a": 3}]',
    '$[*]' COLUMNS (
        a INT PATH '$.a',
        b INT PATH '$.b' DEFAULT 0 ON EMPTY
    )
) AS t;

a	b
1	2
3	0

Combinación con otras funciones

Puedes combinar JSON_TABLE con funciones de agregación estándar para analizar datos JSON de forma avanzada:

SELECT
    COUNT(*) AS total_eventos,
    MIN(fecha_evento) AS primer_evento,
    MAX(fecha_evento) AS ultimo_evento
FROM envios e,
JSON_TABLE(
    e.respuesta_api,
    '$.eventos[*]' COLUMNS (
        fecha_evento DATETIME PATH '$.fecha'
    )
) AS t;

total_eventos	primer_evento	ultimo_evento
4	2025-06-10 09:00:00	2025-06-11 12:45:00

También puedes usar JSON_TABLE en subconsultas, CTEs o vistas para reutilizar la transformación:

CREATE VIEW v_eventos_envio AS
SELECT
    e.pedido_id,
    t.*
FROM envios e,
JSON_TABLE(
    e.respuesta_api,
    '$.eventos[*]' COLUMNS (
        evento_num FOR ORDINALITY,
        fecha DATETIME PATH '$.fecha',
        estado VARCHAR(50) PATH '$.estado',
        ubicacion VARCHAR(100) PATH '$.ubicacion'
    )
) AS t;

Con esta vista, puedes consultar los eventos de envío como si fueran una tabla relacional ordinaria.

En el siguiente artículo veremos MEMBER OF para verificar pertenencia a un array JSON.

Escrito por Eduardo Lázaro