Oracle中与DBMS_XMLGEN.getxml(query)功能对等的JSON函数是什么

Oracle在12.2及之后的版本中提供了和DBMS_XMLGEN便捷度相当的原生JSON生成方案，不需要逐列指定字段即可生成结构化JSON，根据数据库版本可选择对应写法：

12.2及以上版本：通配符最简写法 #

从12cR2版本开始，JSON_OBJECT函数原生支持*通配符，可以自动读取查询返回的所有列生成JSON对象，配合JSON_ARRAYAGG即可直接生成整查询结果的JSON数组，完全不需要手动枚举列名。
和逐列写列名的示例等价的代码如下：

WITH
    ta
    AS
        (SELECT 1 a, 2 b FROM DUAL
         UNION ALL
         SELECT 11, 22 FROM DUAL)
SELECT JSON_ARRAYagg(json_object(* KEYS CASE LOWER))
  FROM ta;

运行结果和逐列指定字段的写法完全一致：

[{"a":1,"b":2},{"a":11,"b":22}]

如果不需要小写key，去掉KEYS CASE LOWER参数即可，生成的JSON key默认和查询返回的列名/别名保持一致（默认为大写）。
针对任意单条查询，只需要把查询语句放在子查询中即可生成对应JSON，比如示例中的select 2 as a from dual，可以直接写为：

SELECT JSON_ARRAYagg(json_object(*)) FROM (select 2 as a from dual);

返回结构化JSON结果：

[{"A":2}]

如果是12.2-19c版本需要动态传入SQL字符串的场景，可以通过动态PL/SQL封装通用函数：传入查询SQL后，拼接为SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) FROM (<传入的查询SQL>)的格式执行，同样可以实现无需指定列名生成结构化JSON的效果。

21c及以上版本：类DBMS_XMLGEN的原生动态接口 #

如果你需要和DBMS_XMLGEN.getxml完全一致的调用方式——直接传入SQL字符串、不需要提前固定查询结构，可以使用Oracle 21c新增的DBMS_JSON包系列方法，用法和DBMS_XMLGEN几乎对齐，直接返回结构化JSON，不会把内容转义为字符串。
PL/SQL调用示例：

DECLARE
  v_ctx  NUMBER;
  v_json CLOB;
BEGIN
  -- 传入查询SQL打开上下文
  v_ctx := DBMS_JSON.OPEN_CONTEXT('SELECT 2 AS a FROM DUAL');
  -- 获取结构化JSON结果
  v_json := DBMS_JSON.GET_JSON(v_ctx);
  DBMS_OUTPUT.PUT_LINE(v_json);
  -- 关闭上下文释放资源
  DBMS_JSON.CLOSE_CONTEXT(v_ctx);
END;
/

运行输出：

[{"A":2}]

写法问题说明 #

JSON_ARRAY(DBMS_XMLGEN.getxml(...))写法无法得到结构化JSON，原因是DBMS_XMLGEN.getxml返回的是XML格式的文本内容，JSON函数会直接将其识别为普通字符串值存入JSON，不会对XML做结构解析和转换。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud