MySQL多表关联查询优化：将申请者数据聚合为JSON数组

问题背景 #

当前每次迭代都执行关联查询，导致网页加载缓慢。目标是通过1-2次查询关联trabajos、postulaciones、usuario三张表，一次性输出职位及该职位下所有申请者的完整信息。但现有SQL执行后，同一条职位数据会随每个申请者重复出现，且postulaciones字段仅包含单个申请者信息，不符合需求。

现有问题SQL #

SELECT JSON_ARRAYAGG( 
    JSON_OBJECT(
      'id', t.id, 
      'rut_empleador', t.rut_empleador,
      'titulo', t.titulo, 
      'descripcion', t.descripcion, 
      'foto', t.foto, 
      'cantidad_personas', t.cantidad_personas, 
      'ubicacion', t.ubicacion, 
      'fecha_publicacion', t.fecha_publicacion, 
      'fecha_seleccion_postulante', t.fecha_seleccion_postulante, 
      'fecha_finalizacion', t.fecha_finalizacion, 
      'precio', t.precio,
      'postulaciones',  JSON_OBJECT(
        'id', p.id,
        'id_trabajo', p.id_trabajo,
        'rut_trabajador', p.rut_trabajador,
        'id_estado', p.id_estado,
        'fecha_publicacion', p.fecha_publicacion,
        'user', JSON_OBJECT(
            'rut', u.rut, 
            'dv', u.dv, 
            'nombres', u.nombres,
            'apellidos', u.apellidos,
            'mail', u.mail,
            'direccion', u.direccion,
            'foto', u.foto,
            'fecha_nacimiento', u.fecha_nacimiento,
            'fecha_registro', u.fecha_registro,
            'ultima_visita', u.ultima_visita
        )
      )
    )
  ) as result FROM trabajos t join postulaciones p on p.id_trabajo = t.id  INNER JOIN usuario u ON p.rut_trabajador = u.rut where p.id_trabajo = t.id GROUP BY t.id order by t.fecha_publicacion desc;

当前临时解决方案（两次查询） #

router.get('/work/:id', async (req, res) => {
    try {
        const workById = await work.getWorkById(req.params.id);
        const workAppliers = await work.getWorkAppliers(req.params.id);
        res.status(200).json({
            ok: true,
            content: {
                work: workById[0],
                appliers: workAppliers
            }
        });
    } catch (err) {
        console.error(`Error while getting the job: `, err.message);
        next(err);
    }
});

修改后的SQL（单查询实现需求） #

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', t.id, 
        'rut_empleador', t.rut_empleador,
        'titulo', t.titulo, 
        'descripcion', t.descripcion, 
        'foto', t.foto, 
        'cantidad_personas', t.cantidad_personas, 
        'ubicacion', t.ubicacion, 
        'fecha_publicacion', t.fecha_publicacion, 
        'fecha_seleccion_postulante', t.fecha_seleccion_postulante, 
        'fecha_finalizacion', t.fecha_finalizacion, 
        'precio', t.precio,
        'postulaciones', p.postulaciones_list
    )
) as result
FROM trabajos t
LEFT JOIN (
    SELECT 
        p.id_trabajo,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', p.id,
                'id_trabajo', p.id_trabajo,
                'rut_trabajador', p.rut_trabajador,
                'id_estado', p.id_estado,
                'fecha_publicacion', p.fecha_publicacion,
                'user', JSON_OBJECT(
                    'rut', u.rut, 
                    'dv', u.dv, 
                    'nombres', u.nombres,
                    'apellidos', u.apellidos,
                    'mail', u.mail,
                    'direccion', u.direccion,
                    'foto', u.foto,
                    'fecha_nacimiento', u.fecha_nacimiento,
                    'fecha_registro', u.fecha_registro,
                    'ultima_visita', u.ultima_visita
                )
            )
        ) as postulaciones_list
    FROM postulaciones p
    INNER JOIN usuario u ON p.rut_trabajador = u.rut
    GROUP BY p.id_trabajo
) p ON p.id_trabajo = t.id
ORDER BY t.fecha_publicacion desc;

关键修改说明 #

1. 子查询聚合申请者数据：先通过子查询关联postulaciones和usuario，并按id_trabajo分组，用JSON_ARRAYAGG将每个职位的所有申请者信息聚合成JSON数组postulaciones_list。
2. 关联职位表：将子查询结果与trabajos表关联，确保每个职位仅返回一条记录，且postulaciones字段包含该职位的全部申请者数组。
3. 使用LEFT JOIN：如果某些职位没有申请者，也能保留职位基础信息（若不需要无申请者的职位，可改为INNER JOIN）。

内容的提问来源于stack exchange，提问作者Prieto