adcfi/fetch/alumnos.php

<?php
require_once "{$_SERVER['DOCUMENT_ROOT']}/dependencies.php";
if (!isset($_SESSION['user'])) {
  returnResponse(status: 401, error: true, message: 'No se ha iniciado sesión');
  exit();
}

header('Content-Type: application/json');
$params = [
  'moodle_host_id' => $_SESSION['moodle_id'],
];
$alumnos = $db->query("WITH expanded_calificaciones AS (
  SELECT DISTINCT
    moodle_host_id,
    jsonb_array_elements(calificaciones)->>'grupo' AS grupo,
    jsonb_array_elements(calificaciones)->>'student_name' AS nombre,
    jsonb_array_elements(calificaciones)->>'username' AS username
  FROM
    public.snapshot_calificaciones
  WHERE
    moodle_host_id = :moodle_host_id
)
SELECT
  grupo,
  jsonb_agg(
    jsonb_build_object(
      'nombre', nombre,
      'username', username
    ) ORDER BY nombre
  ) AS alumnos
FROM
  expanded_calificaciones
GROUP BY grupo;",
  $params
) ?? [];

echo json_encode(array_map(fn($alumno) => ['grupo' => $alumno['grupo'], 'alumnos' => json_decode($alumno['alumnos'])], $alumnos));