Блог

У меня есть три таблицы. Курс, темы, подтемы. Все они содержат соответствующее имя, идентификатор, описание и т.д. Мой запрос заключается в получении всех данных из базы данных.

 select course.course_id, course_name, course_description,
   topic.topic_id, topic_name, topic_description,
   subtopic.subtopic_id, subtopic_name 
from 
course join topic on course.course_id = topic.course_id    
join subtopic on topic.topic_id = subtopic.topic_id
order by course.course_id, topic.topic_id, subtopic.subtopic_id;
  

Формат, который я получаю, представляет собой массив объектов. Желаемый результат

 course:[
     {
       course_id:1,
       topics:[
          {
            topic_id:1,
            subtopics:[
                        subtopic_id:1
                      ]
          }
       ]
     },
     ...
     ...
     ...
]
  

Есть ли способ запросить мою базу данных таким образом, чтобы мой вывод был в этом формате? Подход Javascript к оптимальному форматированию данных в таком формате также приветствуется.

Вы могли бы использовать агрегацию JSON:

 select c.course_id, c.course_name, c.course_description,
    jsonb_agg(
        jsonb_build_object(
            'topic_id',          t.topic_id,
            'topic_name',        t.topic_name,
            'topic_description', t.topic_description,
            'subtopics',         st.subtopics
        )
        order by t.topic_id
    ) topics 
from course c
inner join topic t on c.course_id = t.course_id    
cross join lateral (
    select jsonb_agg(
        jsonb_build_object(
            'subtopic_id',   st.subtopic_id, 
            'subtopic_name', st.subtopic_name
        )
        order by st.subtopic_id
    ) subtopics
    from subtopic st 
    where st.topic_id = t.topic_id
) st
group by c.course_id
order by c.course_id
  

Вы можете добавить еще один уровень агрегации, если хотите получить скалярный результат из большого объекта JSONB, как показано в вашем результирующем наборе:

 select jsonb_build_object(
    'courses',
    jsonb_agg(
        jsonb_build_object(
            'course_id',          course_id,
            'course_name',        course_name
            'course_description', course_description
            'topics',             topics
        )
    )
    order by order by course_id
) result
from (
    select c.course_id, c.course_name, c.course_description,
        jsonb_agg(
            jsonb_build_object(
                'topic_id',          t.topic_id,
                'topic_name',        t.topic_name,
                'topic_description', t.topic_description,
                'subtopics',         st.subtopics
            )
            order by t.topic_id
        ) topics 
    from course c
    inner join topic t on c.course_id = t.course_id    
    cross join lateral (
        select jsonb_agg(
            jsonb_build_object(
                'subtopic_id',   st.subtopic_id, 
                'subtopic_name', st.subtopic_name
            )
            order by st.subtopic_id
        ) subtopics
        from subtopic st 
        where st.topic_id = t.topic_id
    ) st
    group by c.course_id
) t