如何在ORDS Web服务中生成嵌套结构JSON响应？

核心方案 #

不需要额外新增复杂的函数/存储过程，直接利用Oracle原生JSON函数（如JSON_OBJECT、JSON_ARRAYAGG）结合SYS_REFCURSOR，就能让ORDS返回嵌套结构的JSON。

具体实现 #

假设你的表结构如下（以文档、作者、版本为例）：

• documents表：doc_id, title, author_id
• authors表：author_id, name, email
• versions表：version_id, doc_id, version_num, update_date

1. 编写嵌套查询的存储过程 #

DECLARE
  l_result_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_result_cursor FOR
    SELECT 
      JSON_OBJECT(
        '文档ID' VALUE d.doc_id,
        '标题' VALUE d.title,
        '作者' VALUE JSON_OBJECT(
          'ID' VALUE a.author_id,
          '姓名' VALUE a.name,
          '邮箱' VALUE a.email
        ),
        '版本列表' VALUE JSON_ARRAYAGG(
          JSON_OBJECT(
            '版本号' VALUE v.version_num,
            '更新时间' VALUE v.update_date
          ) ORDER BY v.version_num DESC
        )
      ) AS 嵌套JSON
    FROM documents d
    JOIN authors a ON d.author_id = a.author_id
    LEFT JOIN versions v ON d.doc_id = v.doc_id
    GROUP BY d.doc_id, d.title, a.author_id, a.name, a.email;
  
  HTP.print(l_result_cursor);
END;
/

2. ORDS服务配置 #

在ORDS中创建服务时，直接绑定该存储过程即可。ORDS会自动识别游标中的JSON结构，返回符合要求的嵌套JSON响应。如果不想用存储过程，也可以直接将上述SQL查询作为ORDS服务的数据源，效果一致。

关键注意事项 #

• 需使用Oracle 12c及以上版本，原生JSON函数从12c开始支持。
• JSON_ARRAYAGG用于将多行关联数据聚合为数组，必须配合GROUP BY使用，可通过ORDER BY控制数组内元素的排序。
• 如果要返回多条嵌套JSON数据，查询结果会自动被ORDS包装为JSON数组。

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