JSON_ARRAYAGG

La función JSON_ARRAYAGG es una función de agregación que recopila los valores de una columna a lo largo de múltiples filas y los combina en un único array JSON. Funciona de manera similar a GROUP_CONCAT, pero en lugar de producir una cadena separada por comas, genera un array JSON válido con los tipos de datos preservados. Esto la convierte en la herramienta perfecta para construir respuestas JSON desde datos relacionales, transformar resultados de consultas en estructuras JSON anidadas, o crear APIs que devuelven datos en formato JSON directamente desde la base de datos.

Sintaxis

JSON_ARRAYAGG(expresion)

La función recibe una expresión (normalmente el nombre de una columna) y devuelve un array JSON que contiene todos los valores de esa expresión en el grupo. Si se usa sin GROUP BY, agrupa todas las filas de la consulta en un solo array. Si se usa con GROUP BY, genera un array por cada grupo. Los valores NULL de SQL se incluyen en el array como valores JSON null.

SELECT JSON_ARRAYAGG(columna) FROM tabla;
SELECT columna_grupo, JSON_ARRAYAGG(columna_valor) FROM tabla GROUP BY columna_grupo;

Comportamiento básico

Veamos un ejemplo básico sin agrupación. Partimos de una tabla simple:

CREATE TABLE colores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50)
);
 
INSERT INTO colores (nombre) VALUES
('rojo'), ('azul'), ('verde'), ('amarillo'), ('negro');

SELECT JSON_ARRAYAGG(nombre) AS todos_los_colores
FROM colores;

Todos los valores de la columna nombre se recopilaron en un único array JSON. Los valores mantienen su tipo original: las cadenas aparecen entre comillas, los números aparecen sin comillas, y los booleanos aparecen como true o false.

Para ver cómo se preservan los tipos, usemos una tabla con diferentes tipos de datos:

SELECT JSON_ARRAYAGG(valor) AS mezcla
FROM (
    SELECT 42 AS valor
    UNION ALL SELECT 3.14
    UNION ALL SELECT NULL
) AS datos;

El entero 42 se mantuvo como entero, el decimal 3.14 como decimal, y el SQL NULL se convirtió en el JSON null. Esto contrasta con GROUP_CONCAT, que convertiría todo a texto y omitiría los valores NULL.

Caso práctico: productos agrupados por categoría

Imagina una tienda donde necesitas obtener los productos de cada categoría como un array JSON, ideal para alimentar una API:

CREATE TABLE productos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100),
    categoria VARCHAR(50),
    precio DECIMAL(10,2)
);
 
INSERT INTO productos (nombre, categoria, precio) VALUES
('iPhone 15 Pro', 'Smartphones', 1299.00),
('Samsung Galaxy S24', 'Smartphones', 899.00),
('Google Pixel 8', 'Smartphones', 699.00),
('MacBook Air M3', 'Portátiles', 1399.00),
('ThinkPad X1 Carbon', 'Portátiles', 1549.00),
('Funda silicona', 'Accesorios', 29.99),
('Cargador USB-C 65W', 'Accesorios', 35.99),
('Cable USB-C', 'Accesorios', 12.50);

Para obtener la lista de nombres de productos por categoría:

SELECT
    categoria,
    JSON_ARRAYAGG(nombre) AS productos,
    COUNT(*) AS total
FROM productos
GROUP BY categoria;

Cada categoría tiene su propio array con los nombres de sus productos. Esto es mucho más útil que GROUP_CONCAT cuando el resultado se va a consumir desde una aplicación que espera JSON.

Para obtener los precios como un array numérico (en vez de una cadena de texto):

SELECT
    categoria,
    JSON_ARRAYAGG(precio) AS precios
FROM productos
GROUP BY categoria;

Los precios se mantienen como números, no como cadenas de texto. Esto es crucial cuando el array lo va a consumir una aplicación que necesita operar matemáticamente con los valores.

Caso práctico: construir documentos JSON anidados

Uno de los usos más potentes de JSON_ARRAYAGG es combinarlo con JSON_OBJECT para construir documentos JSON complejos directamente desde la base de datos:

SELECT
    categoria,
    JSON_ARRAYAGG(
        JSON_OBJECT('nombre', nombre, 'precio', precio)
    ) AS productos
FROM productos
GROUP BY categoria;

Cada elemento del array es un objeto JSON completo con nombre y precio. Este formato es exactamente lo que una API REST devolvería al listar productos por categoría.

Puedes ir un paso más allá y construir todo el documento de respuesta:

SELECT JSON_OBJECT(
    'categoría', categoria,
    'total', COUNT(*),
    'precio_promedio', ROUND(AVG(precio), 2),
    'productos', JSON_ARRAYAGG(
        JSON_OBJECT('nombre', nombre, 'precio', precio)
    )
) AS respuesta_api
FROM productos
GROUP BY categoria;

Caso práctico: relaciones uno a muchos como JSON

Cuando tienes tablas relacionadas y quieres presentar los datos como un documento JSON con sus hijos anidados:

CREATE TABLE pedidos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cliente VARCHAR(100),
    fecha DATE
);
 
CREATE TABLE lineas_pedido (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pedido_id INT,
    producto VARCHAR(100),
    cantidad INT,
    precio DECIMAL(10,2)
);
 
INSERT INTO pedidos VALUES
(1, 'María García', '2026-02-10'),
(2, 'Carlos López', '2026-02-12');
 
INSERT INTO lineas_pedido (pedido_id, producto, cantidad, precio) VALUES
(1, 'iPhone 15 Pro', 1, 1299.00),
(1, 'Funda silicona', 2, 29.99),
(1, 'Cable USB-C', 1, 12.50),
(2, 'MacBook Air M3', 1, 1399.00),
(2, 'Cargador USB-C 65W', 1, 35.99);

SELECT
    p.id AS pedido_id,
    p.cliente,
    p.fecha,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'producto', lp.producto,
            'cantidad', lp.cantidad,
            'precio', lp.precio,
            'subtotal', lp.cantidad * lp.precio
        )
    ) AS lineas
FROM pedidos p
JOIN lineas_pedido lp ON p.id = lp.pedido_id
GROUP BY p.id, p.cliente, p.fecha;

Cada pedido tiene sus líneas de detalle representadas como un array de objetos JSON, exactamente como lo esperaría una aplicación frontend.

Manejo de NULL

JSON_ARRAYAGG incluye los valores NULL como JSON null dentro del array:

SELECT JSON_ARRAYAGG(valor) AS resultado
FROM (
    SELECT 'a' AS valor
    UNION ALL SELECT NULL
    UNION ALL SELECT 'b'
) AS datos;

Este comportamiento difiere de GROUP_CONCAT, que omite los valores NULL. Si quieres excluir los NULL del array, filtra en la consulta:

SELECT JSON_ARRAYAGG(valor) AS sin_nulls
FROM (
    SELECT 'a' AS valor
    UNION ALL SELECT NULL
    UNION ALL SELECT 'b'
) AS datos
WHERE valor IS NOT NULL;

Cuando no hay filas que agregar (por ejemplo, un grupo vacío tras un filtro que elimina todo), JSON_ARRAYAGG devuelve NULL, no un array vacío:

SELECT JSON_ARRAYAGG(nombre) AS resultado
FROM productos
WHERE categoria = 'Inexistente';

Si necesitas un array vacío en lugar de NULL, usa COALESCE:

SELECT COALESCE(JSON_ARRAYAGG(nombre), JSON_ARRAY()) AS resultado
FROM productos
WHERE categoria = 'Inexistente';

Combinación con otras funciones

JSON_ARRAYAGG se combina frecuentemente con JSON_OBJECT como vimos en los ejemplos anteriores. Otro patrón útil es combinarla con subconsultas para construir estructuras más complejas:

SELECT JSON_OBJECT(
    'total_categorías', COUNT(DISTINCT categoria),
    'total_productos', COUNT(*),
    'categorías', JSON_ARRAYAGG(DISTINCT categoria)
) AS resumen
FROM productos;

La comparación directa con GROUP_CONCAT muestra las ventajas de JSON_ARRAYAGG:

SELECT
    categoria,
    GROUP_CONCAT(nombre ORDER BY precio DESC) AS gc_result,
    JSON_ARRAYAGG(nombre) AS ja_result
FROM productos
GROUP BY categoria;

GROUP_CONCAT produce una cadena como "iPhone 15 Pro,Samsung Galaxy S24,Google Pixel 8", mientras que JSON_ARRAYAGG produce un array JSON válido ["iPhone 15 Pro", "Samsung Galaxy S24", "Google Pixel 8"]. La diferencia es significativa: el array JSON se puede parsear directamente en cualquier lenguaje de programación, maneja correctamente caracteres especiales como comas dentro de los valores, y preserva los tipos de datos. En el siguiente artículo veremos JSON_OBJECTAGG para crear objetos JSON desde pares agrupados.