JSON_VALUE

La función JSON_VALUE, introducida en MySQL 8.0.21, extrae un valor escalar de un documento JSON y lo devuelve como un tipo SQL específico. A diferencia de JSON_EXTRACT, que devuelve un valor de tipo JSON, JSON_VALUE devuelve directamente un tipo SQL nativo como VARCHAR, INT, DECIMAL o DATE. Esto elimina la necesidad de hacer conversiones manuales con CAST y facilita el uso de valores JSON en comparaciones, ordenaciones e índices.

Sintaxis

JSON_VALUE(documento_json, ruta
    [RETURNING tipo]
    [ON EMPTY {NULL | DEFAULT valor | ERROR}]
    [ON ERROR {NULL | DEFAULT valor | ERROR}]
)

La cláusula RETURNING especifica el tipo SQL de retorno. Si se omite, el valor por defecto es VARCHAR(512). Las cláusulas ON EMPTY y ON ERROR controlan el comportamiento cuando la ruta no existe o cuando ocurre un error de conversión.

Comportamiento básico

Supongamos una tabla de productos con atributos JSON:

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    atributos JSON
);
 
INSERT INTO productos (nombre, atributos) VALUES
('MacBook Air M3', '{"marca": "Apple", "precio": 1399.00, "ram_gb": 16, "peso_kg": 1.24, "fecha_lanzamiento": "2024-03-08"}'),
('ThinkPad X1', '{"marca": "Lenovo", "precio": 1549.00, "ram_gb": 32, "peso_kg": 1.12, "fecha_lanzamiento": "2024-01-15"}'),
('Galaxy Book4', '{"marca": "Samsung", "precio": 899.99, "ram_gb": 16, "peso_kg": 1.55, "fecha_lanzamiento": "2024-02-20"}');

Comparemos JSON_EXTRACT y JSON_VALUE:

SELECT
    nombre,
    JSON_EXTRACT(atributos, '$.precio') AS extract_precio,
    JSON_VALUE(atributos, '$.precio') AS value_precio
FROM productos;

nombre	extract_precio	value_precio
MacBook Air M3	1399.00	1399.00
ThinkPad X1	1549.00	1549.00
Galaxy Book4	899.99	899.99

Aunque los valores parecen iguales, hay una diferencia fundamental: extract_precio es de tipo JSON, mientras que value_precio es de tipo VARCHAR(512). Esto importa cuando usas el valor en comparaciones u operaciones aritméticas.

La cláusula RETURNING

Con RETURNING puedes especificar exactamente el tipo SQL del resultado:

SELECT
    nombre,
    JSON_VALUE(atributos, '$.precio' RETURNING DECIMAL(10,2)) AS precio,
    JSON_VALUE(atributos, '$.ram_gb' RETURNING UNSIGNED) AS ram,
    JSON_VALUE(atributos, '$.peso_kg' RETURNING DECIMAL(4,2)) AS peso,
    JSON_VALUE(atributos, '$.fecha_lanzamiento' RETURNING DATE) AS lanzamiento
FROM productos;

nombre	precio	ram	peso	lanzamiento
MacBook Air M3	1399.00	16	1.24	2024-03-08
ThinkPad X1	1549.00	32	1.12	2024-01-15
Galaxy Book4	899.99	16	1.55	2024-02-20

Los valores ahora son del tipo SQL que especificaste. Esto significa que puedes hacer operaciones aritméticas y comparaciones de forma directa sin necesidad de CAST:

-- Sin JSON_VALUE necesitarías: CAST(atributos->>'$.precio' AS DECIMAL(10,2))
SELECT nombre
FROM productos
WHERE JSON_VALUE(atributos, '$.precio' RETURNING DECIMAL(10,2)) < 1000;

nombre
Galaxy Book4

Caso práctico: ordenar por valores JSON tipados

Un escenario frecuente es ordenar resultados por un valor almacenado dentro del JSON. Con ->> el valor es una cadena, lo que produce ordenaciones incorrectas para números:

-- Ordenación INCORRECTA (orden alfabético de cadenas)
SELECT nombre, atributos->>'$.precio' AS precio
FROM productos
ORDER BY atributos->>'$.precio';

nombre	precio
MacBook Air M3	1399.00
ThinkPad X1	1549.00
Galaxy Book4	899.99

El valor 899.99 aparece después de 1549.00 porque como cadena, "8" va después de "1". Con JSON_VALUE y RETURNING, la ordenación es correcta:

-- Ordenación CORRECTA (orden numérico)
SELECT nombre,
       JSON_VALUE(atributos, '$.precio' RETURNING DECIMAL(10,2)) AS precio
FROM productos
ORDER BY precio;

nombre	precio
Galaxy Book4	899.99
MacBook Air M3	1399.00
ThinkPad X1	1549.00

Las cláusulas ON EMPTY y ON ERROR

ON EMPTY controla qué ocurre cuando la ruta no existe en el documento. ON ERROR controla qué ocurre cuando hay un error de conversión de tipo:

INSERT INTO productos (nombre, atributos) VALUES
('Producto mínimo', '{"marca": "Genérica"}'),
('Producto raro', '{"marca": "Test", "precio": "no_es_numero"}');

SELECT
    nombre,
    JSON_VALUE(atributos, '$.precio'
        RETURNING DECIMAL(10,2)
        DEFAULT 0.00 ON EMPTY
        DEFAULT -1.00 ON ERROR
    ) AS precio
FROM productos;

nombre	precio
MacBook Air M3	1399.00
ThinkPad X1	1549.00
Galaxy Book4	899.99
Producto mínimo	0.00
Producto raro	-1.00

Para "Producto mínimo", la clave precio no existe, así que se aplica DEFAULT 0.00 ON EMPTY. Para "Producto raro", la clave existe pero su valor "no_es_numero" no se puede convertir a DECIMAL, así que se aplica DEFAULT -1.00 ON ERROR.

Las tres opciones disponibles para cada cláusula son NULL (devuelve NULL, comportamiento por defecto), DEFAULT valor (devuelve el valor especificado) y ERROR (lanza un error SQL).

Caso práctico: columnas generadas e índices con JSON_VALUE

JSON_VALUE es ideal para crear columnas generadas e índices sobre datos JSON porque ya devuelve el tipo correcto:

CREATE TABLE catalogo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    datos JSON,
    -- Columna generada con JSON_VALUE (tipo correcto directamente)
    precio DECIMAL(10,2) GENERATED ALWAYS AS (
        JSON_VALUE(datos, '$.precio' RETURNING DECIMAL(10,2))
    ) STORED,
    fecha_lanzamiento DATE GENERATED ALWAYS AS (
        JSON_VALUE(datos, '$.fecha_lanzamiento' RETURNING DATE)
    ) VIRTUAL,
    INDEX idx_precio (precio),
    INDEX idx_fecha (fecha_lanzamiento)
);
 
INSERT INTO catalogo (nombre, datos) VALUES
('Monitor 4K', '{"precio": 349.99, "fecha_lanzamiento": "2024-06-01"}'),
('Teclado RGB', '{"precio": 129.99, "fecha_lanzamiento": "2024-03-15"}'),
('Ratón Pro', '{"precio": 89.99, "fecha_lanzamiento": "2024-09-10"}');

SELECT nombre, precio, fecha_lanzamiento
FROM catalogo
WHERE precio BETWEEN 100 AND 400
ORDER BY fecha_lanzamiento;

nombre	precio	fecha_lanzamiento
Teclado RGB	129.99	2024-03-15
Monitor 4K	349.99	2024-06-01

Las columnas generadas con JSON_VALUE tienen el tipo correcto desde el inicio, sin necesidad de combinar ->> con CAST.

Manejo de NULL

JSON_VALUE devuelve NULL cuando la columna JSON es NULL o cuando la ruta no existe (a menos que hayas configurado ON EMPTY):

SELECT
    nombre,
    JSON_VALUE(atributos, '$.marca') AS marca,
    JSON_VALUE(atributos, '$.campo_inexistente') AS inexistente
FROM productos
WHERE id <= 3;

nombre	marca	inexistente
MacBook Air M3	Apple	NULL
ThinkPad X1	Lenovo	NULL
Galaxy Book4	Samsung	NULL

Si necesitas distinguir entre "la clave no existe" y "la clave existe pero tiene valor null", puedes usar ON EMPTY ERROR para que lance un error cuando la clave no existe, y manejar el null de JSON como un NULL normal de SQL.

Combinación con otras funciones

Al devolver tipos SQL nativos, los valores de JSON_VALUE se integran sin fricciones con todas las funciones SQL:

SELECT
    nombre,
    DATEDIFF(CURDATE(), JSON_VALUE(atributos, '$.fecha_lanzamiento' RETURNING DATE)) AS dias_desde_lanzamiento,
    ROUND(JSON_VALUE(atributos, '$.precio' RETURNING DECIMAL(10,2)) * 0.9, 2) AS precio_con_descuento
FROM productos
WHERE id <= 3
ORDER BY dias_desde_lanzamiento DESC;

nombre	dias_desde_lanzamiento	precio_con_descuento
ThinkPad X1	396	1394.10
Galaxy Book4	360	809.99
MacBook Air M3	343	1259.10

No necesitas CAST ni conversiones intermedias. JSON_VALUE con RETURNING hace que los valores JSON se comporten exactamente como columnas regulares del tipo especificado.

En el siguiente artículo veremos JSON_INSERT para agregar nuevos valores a un documento JSON.

Escrito por Eduardo Lázaro