with tabla_complete as (
WITH tabla_estudiantes_awards AS (
with aw AS (SELECT (t.unw->>'id_award')::INTEGER AS id_award,
(t.unw->>'fecha_award')::DATE::TEXT AS fecha_award,
(t._id_estudiante)::INTEGER AS _id_estudiante
FROM (SELECT _id_estudiante,JSONB_ARRAY_ELEMENTS(json_awards) AS unw
FROM notas.notas_estudiante
WHERE _id_main=7365 ) AS t)
SELECT aw.id_award AS id_award,
aw._id_estudiante AS _id_estudiante,
aw.fecha_award::DATE AS fecha_award,
na.award_points AS award_points
--nc.*
FROM aw,
notas.award na
--notas.__calificaciones_get_estudiante_main__11(7365) nc
--GROUP BY aw._id_estudiante, aw.id_award
WHERE --aw._id_estudiante = nc._id_estudiante
aw.id_award = na.id_award
)
--select _id_estudiante from tabla_estudents group by _id_estudiante
--SELECT _id_estudiante,
-- (CASE WHEN id_award is not null THEN ARRAY_TO_JSON(ARRAY_AGG(JSONB_BUILD_OBJECT('id_award',id_award,
-- 'fecha_award',fecha_award,
-- 'award_points',award_points
-- )
-- )
-- ) ELSE null
-- END)
select _id_estudiante,
ARRAY_TO_JSON(ARRAY_AGG(JSONB_BUILD_OBJECT('id_award',id_award,
'fecha_award',fecha_award,
'award_points',award_points
)
)
)
FROM tabla_estudiantes_awards GROUP BY _id_estudiante)
select * from tabla_complete
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.