I can not convert the data to the required form-CodePudding

Hi hi I have a Nodejs server and I am using PostgreSQL, the bottom part of my database. How can I select data and represent it like this (in one sql query):

 {lessons: ['lesson1','lesson2'], tests: [{quest:'quest_name',options:['opt1','opt2']}]}

I wrote a request but it is far from what I need:

SELECT tests.id, 
       chapter, 
       time, 
       ARRAY_AGG(quests.question) AS questions, 
       ARRAY_AGG(options.option) AS quest_options 
FROM tests 
LEFT JOIN quests ON tests.id = quests.test_id 
LEFT JOIN options ON quests.id = options.quest_id 
WHERE tests.course_id = '${courseId}' 
GROUP BY tests.id

CodePudding user response：

You can use a cte to first build the question JSON data, and then aggregate the latter with the lessons:

with tests(q, o) as (
   select q.question, json_agg(o.option) 
   from tests t join quests q on t.id = q.test_id join options o on q.id = o.quest_id 
   where t.course_id = '${courseId}'
   group by q.question
)
select json_build_object('lesson', 
     (select json_agg(l.lesson_text) from lessons l where l.course_id = '${courseId}'),
     'tests',
     (select json_agg(json_build_object('quest', t.q, 'options', t.o)) from tests t)
)