如何在PostgreSQL中通过查询或函数返回双层嵌套JSON

现有关联表结构及数据 #

courses表 #

chapters表 #

sub_chapters表 #

需求 #

需要通过PostgreSQL查询或函数，生成如下格式的双层嵌套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"
            },
            {
              "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"
            }
          ]
        }
      ]
    }
  ]
}

解决方案 #

利用PostgreSQL的json_agg和json_build_object函数，通过嵌套查询实现目标双层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
      )
    )
  )
) AS result
FROM courses c;

说明 #

1. 最内层查询：针对单个章节，聚合其下属子章节为JSON数组，用json_build_object构造子章节的键值对结构。
2. 中间层查询：针对单个课程，聚合其下属章节（包含子章节数组）为JSON数组。
3. 最外层：将所有课程数据聚合为course对应的JSON数组，最终生成符合要求的嵌套JSON。

如果只需查询特定课程（如course_id=1），可在最外层FROM courses c后添加WHERE c.id = 1。

内容的提问来源于stack exchange，提问作者Ayoub Groubi