PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres returning json with two level of nesting from query or function

I have these three tables:

"courses table"

id course_title chapters_count
1 course 1 5
2 course 2 3

"chapters table"

id chapter_title sub_chapters_count course_id –references courses(id)
1 chapter 1 2 1
2 chapter 2 4 1
3 chapter 3 1 1
4 chapter 4 3 1
5 chapter 5 0 1
6 chapter 1 4 2
7 chapter 2 5 2
8 chapter 3 0 2

"sub_chapters table"

id sub_chapter_title chapter_id –references chapters(id)
1 sub chapter 1 1
2 sub chapter 2 1
3 sub chapter 1 2
4 sub chapter 2 2
5 sub chapter 3 2
6 sub chapter 4 2
7 sub chapter 1 3
… …………. …

I want the result to be in JSON format like this:

{
  "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"
            },
            {
              "sub_chapter_id": 3,
              "sub_chapter_title": "sub chapter 3"
            }
          ]
        },
        {
          "chapter_id": 2,
          "chapter_title": "chapter 2",
          "sub_chapters": [
            {
              "sub_chapter_id": 1,
              "sub_chapter_title": "sub chapter 1"
            },
            {
              "sub_chapter_id": 2,
              "sub_chapter_title": "sub chapter 2"
            }
          ]
        }
      ]
    }
  ]
}
Tags:

2

Answers


  1. 0

    The most complicated part of this type of query is determining how to build the nested aggregates since PostgreSQL doesn’t support nesting aggregate functions.

    Run the following SQL to setup a demonstration environment (note that tables do not include a count of subordinate tables’ associated rows):

    CREATE TABLE courses (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
course_title text NOT NULL UNIQUE
);

CREATE TABLE chapters (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
chapter_title text NOT NULL,
course_id integer NOT NULL REFERENCES courses(id),
UNIQUE (course_id, chapter_title)
);

CREATE TABLE sub_chapters (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sub_chapter_title text NOT NULL,
  chapter_id integer NOT NULL REFERENCES chapters(id),
  UNIQUE (chapter_id, sub_chapter_title)
);

INSERT INTO courses (id, course_title)
OVERRIDING SYSTEM VALUE
VALUES (1, 'course 1'),
       (2, 'course 2');

INSERT INTO chapters (id, chapter_title, course_id)
OVERRIDING SYSTEM VALUE
VALUES (1, 'chapter 1', 1),
       (2, 'chapter 2', 1),
       (3, 'chapter 3', 1),
       (4, 'chapter 4', 1),
       (5, 'chapter 5', 1),
       (6, 'chapter 1', 2),
       (7, 'chapter 2', 2),
       (8, 'chapter 3', 2);

INSERT INTO sub_chapters (id, sub_chapter_title, chapter_id)
OVERRIDING SYSTEM VALUE
VALUES (1, 'sub chapter 1', 1),
       (2, 'sub chapter 2', 1),
       (3, 'sub chapter 1', 2),
       (4, 'sub chapter 2', 2),
       (5, 'sub chapter 3', 2),
       (6, 'sub chapter 4', 2),
       (7, 'sub chapter 1', 3);

--- synchronize ID sequences with inserted rows
SELECT
  c.table_schema,
  c.table_name,
  c.column_name,
  x.sequence_name,
  x.last_value,
  x.max_value,
  SETVAL(x.sequence_name, GREATEST(x.last_value, x.max_value))
FROM
  information_schema.columns c
  CROSS JOIN LATERAL XMLTABLE(
    '/table/row'
    PASSING QUERY_TO_XML(
      FORMAT(
        $$SELECT %1$L AS sequence_name, last_value, (SELECT MAX(%2$I) FROM %3$I.%4$I) AS max_value FROM %1$s$$,
        PG_GET_SERIAL_SEQUENCE(c.table_name, c.column_name),
        c.column_name,
        c.table_schema,
        c.table_name
      ),
      FALSE,
      FALSE,
      ''
    )
    COLUMNS
      sequence_name TEXT,
      LAST_VALUE BIGINT,
      max_value bigint
  ) x
WHERE
  c.table_schema = CURRENT_SCHEMA()
  AND c.table_name IN ('courses', 'chapters', 'sub_chapters')
  AND c.column_name = 'id';

    The following query uses LATERAL subqueries to get the nested aggregates:

    SELECT
  JSON_STRIP_NULLS(
    JSON_BUILD_OBJECT(
      'course',
      JSON_AGG(
        JSON_BUILD_OBJECT(
          'course_id',
          courses.id,
          'course_title',
          courses.course_title,
          'chapters_count',
          chaps.chapters_count,
          'chapters',
          chaps.chapters
        )
      )
    )
  ) AS courses
FROM
  courses
  CROSS JOIN LATERAL (
    SELECT
      COUNT(*) AS chapters_count,
      JSON_AGG(
        JSON_BUILD_OBJECT(
          'chapter_id',
          chapters.id,
          'chapter_title',
          chapters.chapter_title,
          'sub_chapters_count',
          sc.sub_chapters_count,
          'sub_chapters',
          sc.sub_chapters
        )
      ) AS chapters
    FROM
      chapters
      CROSS JOIN LATERAL (
        SELECT
          COUNT(*) AS sub_chapters_count,
          JSON_AGG(
            JSON_BUILD_OBJECT(
              'sub_chapter_id',
              sub_chapters.id,
              'sub_chapter_title',
              sub_chapters.sub_chapter_title
            )
            ORDER BY
              sub_chapters.id
          ) AS sub_chapters
        FROM
          sub_chapters
        WHERE
          sub_chapters.chapter_id = chapters.id
      ) sc
    WHERE
      chapters.course_id = courses.id
  ) chaps;

    The resulting output is:

    {
    "course": [
        {
            "course_id": 1,
            "course_title": "course 1",
            "chapters_count": 5,
            "chapters": [
                {
                    "chapter_id": 1,
                    "chapter_title": "chapter 1",
                    "sub_chapters_count": 2,
                    "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_count": 4,
                    "sub_chapters": [
                        {
                            "sub_chapter_id": 3,
                            "sub_chapter_title": "sub chapter 1"
                        },
                        {
                            "sub_chapter_id": 4,
                            "sub_chapter_title": "sub chapter 2"
                        },
                        {
                            "sub_chapter_id": 5,
                            "sub_chapter_title": "sub chapter 3"
                        },
                        {
                            "sub_chapter_id": 6,
                            "sub_chapter_title": "sub chapter 4"
                        }
                    ]
                },
                {
                    "chapter_id": 3,
                    "chapter_title": "chapter 3",
                    "sub_chapters_count": 1,
                    "sub_chapters": [
                        {
                            "sub_chapter_id": 7,
                            "sub_chapter_title": "sub chapter 1"
                        }
                    ]
                },
                {
                    "chapter_id": 4,
                    "chapter_title": "chapter 4",
                    "sub_chapters_count": 0
                },
                {
                    "chapter_id": 5,
                    "chapter_title": "chapter 5",
                    "sub_chapters_count": 0
                }
            ]
        },
        {
            "course_id": 2,
            "course_title": "course 2",
            "chapters_count": 3,
            "chapters": [
                {
                    "chapter_id": 6,
                    "chapter_title": "chapter 1",
                    "sub_chapters_count": 0
                },
                {
                    "chapter_id": 7,
                    "chapter_title": "chapter 2",
                    "sub_chapters_count": 0
                },
                {
                    "chapter_id": 8,
                    "chapter_title": "chapter 3",
                    "sub_chapters_count": 0
                }
            ]
        }
    ]
}

    The JSON_* functions can be changed to their JSONB_* equivalents; however, the order of elements within the JSON output might be different. The counts could also be removed without losing any information since the number of elements in an array can be determined easily.

    Login or Signup to reply.
  2. 0

    The key insight to generating nested JSON objects is to avoid using JOINs and instead write subqueries. The target structure then is mirrored nicely by the query structure:

    SELECT jsonb_build_object(
  'course', ARRAY(
    SELECT jsonb_build_object(
      'course_id', c.id,
      'course_title', c.course_title,
      'chapters', ARRAY(
        SELECT jsonb_build_object(
          'chapter_id', ch.id,
          'chapter_title', ch.chapter_title,
          'sub_chapters', ARRAY(
            SELECT jsonb_build_object(
              'sub_chapter_id', sch.id,
              'sub_chapter_title', sch.sub_chapter_title
            )
            FROM sub_chapters sch
            WHERE sch.chapter_id = ch.id
          )
        )
        FROM chapters ch
        WHERE ch.course_id = c.id
      )
    )
    FROM courses c
    WHERE c.id = 1
  )
);

    (online demo – thanks to JohnH for the setup SQL)

    An alternative to the ARRAY(SELECT … FROM …) subqueries is (SELECT jsonb_agg(…) FROM …), however that returns NULL when no row is found – you’d have to use COALESCE((SELECT jsonb_agg(…) FROM …), '[]') to work around that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search