Query average point each student

Question

I have 2 tables:

Student:

   id       name     
   2        ABC      
   13       DEF      
   22       GHI      

Semester:

   id       student_id     sem     marks                                    
   1        2              1       {"math":3,   "physic":4,   "chemis":5}   
   2        2              2       {"math":2.5, "physic":4.5, "chemis":5}   
   3        2              3       {"math":3,   "physic":3.5, "chemis":4}   
   5        13             1       {"math":3,   "physic":4,   "chemis":5}   
   6        13             2       {"math":3,   "physic":4,   "chemis":5}   

So e.g with student_id=2:

average marks = ((3+4+5)/3)+(2.5+4.5+5)/3+(3+3.5+4)/3)/3 = 3.83

Is there any way to query average marks group by student ID?

   student_id      average     number_of_sems     
   2               3.83        3                  
   13              xxx         2         

I'm trying to count by subject:

SELECT
  t1.student_id,
  t1.count,
  (SELECT sum(xx.count)
   FROM
     (SELECT (marks:: JSON ->> 'math') :: DOUBLE PRECISION AS count
      FROM "Semester"
      WHERE student_id= t1.student_id) AS xx)
FROM
  (
    SELECT
      student_id,
      count(1)
    FROM "Semester"
    GROUP BY student_id
  ) AS t1;

But still don't know how to continue. This might be bad solution.

Answer 1

This includes the students without marks:

SELECT st.id, avg(m.value)
FROM student st
   LEFT JOIN semester se
      ON st.id = se.student_id
   LEFT JOIN LATERAL (SELECT value::numeric
                      FROM jsonb_each_text(se.marks)
                     ) m
      ON TRUE
GROUP BY st.id;

┌────┬────────────────────┐
│ id │        avg         │
├────┼────────────────────┤
│  2 │ 3.8333333333333333 │
│ 13 │ 4.0000000000000000 │
│ 22 │                    │
└────┴────────────────────┘
(3 rows)