如何通过Oracle SQL查询获取分组嵌套的特殊JSON结果？

原代码的核心问题是：仅使用GROUP BY但未对分组内的行做聚合操作，且没有将所有分组结果合并为一个顶层JSON对象。要实现预期的嵌套JSON结构，需要结合JSON_ARRAYAGG和JSON_OBJECTAGG两个聚合函数：

修正后的SQL代码 #

SELECT JSON_OBJECTAGG(
         ROOM_LIGHT VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             'START' VALUE "START",
             'ENDE' VALUE ENDE,
             'RUNNING' VALUE RUNNING
           )
         )
       ) AS nested_json
FROM runtime
WHERE 1 = 1
GROUP BY ROOM_LIGHT
HAVING COUNT(*) > 0
ORDER BY ROOM_LIGHT;

代码说明 #

1. JSON_OBJECT：将每一行的START、ENDE、RUNNING字段转换为单个JSON对象。
2. JSON_ARRAYAGG：对每个ROOM_LIGHT分组，将组内所有JSON对象聚合成一个JSON数组，实现同组数据的嵌套。
3. JSON_OBJECTAGG：将所有分组的ROOM_LIGHT值作为键，对应的JSON数组作为值，最终合并成一个顶层JSON对象，得到符合规范的目标结构。

注意事项 #

• 原期望的JSON格式存在语法错误（重复的ROOM_LIGHT键），修正后的结果会生成符合JSON规范的结构，示例如下：

  {
    "01": [
      {"START": "xxx", "ENDE": "xxx", "RUNNING": "xxx"},
      {"START": "yyy", "ENDE": "yyy", "RUNNING": "yyy"}
    ],
    "02": [
      {"START": "aaa", "ENDE": "aaa", "RUNNING": "aaa"}
    ]
  }
  

• 由于START是Oracle关键字，需要用双引号包裹（"START"）避免语法错误。

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