Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table result (
    id                uuid primary key,
    created_at        timestamp not null,
    updated_at        timestamp not null,
    academy_year_id   integer,
    form_id           integer,
    others_assessment jsonb,
    section_id        integer,
    term_id           integer
);

INSERT INTO result (id, created_at, updated_at, academy_year_id, form_id, others_assessment, section_id, term_id) VALUES
  ('084d1169-114c-45f6-9494-545698689f0f', '2019-10-14 21:35:23.017000', '2019-10-14 21:47:29.608000', 3, 2, '[{"others": [{"id": 1, "values": [{"id": 1, "value": 5}, {"id": 2, "value": 4}, {"id": 3, "value": 3}, {"id": 4, "value": 4}]}, {"id": 2, "values": [{"id": 1, "value": 3}, {"id": 2, "value": 3}, {"id": 3, "value": 3}, {"id": 4, "value": 3}, {"id": 5, "value": 3}, {"id": 6, "value": 3}, {"id": 7, "value": 3}]}], "average": 45.5, "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "school_header": "Excellent result keep it up", "class_position": "1", "class_master_comment": "Good result keep it up"}, {"others": [{"id": 1, "values": [{"id": 1, "value": 5}, {"id": 2, "value": 4}, {"id": 3, "value": 3}, {"id": 4, "value": 4}]}, {"id": 2, "values": [{"id": 1, "value": 3}, {"id": 2, "value": 3}, {"id": 3, "value": 3}, {"id": 4, "value": 3}, {"id": 5, "value": 3}, {"id": 6, "value": 3}, {"id": 7, "value": 3}]}], "average": 45.5, "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "school_header": "Excellent result keep it up", "class_position": "1", "class_master_comment": "Good result keep it up"}]', 4, 1);

CREATE TABLE



INSERT 0 1


create table assessment (
    id              uuid primary key,
    created_at      timestamp not null,
    updated_at      timestamp not null,
    academy_year_id integer,
    assessment      jsonb,
    form_id         integer,
    section_id      integer,
    subject_id      integer,
    term_id         integer
);

INSERT INTO assessment (id, created_at, updated_at, academy_year_id, assessment, form_id, section_id, subject_id, term_id) VALUES
  ('632b53ee-1a4d-49bb-93d3-8a1d74bbc916', '2019-09-12 22:13:33.984000', '2019-09-12 22:13:33.984000', 3, '[{"exam": 60.0, "grade": "A", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", ",assessment_1": 9.0, "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 49.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]', 2, 4, 3, 1)
, ('72dd61d7-24ef-4708-b78a-b5e824d8f959', '2019-09-12 22:13:49.758000', '2019-09-12 22:13:49.758000', 3, '[{"exam": 29.5, "grade": "n", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 69.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]', 2, 4, 2, 1)
, ('e41cbacf-c16f-49eb-a95d-4d86e8b894d6', '2019-09-21 01:13:28.406000', '2019-09-21 01:13:28.406000', 3, '[{"exam": 40, "grade": "A", "total": 50, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Aristotle", "result_computed": 0, "subject_teacher_done": 0}, {"exam": 50, "grade": "B", "total": 69, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Elvis", "result_computed": 0, "subject_teacher_done": 0}]', 2, 4, 5, 1)
, ('7c03c648-556d-447e-8eca-8cf79d993513', '2019-09-12 22:13:03.421000', '2019-09-12 22:13:03.421000', 3, '[{"exam": 42.5, "grade": "B", "total": 73.5, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 40.5, "grade": "n", "total": 71.5, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]', 2, 4, 4, 1)

CREATE TABLE



INSERT 0 4


SELECT jsonb_agg(student) AS students
FROM (
   SELECT jsonb_object_agg((kv).key, (kv).value) AS student
   FROM (
      SELECT ass->>'student_id' AS student_id, "char" 'a' AS tbl, id, jsonb_each(ass) AS kv
      FROM (SELECT jsonb_array_elements(assessment       ) AS ass, id FROM assessment) a

      UNION ALL
      SELECT ass->>'student_id'              ,        'b'       , id, jsonb_each(ass)
      FROM (SELECT jsonb_array_elements(others_assessment) AS ass, id FROM result    ) r
      ORDER BY 1, 2, 3
      ) sub1
   GROUP  BY student_id
   ) sub2
;


students


[{"exam": 50, "grade": "B", "total": 69, "others": [{"id": 1, "values": [{"id": 1, "value": 5}, {"id": 2, "value": 4}, {"id": 3, "value": 3}, {"id": 4, "value": 4}]}, {"id": 2, "values": [{"id": 1, "value": 3}, {"id": 2, "value": 3}, {"id": 3, "value": 3}, {"id": 4, "value": 3}, {"id": 5, "value": 3}, {"id": 6, "value": 3}, {"id": 7, "value": 3}]}], "average": 45.5, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Elvis", "school_header": "Excellent result keep it up", "class_position": "1", "result_computed": 0, "class_master_comment": "Good result keep it up", "subject_teacher_done": 0}, {"exam": 40, "grade": "A", "total": 50, "others": [{"id": 1, "values": [{"id": 1, "value": 5}, {"id": 2, "value": 4}, {"id": 3, "value": 3}, {"id": 4, "value": 4}]}, {"id": 2, "values": [{"id": 1, "value": 3}, {"id": 2, "value": 3}, {"id": 3, "value": 3}, {"id": 4, "value": 3}, {"id": 5, "value": 3}, {"id": 6, "value": 3}, {"id": 7, "value": 3}]}], "average": 45.5, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Aristotle", ",assessment_1": 9.0, "school_header": "Excellent result keep it up", "class_position": "1", "result_computed": 0, "class_master_comment": "Good result keep it up", "subject_teacher_done": 0}]



SELECT 1