Find Total Possible Questions for Practice Test Quiz

find_total_posible_questions_for_quiz.sql

--  vtw_id  data 
--  exam_1  {items: [q1,q2,q3]}
--  exam_2  {items: [q3,q4]}
--  quiz_3  {examVtwId: ['exam_1', 'exam_2']}
--  quiz_4  {examVtwId: ['exam_2'] }
-- Result:
-- quiz_vtw total_question  count(exam)
-- quiz_4   2               1
-- quiz_3   5               2

SELECT 
    quiz.isbn,
    quiz.vtw_id, 
    sum(jsonb_array_length(exam.data->'items')) total_questions, 
    COUNT(exam) total_exams
 FROM ( 
     SELECT
         data->>'isbn' as isbn,
         vtw_id,
         trim(json_array_elements((data->>'examVtwId')::JSON)::TEXT, '"') exam_id
     FROM content.eols_collection
     --order by isbn, vtw_id
 ) quiz
 LEFT JOIN content.eols_collection exam ON exam.vtw_id = quiz.exam_id
 --where quiz.vtw_id='xxxxxxxxxxx'
 group by quiz.vtw_id, quiz.isbn
 order by total_questions