Hace tiempo te mostré cómo “encapsular” varias filas de datos en una sola fila usando JSON con PostgreSQL y las funciones que de JSON que este motor ofrece.

Hoy te enseñaré a obtener varias filas “comprimidas” dentro de una fila simulando un JSON concatenando los resultados, por lo que será compatible con varios motores de bases de datos.

En pocas palabras ingresaremos un arreglo de filas en una fila, agrupando varias filas en una sola, o mejor dicho, colocar resultados de una subconsulta como JSON.

Agrupar subconsulta como JSON

Recuerda que para comprimir varias filas de una subconsulta en una sola fila de otra consulta podemos usar las funciones de agrupación.

Para el ejemplo que te enseñaré necesitaremos dos funciones o sus equivalentes: CONCAT (para concatenar valores) y GROUP_CONCAT para concatenar en subconsultas.

La mayoría de motores debe tener estas funciones. Por ejemplo, en MySQL son CONCAT y GROUP_CONCAT, por otro lado en SQLite3 se usa || para concatenar y también existe group_concat.

Con estas funciones es más que suficiente para simular un JSON y agrupar los resultados de una subconsulta.

Ejemplo de agrupación

Tengo una tabla de compras y productos. Cada compra tiene relacionados varios productos.

Es decir, cuando se hace una compra se registra la fecha, el proveedor y el usuario, pero en otra tabla se registran los detalles de los productos obtenidos en esa compra, que pueden ser varios o uno.

Se necesitaba que en un reporte apareciera el listado de compras y además todos los productos, agrupados como un arreglo para que por cada compra hubiera un array de varios productos como JSON obtenidos de otra tabla.

Podría hacer un INNER JOIN pero eso repetiría cada compra por cada producto existente, y luego tendría que agruparlo con un algoritmo. Así que lo que hice fue simular un JSON así:

SELECT
 compras.id,
    compras.monto,
    compras.fecha,
    proveedores.nombreCompleto AS proveedor,
    usuarios.nombre AS usuario,
    (
        '[' || (
            SELECT
                GROUP_CONCAT(
                    '{"descripcion":"' || productos.descripcion || '","numero":'||productos.idProducto || ',"precioCompra":' || productos_comprados.precioCompra || ',"cantidad":' || productos_comprados.cantidad || '}'
                )
            FROM
                productos_comprados
                INNER JOIN productos ON productos_comprados.idProducto = productos.idProducto
            WHERE
                idCompra = compras.id
        ) || ']'
    ) AS productos_json
FROM
    compras
    INNER JOIN proveedores ON proveedores.idProveedor = compras.idProveedor
    INNER JOIN usuarios ON usuarios.idUsuario = compras.idUsuario
    WHERE compras.fecha >= ? AND compras.fecha <= ?

La parte interesante que agrupa las filas de la subconsulta como un arreglo JSON es la siguiente:

(
        '[' || (
            SELECT
                GROUP_CONCAT(
                    '{"descripcion":"' || productos.descripcion || '","numero":'||productos.idProducto || ',"precioCompra":' || productos_comprados.precioCompra || ',"cantidad":' || productos_comprados.cantidad || '}'
                )
            FROM
                productos_comprados
                INNER JOIN productos ON productos_comprados.idProducto = productos.idProducto
            WHERE
                idCompra = compras.id
        ) || ']'
    ) AS productos_json

Fíjate que estoy armando el JSON manualmente, concatenando todo. Primero concateno los campos como cadenas, y luego concateno esos campos concatenados a los corchetes.

Un arreglo de JSON está delimitado por [ y ], y luego cada propiedad está definida con llaves. Si son cadenas llevan comillas.

El resultado (obtenido con la CLI de SQLite3) en este caso es el siguiente:

21|88.5|2022-08-31T11:20:10|Sabritas|parzibyte|[{"descripcion":"Barritas","numero":84,"precioCompra":10.0,"cantidad":1.0},{"descripcion":"krankys","numero":85,"precioCompra":5.0,"cantidad":1.0},{"descripcion":"rufles","numero":87,"precioCompra":20.0,"cantidad":1.0},{"descripcion":"Pan dulce","numero":86,"precioCompra":53.5,"cantidad":1.0}]
22|20.0|2022-08-31T11:20:20|Marinela|parzibyte|[{"descripcion":"Barritas","numero":84,"precioCompra":10.0,"cantidad":1.0},{"descripcion":"krankys","numero":85,"precioCompra":5.0,"cantidad":2.0}]

Y si te fijas bien en el JSON, es válido:

[
    {
        "descripcion": "Barritas",
        "numero": 84,
        "precioCompra": 10.0,
        "cantidad": 1.0
    },
    {
        "descripcion": "krankys",
        "numero": 85,
        "precioCompra": 5.0,
        "cantidad": 1.0
    },
    {
        "descripcion": "rufles",
        "numero": 87,
        "precioCompra": 20.0,
        "cantidad": 1.0
    },
    {
        "descripcion": "Pan dulce",
        "numero": 86,
        "precioCompra": 53.5,
        "cantidad": 1.0
    }
]

Ahora del lado del cliente solo hay que decodificar ese JSON y podremos tener varias filas agrupadas como arreglo de JSON ya sea en MySQL, SQLite, PostgreSQL o cualquier otro motor que tenga estas funciones sin necesidad de contar con funciones de JSON.