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.
select cantidad, concepto from bonos_empleado_nomina where id_nomina = 3 and id_empleado = 6;Cuando ejecutemos la consulta SQL los resultados pueden ser como los siguientes:
sistema_textil=# select cantidad, concepto from bonos_empleado_nomina where id_nomina = 3 and id_empleado = 6;
cantidad | concepto
----------+-----------------
20.00 | Ejemplo de bono
30.00 | Otro bono
(2 filas)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:
select e.nombre, e.apellido_paterno, e.apellido_materno, e.sueldo
from empleados eNota: la e es porque estoy asignando un alias a la tabla de empleados.
Los posibles resultados son:
sistema_textil=# select e.nombre, e.apellido_paterno, e.apellido_materno, e.sueldo from empleados e;
nombre | apellido_paterno | apellido_materno | sueldo
------------------+------------------+------------------+---------
Luis | Cabrera | Benito | 1500.00
Marφa JosΘ | Nombre muy largo | Nombre muy largo | 2000.00
JosΘ Francisco | Nombre muy largo | Nombre muy largo | 5000.00
Nombre muy largo | Nombre muy largo | Nombre muy largo | 5000.00
Otro | Rivera | BernabΘ | 30.00
Abraham | Nombre muy largo | Nombre muy largo | 50.00
Hola | Hola | Hola | 20.00
Ejemplo | 20 | 30 | 30.00
Ejemplo | 20 | 20 | 30.00
123 | 123 | 123 | 12.00
asd | 123 | 123 | 213.00
13 | 12 | 31 | 31.00
3 | 123 | 1 | 1.00
asd | asd | da | 12.00
qweq | qew | qwe | 12.00
asd | assad | asd | 12.00
asd | asd | asd | 123.00
Pedro | 123 | 1231 | 123.00
Chris | Redfield | A | 300.00
Leon | Scott | Kennedy | 500.00
Luis | Cabrera Benito | 20 | 30.00
(21 filas) 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í:
select e.nombre, e.apellido_paterno, e.apellido_materno, e.sueldo,
(
select coalesce(json_agg(bonos), '[]')
from (
select cantidad,
concepto
from bonos_empleado_nomina
where id_nomina = 3
and id_empleado = e.id
) bonos
) bonos
from empleados eEn 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:
sistema_textil=# select e.nombre, e.apellido_paterno, e.apellido_materno, e.sueldo, ( select coalesce(json_agg(bonos), '[]') from ( select cantidad, concepto from bonos_empleado_nomina where id_nomina = 3 and id_empleado = e.id ) bonos ) bonos from empleados e;
nombre | apellido_paterno | apellido_materno | sueldo | bonos
------------------+------------------+------------------+---------+----------------------------------------------------
Luis | Cabrera | Benito | 1500.00 | [{"cantidad":50.00,"concepto":"Me cae bien"}]
Nombre muy largo | Nombre muy largo | Nombre muy largo | 5000.00 | [{"cantidad":20.00,"concepto":"Ejemplo de bono"}, +
| | | | {"cantidad":30.00,"concepto":"Otro bono"}]
Otro | Rivera | BernabΘ | 30.00 | []
Hola | Hola | Hola | 20.00 | []
Ejemplo | 20 | 30 | 30.00 | []
Ejemplo | 20 | 20 | 30.00 | []
123 | 123 | 123 | 12.00 | []
asd | 123 | 123 | 213.00 | []
13 | 12 | 31 | 31.00 | []
3 | 123 | 1 | 1.00 | []
asd | asd | da | 12.00 | []
qweq | qew | qwe | 12.00 | []
asd | assad | asd | 12.00 | []
asd | asd | asd | 123.00 | []
Pedro | 123 | 1231 | 123.00 | []
Chris | Redfield | A | 300.00 | []
Leon | Scott | Kennedy | 500.00 | []
Marφa JosΘ | a | a | 2000.00 | []
JosΘ Francisco | a | a | 5000.00 | []
Fernando | a | a | 30.00 | []
Abraham | a | a | 50.00 | []
(21 filas)
sistema_textil=#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í:
select e.nombre, e.apellido_paterno, e.apellido_materno, e.sueldo,
(
select count(*) as dias
from asistencia_de_empleado
where asistencia_de_empleado.estado = 1
and asistencia_de_empleado.fecha >= ?
and asistencia_de_empleado.fecha <= ?
and asistencia_de_empleado.id_empleado = e.id
) dias_trabajados,
(
select coalesce(json_agg(bonos), '[]')
from (
select cantidad,
concepto
from bonos_empleado_nomina
where id_nomina = ?
and id_empleado = e.id
) bonos
) bonos,
(
select coalesce(json_agg(descuentos), '[]')
from (
select cantidad,
concepto
from descuentos_empleado_nomina
where id_nomina = ?
and id_empleado = e.id
) descuentos
) descuentos,
(
select coalesce(json_agg(incentivos), '[]')
from (
select i.eficiencia,
o.nombre,
i.fecha,
i.monto
from incentivo_de_empleado_en_nominas i
inner join operaciones_de_cortes o on i.id_operacion = o.id
where i.id_nomina = ?
and i.id_empleado = e.id
) incentivos
) incentivos,
(
select coalesce(json_agg(retiros), '[]')
from (
select r.fecha,
r.monto
from retiro_de_ahorros r
where r.id_nomina = ?
and r.id_empleado = e.id
) retiros
) retiros,
(
select coalesce(json_agg(ahorros), '[]')
from (
select a.monto
from ahorro_de_empleado_en_nominas a
where a.id_nomina = ?
and a.id_empleado = e.id
) ahorros
) ahorros
from empleados e;Y el sistema sobre el que trabajé fue en el sistema para maquilas, en el apartado de nóminas.
