Hoy vamos a ver algo muy necesario al realizar consultas en bases de datos usando PostgreSQL.

Se trata de traer varios datos en una subconsulta pero colocarlos todos dentro de una columna, codificados de cierta manera. De este modo traemos varios datos de varias filas, por cada fila, como una columna.

Para esto vamos a usar las funciones JSON que PostgreSQL ofrece, en especial la función json_agg.

Con esto vamos a tener varios datos de una subconsulta como una cadena que podremos decodificar más adelante.

Si bien no es lo más adecuado, funciona y es muy rápido.

Nota: si tú no usas PostgreSQL aquí te dejo una alternativa.

Explicando la subconsulta

Tenemos una consulta que devuelve varias filas, pero queremos meter todas estas filas en una sola fila. Entonces primero veamos la consulta que vamos a convertir a JSON.

See the gist on github.

Cuando ejecutemos la consulta SQL los resultados pueden ser como los siguientes:

See the gist on github.

Estoy obteniendo los bonos de empleados, filtrando por el id del empleado.

Pero lo que quiero es obtener todos los empleados y agrupar todos sus bonos (no importa si no tiene bonos, o si tiene cientos de ellos) dentro de una sola fila. Justo aquí viene al rescate la opción de json_agg.

Consulta general

Ya vimos la subconsulta que devuelve varias filas que vamos a incluir como una sola dentro de otra consulta. Ahora veamos la consulta normal que obtiene los empleados:

See the gist on github.

Nota: la e es porque estoy asignando un alias a la tabla de empleados.

Los posibles resultados son:

See the gist on github.

Ahora aquí viene la parte importante. Quiero que todos los bonos estén en una columna, codificados como un arreglo de JSON que PostgreSQL va a codificar.

Entonces es momento de meter varias filas de una subconsulta en una columna y usar coalesce con json_agg.

Ingresar varias filas en una columna de PostgreSQL

Es momento de convertir las filas a un arreglo de  JSON y colocarlas dentro de una columna. Queda así:

See the gist on github.

En este caso estoy usando coalesce porque en ocasiones json_agg puede devolver null, y en ese caso devuelvo un [] que indica un arreglo vacío.

Al ejecutar la consulta, tengo los datos como JSON en una columna, por cada fila, usando PostgreSQL:

See the gist on github.

Y si te fijas, en bonos tengo una cadena que es JSON válido. Ahora solo debo decodificar del lado del cliente y obtendré los bonos como un arreglo.

Conclusión

Ya dije anteriormente que esta no es la solución más adecuada pero sí la más simple y la que funciona. Siempre he tenido ese problema con los motores de bases de datos al consultar varios datos y tratar de colocarlos en una sola columna.

Con PostgreSQL y JSON he encontrado la solución, y es muy rápida. Además, varios lenguajes de programación soportan JSON.

Por si te lo preguntas, la consulta final quedó así:

See the gist on github.

Y el sistema sobre el que trabajé fue en el sistema para maquilas, en el apartado de nóminas.