从一组平面记录生成嵌套JSON的PostgreSQL方法

Question

获取了故意未规范化的数据表：

SELECT location, day, title, teacher, "startTime", "endTime", canceled from "Schedules" ORDER by location, day, "startTime";

数据...

"First", 1, "Knitting 101", "Doe, John", "2019-02-19T09:00Z", "2019-02-19T11:00Z"
"First", 2, "Model Building 201", "Doe, Jane", "2019-02-20T09:00Z", "2019-02-20T11:00Z"
"Second", 1, "Rocks for Jocks 101", "Smith, Terry", "2019-02-19T09:00Z", "2019-02-19T11:00Z"
"Second", 2, "Speed Reading 101", "Case, Justin", "2019-02-20T09:00Z", "2019-02-20T11:00Z"

并希望生成此JSON：

[
  {
    "location": "First",
    "days": [
      {
        "day": 1,
        "classes": [
          {
            "title": "Knitting 101",
            "teacher": "Doe, John",
            "starts": "2019-02-19T09:00Z"
            "ends": "2019-02-19T11:00Z",
            "canceled": false
          }
          ... other classes
        ]
      }
      ... other days
    ]
  }
  ... other locations
]

当前正在使用JavaScript函数获取查询结果并将其嵌套。尝试使用此方法使用JSON函数失败：

1. （从"时间表"位置中选择不同的位置ORDER BY位置）l
2. （选择位置，天从"附表"GROUP BY位置ORDER BY位置，天）d
3. （选择*从"时间表"按位置、日期、开始时间排序）c
4. array_to_json（array_agg（row_to_json（l））
5. array_to_json（array_agg（row_to_json（d）））子查询，其中d.location=l.location
6. array_to_json（array_agg（row_to_json（c）））子查询，其中c.location=l.locationc.day=d.day

这是正确的方法还是我错过了一个更优雅的解决方案？

更新：@FXD提供的解决方案是从最详细的组织级别开始，向后工作。下面是工作查询（引用混合大小写对象的小调整：

SELECT array_to_json(array_agg("LocationClasses" ORDER BY Location)) AS "FullSchedule"
FROM (
    SELECT location, json_build_object('location', location, 'days', array_to_json(array_agg("LocationDayClasses" ORDER BY day))) AS "LocationClasses"
    FROM (
        SELECT location, day, json_build_object('day', day, 'classes', array_to_json(array_agg(json_build_object('title', title, 'teacher', teacher,'starts', "startTime", 'ends', "endTime", 'cancelled', canceled) ORDER BY "startTime"))) as "LocationDayClasses"
        FROM "Schedules"
        GROUP BY location, day
    ) T1
    GROUP BY location
) T2

Answer 1

你的步骤是对的，但我的顺序相反
如果您构建类，将它们按位置/天分组，然后按位置分组所有天，最后将整个内容分组到一个记录中，则可以在一个查询中完成所有操作。

从下面的查询中，我建议您尝试查看T1子查询的功能，然后对T2子查询执行相同的操作。你会发现这并不复杂。

SELECT array_to_json(array_agg(LocationClasses ORDER BY Location)) AS FullSchedule
FROM (
    SELECT Location, json_build_object('location',Location, 'days', array_to_json(array_agg(LocationDayClasses ORDER BY day))) AS LocationClasses
    FROM (
        SELECT Location, day, json_build_object('day', Day, 'classes', array_to_json(array_agg(json_build_object('title',Title,'teacher',Teacher,'starts',Starts,'ends',Ends,'cancelled',false) ORDER BY starts))) as LocationDayClasses
        FROM Schedules
        GROUP BY Location, Day
    ) T1
    GROUP BY Location
) T2