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
Advertisement
Answer
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) )