Postgres возвращает json с двумя уровнями вложенности из запроса или функции

Вопрос или проблема

{
  "курс": [
    {
      "course_id": 1,
      "название_курса": "курс 1",
      "количество_глав": 5,
      "главы": [
        {
          "chapter_id": 1,
          "chapter_title": "глава 1",
          "подглавы": [
            {
              "sub_chapter_id": 1,
              "название_подглавы": "подглава 1"
            },
            {
              "sub_chapter_id": 2,
              "название_подглавы": "подглава 2"
            },
            {
              "sub_chapter_id": 3,
              "название_подглавы": "подглава 3"
            }
          ]
        },
        {
          "chapter_id": 2,
          "chapter_title": "глава 2",
          "подглавы": [
            {
              "sub_chapter_id": 1,
              "название_подглавы": "подглава 1"
            },
            {
              "sub_chapter_id": 2,
              "название_подглавы": "подглава 2"
            }
          ]
        }
      ]
    }
  ]
}

Ответ или решение

Для получения данных из базы данных PostgreSQL в формате JSON с двумя уровнями вложенности, как в вашем примере, мы можем использовать оператор json_agg() и json_build_object(). Данный подход позволит вам объединить данные из трёх связанных таблиц: courses, chapters и sub_chapters.

Шаг 1: Описание связи между таблицами

Перед тем как перейти к написанию запроса, давайте кратко рассмотрим структуру и связи между таблицами:

1. Таблица courses содержит информацию о курсах.
2. Таблица chapters содержит главы, которые относятся к конкретным курсам по полю course_id.
3. Таблица sub_chapters включает подглавы, которые связаны с главами по полю chapter_id.

Шаг 2: SQL-запрос для получения нужного формата

Ниже представлен SQL-запрос, который создаёт требуемую структуру JSON:

SELECT json_build_object(
    'course', json_agg(
        json_build_object(
            'course_id', c.id,
            'course_title', c.course_title,
            'chapters_count', c.chapters_count,
            'chapters', (
                SELECT json_agg(
                    json_build_object(
                        'chapter_id', ch.id,
                        'chapter_title', ch.chapter_title,
                        'sub_chapters', (
                            SELECT json_agg(
                                json_build_object(
                                    'sub_chapter_id', sc.id,
                                    'sub_chapter_title', sc.sub_chapter_title
                                )
                            )
                            FROM sub_chapters sc
                            WHERE sc.chapter_id = ch.id
                        )
                    )
                )
                FROM chapters ch
                WHERE ch.course_id = c.id
            )
        )
    )
)
FROM courses c;

Объяснение запроса

1. Основной запрос: Мы выбираем данные из таблицы courses и используем json_build_object() для создания объекта JSON.
2. Агрегация курсов: Оператор json_agg() собирает все объекты курсов в массив, формируя поле course.
3. Глава курсов: Вложенный SELECT выбирает главы курсов, суммируя их в массив chapters, используя аналогичный подход с json_build_object() и json_agg().
4. Подглавы: Унутренний SELECT из таблицы sub_chapters обеспечивает вложение подглав в соответствующие главы.

Итоговый результат

Запрос вернёт JSON-документ в формате, который вы указали:

{
  "course": [
    {
      "course_id": 1,
      "course_title": "course 1",
      "chapters_count": 5,
      "chapters": [
        {
          "chapter_id": 1,
          "chapter_title": "chapter 1",
          "sub_chapters": [
            {
              "sub_chapter_id": 1,
              "sub_chapter_title": "sub chapter 1"
            },
            {
              "sub_chapter_id": 2,
              "sub_chapter_title": "sub chapter 2"
            }
          ]
        },
        {
          "chapter_id": 2,
          "chapter_title": "chapter 2",
          "sub_chapters": [
            {
              "sub_chapter_id": 1,
              "sub_chapter_title": "sub chapter 1"
            }
          ]
        }
      ]
    }
  ]
}

Заключение

Используя оператор JSON в PostgreSQL, вы можете эффективно агрегировать и структурировать данные с множеством уровней вложенности. Данный подход не только улучшает читаемость и управляемость данных, но и значительно сокращает время, необходимое для последующего извлечения и обработки информации на стороне клиента.